C# LINQ

Array Class Collections File Keyword String .NET ASP.NETCast Compression Data Delegate Directive Enum Exception IfInterface LINQ Loop Method Number Process Property RegexSort StringBuilder Struct Switch Time Windows WPF

LINQ. Imperative code describes how to complete an algorithm.
It proceeds step by step,
emphasizing process,
not result. Declarative code (like LINQ) describes the end result.

This technology, Language Integrated Query, introduces extension methods. These work on Lists and arrays. We even use them on collections not yet in memory.

Example:We use the Average extension method to average all the elements in an int array. A double value is returned.

Tip:The Average method is implemented as an extension method within the .NET Framework. Extension methods have special syntax.

Extension Method

Based on:

.NET 4.5

Program that uses LINQ extension: C#

using System;
using System.Linq;

class Program
{
    static void Main()
    {
 int[] array = { 1, 3, 5, 7 };
 Console.WriteLine(array.Average());
    }
}

Output

4

Convert. Some extension methods in LINQ convert from an IEnumerable to another type.
They convert to an array,
Dictionary,
List
or Lookup.

ToArrayToDictionaryToListToLookup

Mutate. These methods filter or mutate. They change the elements in your query in some way. We remove unneeded elements, add new ones, or change other aspects of the elements themselves.

AsEnumerableAsParallelCastConcatContainsDefaultIfEmptyDistinctElementAtElementAtOrDefaultExceptFirstFirstOrDefaultGroupByGroupJoinIntersectJoinLastLastOrDefaultOfTypeOrderByOrderByDescendingReverseSelectSelectManySingleSingleOrDefaultUnionWhereZip

Skip and take. These extension methods are useful. They eliminate the need for custom code to check ranges. Skip passes over the first elements.

Skip, SkipWhileTake, TakeWhile

Computation. LINQ also provides computational methods. These act upon a certain query and then return a number or other value. These can also simplify code.

AggregateAllAnyAverageCountSequenceEqualSum

Max and min. We can search a collection for its largest (max) or smallest (min) value. This is effective for many value types. Which tower is the tallest?

Max, Min

Enumerable. The Enumerable type has some useful static methods. If you need an IEnumerable collection of a range or a repeated element, consider Range or Repeat.

Empty:The Empty method returns an empty enumerable collection. This can be useful as a "dummy" value.

Empty

Range:The Range method provides an enumerable collection that progresses from one value to another.

Range

Repeat:This method is repetitive—that is why it is called Repeat. It creates an enumerable collection full of one element.

Repeat

Query. A query expression uses declarative clauses. These specify the results we want, not how we are to achieve them. To start, we use a query expression on an array of integers.

Imperative:You describe how to accomplish the task by indicating each step in code statements.

Declarative:You describe the final result needed, leaving the steps up to the query language.

Linq Concept

In the query, we select elements from an array in descending order (high to low). We filter out elements <= 2.
In the loop,
we evaluate the expression
and print the results.

Var

Program that uses query expression: C#

using System;
using System.Linq;

class Program
{
    static void Main()
    {
 int[] array = { 1, 2, 3, 6, 7, 8 };
 // Query expression.
 var elements = from element in array
         orderby element descending
         where element > 2
         select element;
 // Enumerate.
 foreach (var element in elements)
 {
     Console.Write(element);
     Console.Write(' ');
 }
 Console.WriteLine();
    }
}

Output

8 7 6 3

Keywords. Query expressions use a whole new set of keywords. These are contextual keywords. This means they only have meaning in query expressions.

ascendingdescendinggroupjoinletorderbyselect new

Books. In query languages,
we express what we want,
not how it is to happen. The query language, not the programmer, is concerned with the exact implementation details.

We call this language the query language, because it is very useful for retrieving information from data bases by formulating queries, or questions, expressed in the language.Abelson & Sussman, p. 440

LINQ is a powerful feature. Its methods and query expressions often improve the readability of programs.
And they sometimes lead to new,
delayed,
superior algorithms.

SQL Joins with C# LINQ

ere are Different Types of SQL Joins which are used to query data from more than one tables. In this article, I would like to share how joins work in LINQ. LINQ has a JOIN query operator that provide SQL JOIN like behavior and syntax. Let's see how JOIN query operator works for joins. This article will explore the SQL Joins with C# LINQ.

1. INNER JOIN
2. LEFT OUTER JOIN
3. CROSS JOIN
4. GROUP JOIN

The JOIN query operator compares the specified properties/keys of two collections for equality by using the EQUALS keyword. By default, all joins queries written by the JOIN keyword are treated as equijoins.

LINQ PAD for running and debugging LINQ Query

