Learn SQL to LINQ ( Visual Representation ) with Examples

No.of Views2889
Bookmarked1 times
Downloads 
Votes0
By  pranay rana   On  08 Jan 2011 23:01:36
Tag : LINQ , T-SQL
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.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

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.

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

Image Loading

UserClient

Image Loading

Linq Structure

Image Loading

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. 

Image Loading

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

Image Loading

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 

Image Loading

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

Image Loading

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

Image Loading

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

Image Loading

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

Image Loading

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

 

Image Loading

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.

 
Sign Up to vote for this article
 
About Author
 
pranay rana
Occupation-CEO
Company -GMind Solusion
Member Type-Expert
Location-India
Joined date-08 Jan 2011
Home Page-http://pranayamr.blogspot.com
Blog Page-http://pranayamr.blogspot.com
Hey, I am Pranay Rana, working as a Senior Software engineer in mid-size company located in ahmedabad. Web development in Asp.Net with C# and MS sql server are the experience tools that I have had for the past 4.3 years now. For me def. of programming is : Programming is something that you do once and that get used by multiple for many years You can visit me on my blog - http://pranayamr.blogspot.com/ StackOverFlow - http://stackoverflow.com/users/314488/pranay My CV :- http://careers.stackoverflow.com/pranayamr
 
 
Other popularSectionarticles
    In this article, I am going to show how you can filter your data by RowNumbers that you assigned to your records.
    Published Date : 03/Feb/2011
    SelectMany is Projects each element of a sequence to an IEnumerable and flattens the resulting sequences into one sequence.In this post I am going to show how you can use SelectMany Extension method to achieve the join between related tables easily without writing long queries.
    Published Date : 18/Jan/2011
    I have already written several article about Linq its a great ORM that we can use in various way. The purpose of this post to demonstrate How we can bind custom entity to stored procedure result with use of Linq-To-SQL.
    Published Date : 05/Aug/2010
    Today one of my friend asked me about simple insert,update and delete example with LINQ-To-SQL but at that time i was not having any simple example which will show the power of LINQ-To-SQL Example
    Published Date : 17/May/2010
    After two decades, the industry has reached a stable point in the evolution of object-oriented (OO) programming technologies
    Published Date : 12/May/2010
Comments
By:SQL onlineDate Of Posted:12/3/2012 3:13:59 AM
SQL tutorial
Hello, i would like to ask that what is the benefits of sql training, what all topics should be covered and it is kinda bothering me … and has anyone studies from this course http://www.wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance... would really appreciate help… and Also i would like to thank for all the information you are providing on sql training.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top