Learn SQL to LINQ ( Visual Representation )

Introduction 

Now a days most of the developers are moving towards new LINQ to SQL they find difficult to write down SQL query in C# to query data using LINQ. LINQ is a query language which get integrated in C# to query data form ObjectCollects, SQL, XML etc. But before you start reading about this post its good to have look to the features which support LINQ feature

• Implicitly typed local variables  
• Extension Methods  
• Anonymous types 
• Lamda Expressions  
• Object and Collection Initialization 

Here in this post I am going to discuss about the basic SQL query and LINQ query similar to SQL queries. And visual representation of LINQ queries. Before I start discussing here is structure of the table I am using for this article. 

Users 

UserClient Linq Structure

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

Points of Interest 

Case 1 - SELECT  SQL query to bring all user from the user table with all column

SELECT * FROM [User]

LINQ query similar to above this

var user = from u in Users
select u;

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 above case but the change is we are not selecting all columns of the table instead of that I am going to select only two columns here for this example querying only tow column of table FirstName and LastName. SQL query to select all row with only two column of the table

Select firstname,LastName from [User]

Now the LINQ query for the similar one is

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

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

Case 3 - FILTER SELECT DATA  

FOR INTEGER KIND OF DATA To apply filter on the selected data we use WHERE clause with the column value so the SQL query for this is

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

same as SQL in LINQ we need to use WHERE clause with do the filter data, so the LINQ query is

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

Graphic representation shows breakdown of the LINQ query related to filtering of data FOR STRING KIND OF DATA As we can filter interger kind of data similarly inorder to filter string 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 string datatype field you require to use Contains or StartWith function available in C# so that it generates same result as SQL query

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

or

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

Graphical representation of LINQ query filtering using string field

Case 4 - JOINING TWO TABLE  

INNER JOIN 

Inner join is by which we can get common records between two table i.e related records form that table(s). SQL query for inner join is

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

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

Graphical representation of the Inner join for the LINQ query is as shown below. So as you can see in the image the User connection get added to UserClients and based on condition in On.. Equals

OUTER JOIN 

Outer Join is by which we can get common records between two table i.e related records form that table and as well as the all record form left table and not found right table column get null value. SQL query for outer join is

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 DefaultIfEmpty() function which so the LINQ query for this is 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
};

Graphical representation of the outer join LINQ query is same as inner join but there on more step for the function DefaultIfEmpty() is added

Case 5 - ORDERING DATA  

In SQL to Order  fetched data one need to apply ORDER BY clause with ASC or DESC word, so the SQL query for this is

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

or

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

As SQL LINQ use ORDER BY combine with ASCENDING and DESCENDING keyword so that final LINQ query is

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

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

Graphical breakdown of LINQ query is

Case 6 - GROPING DATA 

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

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

LINQ use Group ... By to grouping data so the query is look like

var user = <span class="Apple-tab-span" style="white-space: pre; "> </span>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 collection of object in LINQ your group by column get converted in key column which you can see in above LINQ query that I am referring UserId as Key. Graphical breakdown of the the Group..By LINQ query is

Case 7 : Filter data by using IN and NOT IN clause  

Most of the developer who started working on LINQ queries gets confuse when they got requirement to write IN and NOT IN query using LINQ. 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 query use IN and NOT IN clause to filter from list of records. LINQ Query To achieve similar task LINQ make use of Contains function of C#. which do filtering of record form the list of record.

//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 same function in LINQ query but it just use !(Not) symbol for it. Graphical representation

Case 8 : Filter data by RowNumbers 

Here in this case I am going to show how you can filter your data by RowNumbers that you assigned to your recored(s). So to filter data in SQL (SQL server-2005) we use RowNumber function and than we use <=, >= or BETWEEN  to filer data. 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 above query as you can see ROW_NUMBER() function assign number to records and than we use that number in outer query to filter data between 11 to 20. LINQ query But in LINQ it make 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.)

So LINQ query is something as below.

var users = from u in Users
select u;

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

In above code we are selecting data first and than we are applying Skip and Take to get data between 11 to 20 records. Graphic representation

Best example of this is when you are using custom paging in you gridcontrol or list control. more detail Example : LINQ TO SQL GridView (Enhanced Gridview)   

Case 9 : SQL ISNULL function 

NOTE 

In this case I am not displaying any image as I shown in above cases but in this case I am just going to show one more function we can achieve with LINQ. 

Read the following post before continuing with this:

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

Solution 1

Use of ternary operator as in the below example and the 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 special Coalescing operator operator (??) as in the below example and 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" 
};

Summary  

So the article shows visual representation LINQ queries.

Enjoy work with LINQ