I am a big fan of LINQ Pad since it allow us to run LINQ to SQL and LINQ to Entity Framework query and gives the query output. Whenever, I need to write LINQ to SQL and LINQ to Entity Framework query then, I prefer to write and run query on LINQ PAD. By using LINQ PAD, you can test and run your desired LINQ query and avoid the head-ache for testing LINQ query with in Visual Studio. You can download the LINQ Pad script used in this article by using this link.

In this article, I am using LINQ PAD for query data from database. It is simple and useful. For more help about LINQ PAD refer the link. You can download the database script used in this article byusing this link. Suppose we following three tables and data in these three tables is shown in figure.

INNER JOIN

Inner join returns only those records or rows that match or exists in both the tables.

C# Code

 var q=(from pd in dataContext.tblProducts join od in dataContext.tblOrders on pd.ProductID equals od.ProductID orderby od.OrderID select new { od.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 od.Quantity,
 od.Price,
 }).ToList(); 

LINQ Pad Query

INNER JOIN among more than two tables

Like SQL, we can also apply join on multiple tables based on conditions as shown below.

C# Code

 var q=(from pd in dataContext.tblProducts join od in dataContext.tblOrders on pd.ProductID equals od.ProductID join ct in dataContext.tblCustomers on od.CustomerID equals ct.CustID orderby od.OrderID select new { od.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 od.Quantity,
 od.Price,
 Customer=ct.Name //define anonymous type Customer
 }).ToList(); 

LINQ Pad Query

INNER JOIN On Multiple Conditions

Sometimes, we required to apply join on multiple coditions. In this case, we need to make two anonymous types (one for left table and one for right table) by using new keyword then we compare both the anonymous types.

C# Code

 var q=(from pd in dataContext.tblProducts join od in dataContext.tblOrders on pd.ProductID equals od.ProductID join ct in dataContext.tblCustomers on new {a=od.CustomerID,b=od.ContactNo} equals new {a=ct.CustID,b=ct.ContactNo} orderby od.OrderID select new { od.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 od.Quantity,
 od.Price,
 Customer=ct.Name //define anonymous type Customer
 }).ToList(); 

LINQ Pad Query

NOTE

1. Always remember, both the anonymous types should have exact same number of properties with same name and datatype other wise you will get the compile time error "Type inferencce failed in the call to Join".
2. Both the comparing fields should define either NULL or NOT NULL values.
3. If one of them is defined NULL and other is defined NOT NULL then we need to do typecasting of NOT NULL field to NULL data type like as above fig.

LEFT JOIN or LEFT OUTER JOIN

LEFT JOIN returns all records or rows from left table and from right table returns only matched records. If there are no columns matching in the right table, it returns NULL values.

In LINQ to achieve LEFT JOIN behavior, it is mandatory to use "INTO" keyword and "DefaultIfEmpty()" method. We can apply LEFT JOIN in LINQ like as :

C# Code

 var q=(from pd in dataContext.tblProducts join od in dataContext.tblOrders on pd.ProductID equals od.ProductID into t from rt in t.DefaultIfEmpty() orderby pd.ProductID select new { //To handle null values do type casting as int?(NULL int) 
 //since OrderID is defined NOT NULL in tblOrders
 OrderID=(int?)rt.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 //no need to check for null since it is defined NULL in database
 rt.Quantity,
 rt.Price,
 }).ToList(); 

LINQ Pad Query

CROSS JOIN

Cross join is a cartesian join means cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records or rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table.

In LINQ to achieve CROSS JOIN behavior, there is no need to use Join clause and where clause. We will write the query as shown below.

C# Code

 var q = from c in dataContext.Customers from o in dataContext.Orders select new { c.CustomerID,
 c.ContactName,
 a.OrderID,
 a.OrderDate
 }; 

LINQ Pad Query

GROUP JOIN

Whene a join clause use an INTO expression, then it is called a group join. A group join produces a sequence of object arrays based on properties equivalence of left collection and right collection. If right collection has no matching elements with left collection then an empty array will be produced.

C# Code

 var q=(from pd in dataContext.tblProducts join od in dataContext.tblOrders on pd.ProductID equals od.ProductID into t orderby pd.ProductID
 select new
 {
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 Order=t
 }).ToList(); 

LINQ Pad Query

Basically, GROUP JOIN is like as INNER-EQUIJOIN except that the result sequence is organized into groups.

GROUP JOIN As SubQuery

We can also use the result of a GROUP JOIN as a subquery like as:

C# Code

 var q=(from pd in dataContext.tblProducts join od in dataContext.tblOrders on pd.ProductID equals od.ProductID into t from rt in t where rt.Price>70000 orderby pd.ProductID select new { rt.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 rt.Quantity,
 rt.Price,
 }).ToList(); 

LINQ Pad Query