How to use SQL to LINQ ( Visual Representation )

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:

• Implicitly typed local variables
• Extension Methods
• Anonymous types
• Lambda Expressions
• Object and collection initialization

Here in this article, 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



UserClients



LINQ structure



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:

SELECT * FROM [User]

The LINQ query to do the above is:

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:



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:

Select FirstName, LastName from [User]

Now the LINQ query:

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:



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:

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

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

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:



For String Data

In order to filter strings, 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 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:

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

or

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

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



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:

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:

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:



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:

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:

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():



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:

--Ascending

Select * from [User] order by firstName

or:

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

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

or

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



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:

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

LINQ uses Group ... By to group data, so the query looks 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 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:



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:

//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, 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:

//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 the same function in the LINQ query, but it just use a ! (not) symbol for it. Here is the graphical representation:



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:

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:

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;



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 funsction

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:

• Coalescing operator - ??

Solution 1

We can use the ternary operator as in the below example 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" : m.MobileNo
           };

Solution 2

Use the special Coalescing operator operator (??) as in the below example, 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

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