Querying LINQ to SQL: Basics

For the purpose of this article, I created a LINQ model using “LINQ to SQL designer” as follows:

Fig 01

All the queries in this article will focus on the above model. The image is linked to here due to size and clarity considerations.

The entire source code for this article is available in the form of a free downloadable zip file (CS and VB). The solution was developed using Microsoft Visual Studio 2008 Team Edition on Microsoft Windows Server 2003 Standard Edition with Microsoft SQL Server 2005 Developer Edition. I didn’t really test it in any other environment. I request that you post in the discussion area if you have any problems regarding execution.

Beginning to fetch data using LINQ (i.e. fetching rows from a table)

To begin fetching data, you must start with a query. As we already have a model created using “LINQ to SQL Designer,” we will start fetching data from that model using LINQ.

In general, to retrieve all rows from the Regions table, we would write a SQL SELECT as follows:

SELECT * FROM Region

The LINQ statement (in VB) for the above SELECT can be written as follows:

Dim q = From p In db.Regions _

Select p

To make it simpler, we can even omit the “Select” part as follows (only in VB):

Dim q = From p In db.Regions

The complete code for the above examples would be as follows:

Protected Sub btnSelectFromRegions_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSelectFromRegions.Click

Dim db As New NorthwindDataContext

Dim s As New IO.StringWriter

db.Log = s

Dim q = From p In db.Regions

'Dim q = From p In db.Regions _

' Select p

Me.GridView1.DataSource = q

Me.GridView1.DataBind()

Me.lblGenSQL.Text = s.ToString

End Sub

This is the code in C#:

protected void btnSelectFromRegions_Click(object sender, EventArgs e)

{

NorthwindDataContext db = new NorthwindDataContext();

StringWriter s = new StringWriter();

db.Log = s;

var q = from p in db.Regions

select p;

this.GridView1.DataSource = q;

this.GridView1.DataBind();

this.lblGenSQL.Text = s.ToString();

}

Let us go further with the following SELECT query:

SELECT RegionID, RegionDescription FROM Region

In the above case, we are specific for only certain columns (but not all columns). The LINQ query (in VB) for the above example would be as follows:

Dim q = From p In db.Regions _

Select p.RegionID, p.RegionDescription

The same in C# would be as follows:

var q = from p in db.Regions

select new { p.RegionID, p.RegionDescription };

Column aliasing and expression based columns using LINQ

Let us start with the following SELECT Query:

SELECT RegionID, RegionName = RegionDescription FROM Region

The following is the code that uses LINQ to represent the above (in VB):

Protected Sub btnColumnAlias_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnColumnAlias.Click

Dim db As New NorthwindDataContext

Dim s As New IO.StringWriter

db.Log = s

Dim q = From p In db.Regions _

Select p.RegionID, RegionName = p.RegionDescription

Me.GridView1.DataSource = q

Me.GridView1.DataBind()

Me.lblGenSQL.Text = s.ToString

End Sub

Translating into C#, we have:

protected void btnColumnAlias_Click(object sender, EventArgs e)

{

NorthwindDataContext db = new NorthwindDataContext();

StringWriter s = new StringWriter();

db.Log = s;

var q = from p in db.Regions

select new { p.RegionID, RegionName = p.RegionDescription };

this.GridView1.DataSource = q;

this.GridView1.DataBind();

this.lblGenSQL.Text = s.ToString();

}

We can also represent columns with .NET expressions as follows (in VB):

Dim q = From p In db.Orders _

Select p.OrderID, p.OrderDate, NoOfDays = Date.Today.Subtract(p.OrderDate).Days

We can also use the “LET” keyword for better representation as follows:

Dim q = From p In db.Orders _

Let u = Date.Today.Subtract(p.OrderDate).Days _

Select p.OrderID, p.OrderDate, NoOfDays = u

And we can use any number of “LET”s as follows:

Dim q = From p In db.Orders _

Let d = Date.Today.Subtract(p.OrderDate).Days _

Let y = Year(p.OrderDate) _

Let m = Month(p.OrderDate) _

Select p.OrderID, p.OrderDate, d, m, y

In C#,

var q = from p in db.Orders

let u = System.DateTime.Today.Subtract(p.OrderDate.Value).Days

select new { p.OrderID, p.OrderDate, NoOfDays = u };

Retrieving DISTINCT values using LINQ

Let us imagine that I have a SELECT query, like the following:

SELECT DISTINCT CustomerID FROM Orders

And now, we will have to represent the same thing using LINQ (in VB). The code is as follows:

Protected Sub btnDistinct_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnDistinct.Click

Dim db As New NorthwindDataContext

Dim s As New IO.StringWriter

db.Log = s

Dim q = From p In db.Orders _

Select p.CustomerID Distinct

Me.GridView1.DataSource = q

Me.GridView1.DataBind()

Me.lblGenSQL.Text = s.ToString

End Sub

The same can be accomplished in C# as follows:

protected void btnDistinct_Click(object sender, EventArgs e)

{

NorthwindDataContext db = new NorthwindDataContext();

StringWriter s = new StringWriter();

db.Log = s;

var q = (from p in db.Orders

select p.CustomerID).Distinct() ;

this.GridView1.DataSource = q;

this.GridView1.DataBind();

this.lblGenSQL.Text = s.ToString();

}

What if I have this query:

SELECT DISTINCT CustomerID, EmployeeID FROM Orders

The above query returns all rows with CustomerID and EmployeeID without repetition (combined DISTINCT and not individual DISTINCT). That means CustomerID and EmployeeID can repeat alone, but cannot repeat combined.

