ASP.NET - LINQ in asp.net

What is the purpose of LINQ Providers in LINQ?

LINQ Providers are a set of classes that takes a LINQ query and dynamically generates a method that executes an equivalent query against a specific data source.

What are the four LINQ Providers that .NET Framework ships?

1. LINQ to Objects - Executes a LINQ query against a collection of objects

2. LINQ to XML - Executes an XPATH query against XML documents

3. LINQ to SQL - Executes LINQ queries against Microsoft SQL Server.

4. LINQ to DataSets - Executes LINQ queries against ADO.NET DataSets.

What is LINQ

Language-Integrated Query (LINQ) is a set of extensions to the .NET  Framework that encompass language-integrated query, set, and transform  operations. It extends C# and Visual Basic with native language syntax  for queries and provides class libraries to take advantage of these
capabilities.

Let us see an example first. Suppose there is a list of integers like this:

List<int> list = new List<int>() { 1, 2, 3, 4, 5, 6, 100 };

To find all the even numbers in this list, you might write code like this:

List<int> list1 = new List<int>();
foreach (var num in list)
{
    if (num % 2 == 0)
      list1.Add(num);
}

Now with LINQ, you can select all of the even numbers from this list  and assign the query result to a variable, in just one sentence, like  this:

var list2 = from number in list
        where number % 2 == 0
        select number;

In this example, list2 and list1 are equivalent. list2 contains the same numbers as list1 does. As you can see, you don't write a foreach loop. Instead, you write a SQL statement.

But what do from, where, and select mean here? Where are they defined? How and when can you use them? Let us start the exploration now.

Linq is the Microsoft's first attempt to integrate queries into language. We know, it is really easy to find data from sql objects simply writing a query while its somewhat hectic when we want to do the same thing in a DataTable or Lists. Generally we will have to loop through every elements to find the exact match, if there is some aggregation we need to aggregate the values etc. Linq provides an easy way to write queries that can run with the in memory objects.  

Types of LINQ 

