Click here to Skip to main content
15,867,568 members
Articles / Programming Languages / SQL

Learn SQL to LINQ (Visual Representation)

Rate me:
Please Sign up or sign in to vote.
4.88/5 (106 votes)
26 Dec 2010CPOL6 min read 254.8K   187   112
A discussion of LINQ queries for matching SQL queries, with visual representations.

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:

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

01_userclient_table.png

UserClients

02_user_table.png

LINQ structure

03_linq_structure.png

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:

SQL
SELECT * FROM [User]

The LINQ query to do the above is:

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

04_select_user.png

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

SQL
Select FirstName, LastName from [User]

Now the LINQ query:

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

05_select_user.png

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:

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

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

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

06_select_filter.png

For String Data

In order to filter strings, we use LIKE:

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

or

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

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

or

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

The graphical representation of the LINQ query filtering using a string field:

07_select_filter_like.png

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:

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

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

Image 8

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:

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

C#
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 function DefaultIfEmpty():

Image 9

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:

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

or:

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

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

or

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

Image 10

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 BY clause, 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:

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

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

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

Image 11

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:

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

or:

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

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

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

Image 12

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:

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

C#
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;

Image 13

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:

Solution 1

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

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

C#
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

The article has shown visual representations of a lot of LINQ queries. I hope you enjoy working with LINQ!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
India India

Microsoft C# MVP (12-13)



Hey, I am Pranay Rana, working as a Team Leadin MNC. Web development in Asp.Net with C# and MS sql server are the experience tools that I have had for the past 5.5 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 my blog


StackOverFlow - http://stackoverflow.com/users/314488/pranay
My CV :- http://careers.stackoverflow.com/pranayamr

Awards:



Comments and Discussions

 
QuestionGreat one Pin
vrkrishnareddy17-Jan-16 16:21
vrkrishnareddy17-Jan-16 16:21 
QuestionThis is great! Pin
Daniel Kamisnki13-Dec-15 14:54
Daniel Kamisnki13-Dec-15 14:54 
GeneralNice Effort Pin
Bilal Haider26-Nov-13 20:49
professionalBilal Haider26-Nov-13 20:49 
QuestionVery Useful Pin
Manish Parik27-Aug-13 2:07
professionalManish Parik27-Aug-13 2:07 
AnswerRe: Very Useful Pin
aarif moh shaikh14-Oct-14 1:55
professionalaarif moh shaikh14-Oct-14 1:55 
GeneralMy vote of 5 Pin
vshekharjha17-May-13 19:01
vshekharjha17-May-13 19:01 
GeneralMy vote of 5 Pin
gagan sawaliya9-May-13 1:02
gagan sawaliya9-May-13 1:02 
GeneralMy vote of 5 Pin
Datta Salunkhe15-Jan-13 20:22
Datta Salunkhe15-Jan-13 20:22 
GeneralMy vote of 4 Pin
AndrewGoel13-Jan-13 18:34
AndrewGoel13-Jan-13 18:34 
GeneralMy vote of 5 Pin
Sriraj Vasireddy16-Nov-12 13:31
Sriraj Vasireddy16-Nov-12 13:31 
Generalnice Pin
xcandyz25-Oct-12 20:04
xcandyz25-Oct-12 20:04 
GeneralMy vote of 5 Pin
ravithejag4-Oct-12 18:03
ravithejag4-Oct-12 18:03 
GeneralMy vote of 2 Pin
Fardan11-Feb-12 21:17
Fardan11-Feb-12 21:17 
GeneralMy vote of 5 Pin
ambarishtv3-Feb-12 20:43
ambarishtv3-Feb-12 20:43 
Generalmy vote of 5 Pin
Uday P.Singh25-Jan-12 22:38
Uday P.Singh25-Jan-12 22:38 
GeneralMy vote of 5 Pin
SleazyOtto9-Nov-11 8:27
SleazyOtto9-Nov-11 8:27 
GeneralRe: My vote of 5 Pin
Pranay Rana10-Dec-11 3:14
professionalPranay Rana10-Dec-11 3:14 
GeneralMy vote of 5 Pin
mandar130528-Sep-11 19:10
mandar130528-Sep-11 19:10 
GeneralRe: My vote of 5 Pin
Pranay Rana10-Dec-11 3:13
professionalPranay Rana10-Dec-11 3:13 
QuestionLearn SQL to LINQ Pin
Bob_shekar6-Jul-11 0:38
Bob_shekar6-Jul-11 0:38 
GeneralMy vote of 5 Pin
Akiii1238-Apr-11 18:56
Akiii1238-Apr-11 18:56 
I am a beginner and i found this article best suited....Thank you very much

Akiii
GeneralRe: My vote of 5 Pin
Pranay Rana12-Apr-11 21:02
professionalPranay Rana12-Apr-11 21:02 
GeneralRe: My vote of 5 Pin
Akiii12312-Apr-11 21:09
Akiii12312-Apr-11 21:09 
GeneralMy vote of 5 Pin
Аslam Iqbal30-Mar-11 2:34
professionalАslam Iqbal30-Mar-11 2:34 
GeneralRe: My vote of 5 Pin
Pranay Rana30-Mar-11 20:22
professionalPranay Rana30-Mar-11 20:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.