To make this possible using LINQ (in VB), the query would be as follows:

Dim q = From p In db.Orders _

Select p.CustomerID, p.EmployeeID Distinct

The same in C# would be as follows:

var q = (from p in db.Orders

select new { p.CustomerID, p.EmployeeID }).Distinct();

Sorting rows with ORDER BY using LINQ

Let us imagine that I have the following SELECT query:

SELECT OrderID, OrderDate FROM Orders

And now, we will represent the same thing using LINQ (in VB). This is the code:

Dim q = From p In db.Orders _

Select p.OrderID, p.OrderDate _

Order By OrderDate

The sorting order defaults to “ascending” when no specific order type is defined. We can define it as “descending” as follows:

Dim q = From p In db.Orders _

Select p.OrderID, p.OrderDate _

Order By OrderDate Descending

Nothing can stop you from defining it as “ascending” or “descending” in your query. The same can be achieved in C# as follows:

var q = from p in db.Orders

orderby p.OrderDate

select new { p.OrderID, p.OrderDate };

If multiple columns need to be sorted as they are provided in the following SELECT query,

SELECT OrderID, OrderDate FROM Orders

ORDER BY OrderDate DESC, OrderID DESC

then the relevant LINQ (in VB) would be as follows:

Dim q = From p In db.Orders _

Select p.OrderID, p.OrderDate _

Order By OrderDate Descending , OrderID Descending 

The same can be achieved in C# as follows:

var q = from p in db.Orders

orderby p.OrderDate descending, p.OrderID descending 

select new { p.OrderID, p.OrderDate };

Filtering rows with conditions and implementing SQL IN operator using LINQ

“WHERE” is one of the most important operators in LINQ and is used to filter rows. Let us imagine that we have the following SELECT query:

SELECT OrderID, OrderDate, Freight

FROM Orders

WHERE Freight > 500 AND Freight < 800

The relevant LINQ (in VB) would be as follows:

Dim q = From p In db.Orders _

Where p.Freight > 500 And p.Freight < 800 _

Select p.OrderID, p.OrderDate, p.Freight

The same thing in C# would be as follows:

var q = from p in db.Orders

where p.Freight > 500 && p.Freight < 800

orderby p.Freight descending

select new { p.OrderID, p.OrderDate, p.Freight };

The WHERE operator in a SELECT statement is equipped with several SQL operators, such as IN, LIKE, IS NULL, etc. LINQ does not directly support a few of those operators. Instead, we will have to trick LINQ using the .NET Framework. Let us see how.

The following SELECT query...

SELECT OrderID, OrderDate, Freight

FROM Orders

WHERE OrderID IN (10257, 10300, 10292)

can be written in LINQ (in VB) as follows:

Dim arOrderIDs() As Integer = {10257, 10300, 10292}

Dim q = From p In db.Orders _

Where arOrderIDs.Contains(p.OrderID) _

Select p.OrderID, p.OrderDate, p.Freight

The NOT can be implemented as follows:

Dim q = From p In db.Orders _

Where Not (arOrderIDs.Contains(p.OrderID)) _

Select p.OrderID, p.OrderDate, p.Freight

In C# (including the NOT operator), the same thing looks like the following:

int[] arOrderIDs = new int[] { 10257, 10300, 10292 };

var q = from p in db.Orders

where !(arOrderIDs.Contains(p.OrderID))

select new { p.OrderID, p.OrderDate, p.Freight };

Implementing LIKE and IS NULL operators using LINQ

“LIKE” is another important operator in SQL that filters rows on string patterns. The LINQ syntax of VB.NET 2008 has great flexibility in terms of using the LIKE operator. If you are familiar with SQL LIKE, you should easily understand the following examples:

Dim q = From p In db.Customers _

Where p.CompanyName Like "A*" _

Select p.CustomerID, p.ContactName, p.CompanyName

Dim q = From p In db.Customers _

Where p.CompanyName Like "?A*" _

Select p.CustomerID, p.ContactName, p.CompanyName

Dim q = From p In db.Customers _

Where Not (p.CompanyName Like "?A*") _

Select p.CustomerID, p.ContactName, p.CompanyName

C# does not directly support the LIKE operator in LINQ. Instead, we have to work as follows:

var q = from p in db.Customers

where System.Data.Linq.SqlClient.SqlMethods.Like(p.CompanyName, "_A%")

select new { p.CustomerID, p.ContactName, p.CompanyName };

Make sure that SqlMethods is not compulsory and just for flexibility. If you can achieve the same thing using built-in .NET Framework functions (like string functions), you can also use them in the conditional expressions.

Now, let us see how we can work with NULLs using LINQ. Consider the following VB.NET statement:

Dim q = From p In db.Customers _

Where p.Region Is Nothing _

Select p.CustomerID, p.ContactName, p.CompanyName, p.Region

The above returns the customer details of the customers whose region is NULL. We can negate the above statement as follows:

Dim q = From p In db.Customers _

Where p.Region IsNot Nothing _

Select p.CustomerID, p.ContactName, p.CompanyName, p.Region

The same can be written in C# as follows:

var q = from p in db.Customers

where p.Region == null

select new { p.CustomerID, p.ContactName, p.Region };

var q = from p in db.Customers

where p.Region != null

select new { p.CustomerID, p.ContactName, p.Region };

We will go further in-depth in my upcoming articles. I hope you enjoyed this article and any suggestions, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com.