Learn SQL to LINQ (Visual Representation)

Introduction

A lot of developers moving towards the new LINQ to SQL find it difficult to write SQL queries in C# to query data using LINQ. LINQ is a query language which is integrated in C# to query data from ObjectCollects, SQL, XML etc. Before you start reading this article, it is good to have look at the features supported by LINQ:

• Implicitly typed local variables
• Extension Methods
• Anonymous types
• Lambda Expressions
• Object and collection initialization

Here in this artcile, 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:

Users

UserClients

LINQ structure

Note: In this article, all LINQ queries are performed in the LINQPAD application.

List of LINQ Queries

Case 1: Select

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

 Collapse

SELECT * FROM [User]

The LINQ query to do the above is:

 Collapse

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 andLastName. The SQL query to select all rows with only two columns is:

 Collapse

Select FirstName, LastName from [User]

Now the LINQ query:

 Collapse

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:

 Collapse

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

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

 Collapse

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:

 Collapse

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

or

 Collapse

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:

 Collapse

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

or

 Collapse

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:

 Collapse

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:

 Collapse

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:

 Collapse

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:

 Collapse

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 functionDefaultIfEmpty():

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:

 Collapse

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

or:

 Collapse

--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:

 Collapse

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

or

 Collapse

//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 BYclause, 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:

 Collapse

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

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

 Collapse

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:

 Collapse

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

or:

 Collapse

//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 theContains function of C#, which does filtering of records from a list of records:

 Collapse

//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:

 Collapse

//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 RowNumber function and then we use <=, >=, or BETWEEN. Here is the SQL query:

 Collapse

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 (see this link).
• Take: Returns a specified number of contiguous elements from the start of a sequence (see this link).

The LINQ query is something like:

 Collapse

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;

The best example of this is when you are using custom paging in you grid control or list control. A more detailed example can be seen here: LINQ tO SQL GridView (Enhanced GridView).

Case 9: SQL ISNULL function

Note: In this case, there is no graphical representation, I am just going to show one more function we can achieve with LINQ.

Read the following posts before continuing:

• SQL to LINQ (Visual Representation)
• Coalescing operator - ??

Solution 1

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

 Collapse

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:

 Collapse

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" 
};