SQL to LINQ ( Visual Representation )

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.

Before you start reading about this post its good to have look on the features which supports LINQ

• Extension Methods
• Anonymous types
• Lamda Expressions
• Object and Collection Initialization

Here in this post I am going to discuss about the basic SQL queries where LINQ queries are 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 are performed in LINQPAD application.


Case 1 - SELECT 

SQL query to get info of all user from the user table with all column

SELECT * FROM [User]

LINQ query similar to above

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 we are 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.SQL query for this is

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

same as SQL in LINQ we use WHERE clause to filter data, 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# 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 those table. SQL query for inner join is

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

SQL LINQ do the same thing it use JOIN keyword with EQUALS to join two collection.LINQ query for this is 

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, SQL query for this is

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

or

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

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 - GROUPING 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 group data , query is look 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 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

Summary
So the article shows visual representation LINQ queries. In part-2 I am going to discuss about more sql queries and related LINQ queries for that.