Linq Comes with 3 Basic Types (Provided there are lots of more types of LINQ on different type of objects :

1. LINQ (Linq to Objects)
2. DLINQ (Linq to SQL)
3. XLINQ (Linq to XML)

Before we continue with LINQ, we must look at some of the new inclusions which may come to us very handy.http://www.worldofasp.net/tut/LINQ/Basic_introduction_of_LINQ_205.aspx
http://www.codeproject.com/KB/dotnet/LINQ.aspx

I am going to discuss the basic SQL queries and the LINQ queries similar to SQL queries, with visual representations of the LINQ queries. Before I start discussing, here is the structure of the table I am using for this article:

List of LINQ Queries

Case 1: Select

The SQL query to get all users from the user table with all columns would be:

The LINQ query to do the above is:

var user = from u in Users
select u;

Here is the graphical representation the break down of the LINQ query that you wrote to get data form the user table:

Case 2: Select with Columns

This case is similar to the above but the difference is we are not selecting all the columns; instead, I select only two columns: FirstName and LastName. The SQL query to select all rows with only two columns is:

Select FirstName, LastName from [User]

Now the LINQ query:

from u in Users
select new
{
    u.FirstName,
    u.LastName
};

So you need to create a new anonymous type to get only the FirstName and LastName form the user object. The graphical representation of this query is:

Case 3: Filter Selected Data

For Integer Data

To apply filter on the selected data, we use the WHERE clause with the column value, so the SQL query would be:

Select firstname,LastName from [User] where id = 3

In LINQ, we need to use the WHERE clause as well, so the query would be:

from u in Users
where u.Id ==3
select new
{
   u.FirstName,
   u.LastName
}

This graphical representation shows the breakdown of the LINQ query related to filtering data:

For String Data

In order to filter strings, we use LIKE:

SELECT  [Id], [FirstName], [LastName], [Email], 
        [DisplayName], [Address1], [Address2], [Password], [Role]
FROM [User]
WHERE [Email] LIKE '%pranay%'

or

SELECT  [Id], [FirstName], [LastName], [Email], 
        [DisplayName], [Address1], [Address2], [Password], [Role]
FROM [User]
WHERE [Email] LIKE 'pranay%'

To apply the filter on the string datatype, you need to use the Contains or StartWith function available in C# so that it generates the same result as the above SQL queries:

from u in Users
where u.Email.Contains ("pranay")
select u

or

from u in Users
where u.Email.StartsWith ("pranay")
select u

The graphical representation of the LINQ query filtering using a string field:

Case 4: Joining Two Tables

Inner Join

Inner join is how we can get common records between two tables, i.e., related records form the table(s). Here is a SQL query for an inner join:

SELECT [User].[Id], [FirstName], [LastName], [UserId], [MobileNo]
FROM [User]
INNER JOIN
[UserClients]
ON [User].[id] = [UserId]

LINQ does the same using the Join keyword with Equals to join two collections:

var user = from u in Users
join uc in UserClients on u.Id equals uc.UserId
select new {
  u.Id,
  u.FirstName,
  u.LastName,
  uc.MobileNo,
  uc.imeiNO,
  uc.Id,
};

The graphical representation of inner join for the LINQ query is as shown below. As you can see, the User connection gets added to UserClients based on the condition in On.. Equals:

Outer Join

Outer Join is how we get common records between two tables, i.e., related records form a table; all records from the left table and not found in the right table gets a null value. A SQL query for an outer join would look like:

SELECT [t0].[Id], [FirstName], [LastName], 
       [UserId] AS [UserId], [MobileNo] AS [MobileNo]
FROM [User] AS [t0]
LEFT OUTER JOIN [UserClients]  ON ([t0].[id]) = [UserId]

In LINQ, to achieve outer join, you need to use the DefaultIfEmpty() function like:

var user = from u in Users
join uc in UserClients on u.Id equals uc.UserId
into myuserwithclient
from m in myuserwithclient.DefaultIfEmpty()
select new {
 u.Id,
 u.FirstName,
 u.LastName,
 m.UserId,
 m.MobileNo
};

The graphical representation of the outer join LINQ query is same as that for the inner join, but there is one more step for the function DefaultIfEmpty():

Case 5: Ordering Data

In SQL to order fetched data, we need to apply the ORDER BY clause with the ASC or DESC keyword, so the SQL query would be:

--Ascending
Select * from [User] order by firstName

or:

--Descending
Select * from [User] order by firstName desc

LINQ uses ORDER BY combined with the ASCENDING and DESCENDING keywords so that the final LINQ query would be:

//Ascending
var user = from u in Users
orderby u.FirstName
 select new
{
   u.FirstName,
   u.LastName 
}

or

//Descending
var user = from u in Users
orderby u.FirstName descending
select new
{
   u.FirstName,
   u.LastName 
};

Here is the graphical breakdown of the LINQ query:

Case 6: Grouping Data

Groups of selected data allow to perform aggregate function likes SUM, MAX, MIN, COUNT etc. To group data in SQL, you need to use the GROUP BY clause, but the thing to remember is you need to include the select list column in your group by clause or you will get a syntax error:

SELECT COUNT(*) AS [test], [UserId]
FROM [UserClients]
GROUP BY [UserId]

LINQ uses Group ... By to group data, so the query looks like:

var user =  from u in UserClients
group u by u.UserId into c
select new
{
 t1 = c.Key,
 tcount = c.Count()
};

Note: After you apply group by on a collection of objects in LINQ, your group by column gets converted to a key column which you can see in the above LINQ query, UserId. The graphical breakdown of the Group...By LINQ query is:

Case 7: Filter Data Using IN and NOT IN Clauses

Most developers who start working on LINQ queries get confused when they have to write IN and NOT IN queries using LINQ. Here is the SQL query:

//IN
SELECT [Id], [UserId], [IMEINo]
FROM [UserClients]
WHERE [UserId] IN (3, 4)

or:

//NOT IN
SELECT [Id], [UserId],  [IMEINo]
FROM [UserClients]
WHERE [UserId] IN (3, 4)

As you see above, the query uses IN and NOT IN clauses to filter from a list of records. The LINQ query to achieve this task makes use of the Contains function of C#, which does filtering of records from a list of records:

//IN
int[] chosenOnes = { 3, 4 };
var user = from u in UserClients
where chosenOnes.Contains(u.UserId.Value)
select new  { u.id,u.userid, u.ImeiNo};

or:

//NOT IN
int[] chosenOnes = { 3, 4 };
var user = from u in UserClients
where !chosenOnes.Contains(u.UserId.Value)
select u;

Note: IN and NOT IN use the same function in the LINQ query, but it just use a ! (not) symbol for it. Here is the graphical representation:

Case 8: Filtering Data by Row Numbers

I am now going to show how you can filter your data by row numbers that you assigned to your record(s). To filter data in SQL Server (SQL Server 2005), we use the http://msdn.microsoft.com/en-us/library/ms186734.aspx function and then we use <=, >=, or BETWEEN. Here is the SQL query:

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [id]) AS [ROW_NUMBER],
           [id], [FirstName], [LastName], [Email], [DisplayName], 
           [Address1], [Address2], [Password], [Role]
    FROM [User] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN 11 AND 20
ORDER BY [t1].[ROW_NUMBER]

In the above query, as you can see, the ROW_NUMBER() function assigns a number to the records, and we can use that number in an outer query to filter data between 11 to 20. LINQ makes use of two functions:

• Skip: Bypasses a specified number of elements in a sequence and then returns the remaining elements
• Take: Returns a specified number of contiguous elements from the start of a sequence

The LINQ query is something like:

var users = from u in Users
select u;

var filterUsers= users.OrderBy (p => p.Id).Skip (10).Take(10);

In the above code, we are selecting data first and than we are applying Skip and Take to get data between the 11 to 20 records. Here is the graphical representation;

Case 9: SQL ISNULL function

Solution 1

We can use the ternary operator as in the below example and MobileNo = "N/A" for the null values:

var user = from u in Users
join uc in UserClients on u.Id equals uc.UserId
into myuserwithclient
from m in myuserwithclient.DefaultIfEmpty()
select new {
u.Id,
FirstName = u.FirstName,
LastName = u.LastName,
UserId = m.UserId,
MobileNo = (m.MobileNo == null) ? "N/A" : m.MobileNo
};

Solution 2

Use the special Coalescing operator operator (??) as in the below example, and MobileNo = "N/A" for the null values:

var user = from u in Users
join uc in UserClients on u.Id equals uc.UserId
into myuserwithclient
from m in myuserwithclient.DefaultIfEmpty()
select new {
u.Id,
FirstName = u.FirstName,
LastName = u.LastName,
UserId = m.UserId,
MobileNo = m.MobileNo == null ?? "N/A" 
};

LINQ is a set of extensions to the .NET Framework that encompass language-integrated query, set, and transform operations. It extends C# and Visual Basic with native language syntax for queries and provides class libraries to take advantage of these capabilities.