Convert SQL to LINQ

LINQ (Language Integrated Query) is Microsoft's technology that provides .NET languages the capability to query data of all types. These types include in-memory arrays and collections, databases, XML documents, and more.

LINQ is such a vast topic that you will be better off learning comprehensively by buying a book on this superb technology. I'd highly recommend Steve Eichert's and Fabrice Marguerie's "LINQ in Action" book available in Amazon.




In this post, I'll provide examples for converting SQL queries into LINQ (in VB.NET and C#).
These samples are taken from VB Team's blog: http://blogs.msdn.com/b/vbteam/archive/tags/converting+sql+to+linq/default.aspx. Since all their samples are in VB.NET, I'll include C# for completeness.

SELECT * Query

SQL

SELECT *
FROM CustomerTable

LINQ(VB.NET)

From Contact In CustomerTable

LINQ(C#)

from Contact in CustomerTable
select Contact

SELECT Query

SQL

SELECT Name CustomerName, CustomerID ID
FROM Customers

LINQ(VB.NET)

From cust In Customers _
Select CustomerName = cust.Name, ID = cust.CustomerID _
Order By ID

LINQ(C#)

from cust in Customers
select new(){CustomerName=cust.Name, ID=cust.CustomerID }

SELECT... WHERE Query
SQL

SELECT * FROM CustomerTable
WHERE State = “WA”

LINQ(VB.NET)

From Contact In CustomerTable _
Where Contact.State = “WA”

LINQ(C#)

from Contact in CustomerTable
where Contact.State=="WA"
select Contact

SELECT DISTINCT Query
SQL

SELECT DISTINCT Name, Address
FROM CustomerTable

LINQ(VB.NET)

From Contact In CustomerTable _
Select Contact.Name, Contact.Address _
Distinct

LINQ(C#)

(from Contact in CustomerTable
select new(){Contact.Name,Contact.Address}).Distinct()

AND Operator Query
SQL

SELECT * FROM CustomerTable
WHERE City = “Seattle” AND Zip = “98122”

LINQ(VB.NET)

From Contact In CustomerTable _
Where Contact.City = “Seattle” And Contact.Zip = “98122”

LINQ(C#)

from Contact in CustomerTable
where Contact.City=="Seattle" && Contact.Zip=="98122"
select Contact

BETWEEN Operator Query
SQL

SELECT * FROM OrderTable
WHERE OrderDate BETWEEN ‘Sept-22-2007’ AND ‘Sept-29-2007’

LINQ(VB.NET)

From Shipment In OrderTable _
Where (Shipment.OrderDate > #9/22/2007#) _
    And (Shipment.OrderDate < #9/29/2007#)

LINQ(C#)

from Shipment in OrderTable
where Shipment.OrderDate > new Date(2007,9,22) && 
Shipment.OrderDate <  new Date(2007,9,29)
select Shipment

Order By Query
SQL

SELECT * FROM CustomerTable
ORDER BY Phone

LINQ(VB.NET)

From Contact In CustomerTable _
Order By Contact.Phone

LINQ(C#)

from Contact in CustomerTable
orderby Contact.Phone
select Contact

Order By ASC/DESC Query
SQL

SELECT * FROM CustomerTable
ORDER BY Phone ASC, Name DESC

LINQ(VB.NET)

From Contact In CustomerTable _
Order By Contact.Phone Ascending, Contact.Name Descending

LINQ(C#)

from Contact in CustomerTable
orderby Contact.Phone ascending, Contact.Name descending
select Contact

I'll update this blog later and add more samples...