Querying LINQ to SQL: Basics - Filtering rows with conditions and implementing SQL IN operator using LINQ
(Page 5 of 6 )
“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 };
Next: Implementing LIKE and IS NULL operators using LINQ >>
More .NET Articles
More By Jagadish Chaterjee
