Tuesday, December 14, 2010

SQL to LINQ ( Visual Representation )

Shout it kick it on DotNetKicks.com
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

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.

4 comments:

  1. Very well written post, Pranay. Keep up the great work. I wish if you could deliver this in a next Tech-Talk session. what say ?

    ReplyDelete
  2. Ver very nice post. But I couldn't find example of

    SELECT *
    FROM Table1
    LEFT OUTER JOIN
    Table2
    ON Table1.key1 = Table2.key2

    ReplyDelete