In this article you will find several examples of LINQ queries that may be useful for your application development. For the wide list refer to 101 LINQ Samples.
You can download the database scripts from our site to run the following scripts.
This query shows to us how we can use implicit join in LINQ-statements. We will join Products and Category entities by the Product.Category field.
LINQ:
var query = from product in db.Products select new { Name = product.Productname, Price = product.Unitprice, Category = product.Category.Categoryname }; foreach (var element in query) { Console.WriteLine("\n" + element.Name + " from " + element.Category + " costs " + element.Price + " per unit."); }
Dim query = From product In db.Products _ Select New With { .Name = product.Productname, _ .Price = product.Unitprice, _ .Category = product.Category.Categoryname } For Each element In query Console.WriteLine(VbCrLf & element.Name & " from " & element.Category & " costs " & element.Price & " per unit.") Next
The native SQL-statements generated for the LINQ code are presented below:
SELECT t1.ProductName AS Productname, t1.UnitPrice AS Unitprice, t2.CategoryName AS Categoryname FROM northwindef.products t1 LEFT OUTER JOIN northwindef.categories t2 ON t1.CategoryID = t2.CategoryID
SELECT t1."ProductName" AS "Productname", t1."UnitPrice" AS "Unitprice", t2."CategoryName" AS "Categoryname" FROM NORTHWINDEF."Products" t1 LEFT OUTER JOIN NORTHWINDEF."Categories" t2 ON t1."CategoryID" = t2."CategoryID"
SELECT [t0].[ProductName] AS [Name], [t0].[UnitPrice] AS [Price], [t1].[CategoryName] AS [Category] FROM [dbo].[Products] AS [t0] LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON [t1].[CategoryID] = [t0].[CategoryID]
SELECT t1."ProductName" AS "Productname", t1."UnitPrice" AS "Unitprice", t2."CategoryName" AS "Categoryname" FROM public."Products" t1 LEFT OUTER JOIN public."Categories" t2 ON t1."CategoryID" = t2."CategoryID"
SELECT t1.ProductName AS Productname, t1.UnitPrice AS Unitprice, t2.CategoryName AS Categoryname FROM Products t1 LEFT OUTER JOIN Categories t2 ON (t1.CategoryID = t2.CategoryID) OR ((t1.CategoryID IS NULL) AND (t2.CategoryID IS NULL))
Chai from Beverages costs 18 per unit.
Chang from Beverages costs 19 per unit.
...
As we can easily see, this query takes a subset of Product columns. Also we have an implicit join of the Products and Categories tables in the product.Category access.
This query shows to us how we can build a query with two from clauses. We will select data from two entities: Customers and Orders.
LINQ:
var query = from c in db.Customers from o in c.Orders select new { c.Contactname, o.Orderid, o.Orderdate }; foreach (var element in query) { Console.WriteLine("\n" + element.Contactname + " made order " + element.Orderid + " on " + element.Orderdate + "."); }
Dim query = From c In db.Customers _ From o in db.Orders _ Select New With { c.Contactname, o.Orderid, o.Orderdate } For Each element In query Console.WriteLine(VbCrLf & element.Contactname & " made order " & element.Orderid & _ " on " & element.Orderdate & ".") Next
The native SQL-statements generated for the LINQ code are presented below:
SELECT t1.ContactName AS Contactname, t2.OrderID AS Orderid, t2.OrderDate AS Orderdate FROM northwindef.customers t1, northwindef.orders t2 WHERE t1.CustomerID = t2.CustomerID
SELECT t1."ContactName" AS "Contactname", t2."OrderID" AS "Orderid", t2."OrderDate" AS "Orderdate" FROM NORTHWINDEF."Customers" t1, NORTHWINDEF."Orders" t2 WHERE t1."CustomerID" = t2."CustomerID"
SELECT [t0].[ContactName] AS [Contactname], [t1].[OrderID] AS [Orderid], [t1].[OrderDate] AS [Orderdate] FROM [dbo].[Customers] AS [t0], [dbo].[Orders] AS [t1] WHERE [t1].[CustomerID] = [t0].[CustomerID]
SELECT t1."ContactName" AS "Contactname", t2."OrderID" AS "Orderid", t2."OrderDate" AS "Orderdate" FROM public."Customers" t1, public."Orders" t2 WHERE t1."CustomerID" = t2."CustomerID"
SELECT t1.ContactName AS Contactname, t2.OrderID AS Orderid, t2.OrderDate AS Orderdate FROM Customers t1, Orders t2 WHERE t1.CustomerID = t2.CustomerID
Maria Anders made order 10643 on 08.25.1997.
Maria Anders made order 10692 on 10.03.1997.
...
So, we are able to query two tables in one query easily using LinqConnect.
This query is very similar to previous but we define a conditions to Customers and Orders entities. We select only customers from "USA" and orders older than 1998.
LINQ:
var query = from c in db.Customers where c.Country == "USA" from o in c.Orders where o.Orderdate >= new DateTime(1998, 1, 1) select new { c.Contactname, o.Orderid, o.Orderdate }; foreach (var element in query) { Console.WriteLine("\n" + element.Contactname + " made order " + element.Orderid + " on " + element.Orderdate + "."); }
Dim query = From c In db.Customers _ Where c.Country = "USA" _ From o In db.Orders _ Where o.Orderdate >= New DateTime (1998, 1, 1) _ Select New With { c.Contactname, o.Orderid, o.Orderdate } For Each element In query Console.WriteLine(VbCrLf & element.Contactname & " made order " & element.Orderid &_ " on " & element.Orderdate & ".") Next
The native SQL-statements generated for the LINQ code are presented below:
SELECT t1.ContactName, t2.OrderID, t2.OrderDate FROM northwindef.customers t1, northwindef.orders t2 WHERE (t2.OrderDate >= :p0) AND (t1.Country = :p1) AND (t1.CustomerID = t2.CustomerID) ParameterName = p0 DbType = DateTime Value = 01.01.1998 0:00:00 ParameterName = p1 DbType = AnsiString Value = USA
SELECT t1."ContactName", t2."OrderID", t2."OrderDate" FROM NORTHWINDEF."Customers" t1, NORTHWINDEF."Orders" t2 WHERE (t2."OrderDate" >= :p0) AND (t1."Country" = :p1) AND (t1."CustomerID" = t2."CustomerID") ParameterName = p0 DbType = DateTime Value = 01.01.1998 0:00:00 ParameterName = p1 DbType = String Value = USA
SELECT [t0].[ContactName] AS [Contactname], [t1].[OrderID] AS [Orderid], [t1].[OrderDate] AS [Orderdate] FROM [dbo].[Customers] AS [t0], [dbo].[Orders] AS [t1] WHERE ([t1].[OrderDate] >= @p0) AND ([t0].[Country] = @p1) AND ([t1].[CustomerID] = [t0].[CustomerID]) -- @p0: Input DateTime (Size = 0; Prec = 0; Scale = 0) [01.01.1998 0:00:00] -- @p1: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [USA]
SELECT t1."ContactName", t2."OrderID", t2."OrderDate" FROM public."Customers" t1, public."Orders" t2 WHERE (t2."OrderDate" >= :p0) AND (t1."Country" = :p1) AND (t1."CustomerID" = t2."CustomerID") ParameterName = p0 DbType = DateTime Value = 01.01.1998 0:00:00 ParameterName = p1 DbType = AnsiString Value = USA
SELECT t1.ContactName AS ContactName, t2.OrderID AS OrderID, t2.OrderDate AS OrderDate FROM Customers t1, Orders t2 WHERE (t2.OrderDate >= :p0) AND (t1.Country = :p1) AND (t1.CustomerID = t2.CustomerID) ParameterName = p0 DbType = DateTime Value = 01.01.1998 0:00:00 ParameterName = p1 DbType = String Value = USA
Howard Snyder made order 10816 on 01.06.1998.
Howard Snyder made order 10936 on 03.09.1998.
...
Here we can see the query which narrows customers to only USA-located ones and orders are narrowed to the ones after Jan 1, 1999. It is good solution from the performance point of view.
In this query we define complicated Where clause - we define customer location and first symbol of its name.
LINQ:
var query = from c in db.Customers where c.Country == "USA" && c.Contactname.StartsWith("J") select new { c.Contactname, c.Address }; foreach (var element in query) { Console.WriteLine("\n" + element.Contactname + " is located at " + element.Address); }
Dim query = From c In db.Customers _ Where c.Country = "USA" And c.Contactname.StartsWith("J") _ Select New With { c.Contactname, c.Address } For Each element In query Console.WriteLine(VbCrLf & element.Contactname & " is located at " & element.Address) Next
The native SQL-statements generated for the LINQ code are presented below:
SELECT t1.ContactName, t1.Address FROM northwindef.customers t1 WHERE (t1.Country = :p0) AND (BINARY t1.ContactName LIKE :p1) ParameterName = p0 DbType = AnsiString Value = USA ParameterName = p1 DbType = AnsiString Value = J%
SELECT t1."ContactName", t1."Address" FROM NORTHWINDEF."Customers" t1 WHERE (t1."Country" = :p0) AND (t1."ContactName" LIKE :p1) ParameterName = p0 DbType = String Value = USA ParameterName = p1 DbType = String Value = J%
SELECT [t0].[ContactName] AS [Contactname], [t0].[Address] FROM [dbo].[Customers] AS [t0] WHERE ([t0].[Country] = @p0) AND ([t0].[ContactName] LIKE @p1) -- @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [USA] -- @p1: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [J%]
SELECT t1."ContactName", t1."Address" FROM public."Customers" t1 WHERE (t1."Country" = :p0) AND (t1."ContactName" LIKE :p1) ParameterName = p0 DbType = AnsiString Value = USA ParameterName = p1 DbType = AnsiString Value = J%
SELECT t1.ContactName AS ContactName, t1.Address AS Address FROM Customers t1 WHERE (t1.Country = :p0) AND (t1.ContactName LIKE :p1) ParameterName = p0 DbType = String Value = USA ParameterName = p1 DbType = String Value = J%
John Steel is located at 12 Orchestra Terrace
Jaime Yorres is located at 87 Polk St. Suite 5
...
So, where clause can be compound and contain some functions.
In this query we define conditions for paging and ordering. To do it orderby clause, and Skip and Take functions are used.
LINQ:
var query = (from p in db.Products orderby p.Productname select p).Skip(10).Take(10); foreach (var element in query) { Console.WriteLine("\n" + element.Productname); }
Dim query = (From p In db.Products _ Order By p.Productname _ Select p).Skip(10).Take(10) For Each element In query Console.WriteLine(VbCrLf & element.Productname) Next
The native SQL-statements generated for the LINQ code are presented below:
SELECT t1.ProductID, t1.ProductName, t1.SupplierID, t1.CategoryID, t1.QuantityPerUnit, t1.UnitPrice, t1.UnitsInStock, t1.UnitsOnOrder, t1.ReorderLevel, t1.Discontinued, t1.DiscontinuedDate FROM northwindef.products t1 ORDER BY t1.ProductName LIMIT :p0, :p1 ParameterName = p0 DbType = Int32 Value = 10 ParameterName = p1 DbType = Int32 Value = 10
SELECT t1."ProductID", t1."ProductName", t1."SupplierID", t1."CategoryID", t1."QuantityPerUnit", t1."UnitPrice", t1."UnitsInStock", t1."UnitsOnOrder", t1."ReorderLevel", t1."Discontinued", t1."DiscontinuedDate" FROM ( SELECT t2."ProductID", t2."ProductName", t2."SupplierID", t2."CategoryID", t2."QuantityPerUnit", t2."UnitPrice", t2."UnitsInStock", t2."UnitsOnOrder", t2."ReorderLevel", t2."Discontinued", t2."DiscontinuedDate", ROW_NUMBER() OVER (ORDER BY t2."ProductName") AS "rnum" FROM NORTHWINDEF."Products" t2 ORDER BY t2."ProductName" ) t1 WHERE (t1."rnum" > :p0) AND (t1."rnum" <= :p1) ParameterName = p0 DbType = Int32 Value = 10 ParameterName = p1 DbType = Int32 Value = 20
SELECT [t1].[ProductID] AS [Productid], [t1].[ProductName] AS [Productname], [t1].[SupplierID] AS [Supplierid], [t1].[CategoryID] AS [Categoryid], [t1].[QuantityPerUnit] AS [Quantityperunit], [t1].[UnitPrice] AS [Unitprice], [t1].[UnitsInStock] AS [Unitsinstock], [t1].[UnitsOnOrder] AS [Unitsonorder], [t1].[ReorderLevel] AS [Reorderlevel], [t1].[Discontinued], [t1].[DiscontinuedDate] AS [Discontinueddate], [t1].[LastEditDate] AS [Lasteditdate], [t1].[CreationDate] AS [Creationdate] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ProductName]) AS [ROW_NUMBER], [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued], [t0].[DiscontinuedDate], [t0].[LastEditDate], [t0].[CreationDate] FROM [dbo].[Products] AS [t0] ) AS [t1] WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1 ORDER BY [t1].[ROW_NUMBER] -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10] -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
SELECT t1."ProductID", t1."ProductName", t1."SupplierID", t1."CategoryID", t1."QuantityPerUnit", t1."UnitPrice", t1."UnitsInStock", t1."UnitsOnOrder", t1."ReorderLevel", t1."Discontinued", t1."DiscontinuedDate" FROM public."Products" t1 ORDER BY t1."ProductName" LIMIT :p0 OFFSET :p1 ParameterName = p0 DbType = Int32 Value = 10 ParameterName = p1 DbType = Int32 Value = 10
SELECT t1.ProductID AS ProductID, t1.ProductName AS ProductName, t1.SupplierID AS SupplierID, t1.CategoryID AS CategoryID, t1.QuantityPerUnit AS QuantityPerUnit, t1.UnitPrice AS UnitPrice, t1.UnitsInStock AS UnitsInStock, t1.UnitsOnOrder AS UnitsOnOrder, t1.ReorderLevel AS ReorderLevel, t1.Discontinued AS Discontinued, t1.DiscontinuedDate AS DiscontinuedDate FROM Products t1 ORDER BY t1.ProductName LIMIT :p0, :p1 ParameterName = p0 DbType = Int32 Value = 10 ParameterName = p1 DbType = Int32 Value = 10
Chocolade
Cote de Blaye
...
This type of queries is useful in the paging tasks. As you see, the peculiarities of differents DBMSs are taken into account.
Using of aggregate functions and group by clause is shown in following query.
LINQ:
var query = from p in db.Products group p by p.Category into g select new { Category = g.Key.Categoryname, Max = g.Max(c=>c.Unitprice) }; foreach (var element in query) { Console.WriteLine("\n" + "The most expensive product in " + element.Category + " category costs " + element.Max); }
Dim query = From p In db.Products _ Group p By p.Category Into Group _ Select New With { .Category = Category.Categoryname, .Max = Group.Max(Function(c) c.Unitprice) } For Each element In query Console.WriteLine(VbCrLf & "The most expensive product in " & element.Category & _ " category costs " & element.Max) Next
The native SQL-statements generated for the LINQ code are presented below:
SELECT t3.CategoryName AS Categoryname, t1.C1 FROM ( SELECT t2.CategoryID, MAX(t2.UnitPrice) AS C1 FROM northwindef.products t2 GROUP BY t2.CategoryID ) t1 LEFT OUTER JOIN northwindef.categories t3 ON t1.CategoryID = t3.CategoryID
SELECT t3."CategoryName" AS "Categoryname", t1.C1 FROM ( SELECT t2."CategoryID", MAX(t2."UnitPrice") AS C1 FROM NORTHWINDEF."Products" t2 GROUP BY t2."CategoryID" ) t1 LEFT OUTER JOIN NORTHWINDEF."Categories" t3 ON t1."CategoryID" = t3."CategoryID"
SELECT [t2].[CategoryName] AS [Category], [t1].[value] AS [Max] FROM ( SELECT MAX([t0].[UnitPrice]) AS [value], [t0].[CategoryID] FROM [dbo].[Products] AS [t0] GROUP BY [t0].[CategoryID] ) AS [t1] LEFT OUTER JOIN [dbo].[Categories] AS [t2] ON [t2].[CategoryID] = [t1].[CategoryID]
SELECT t3."CategoryName" AS "Categoryname", t1."C1" FROM ( SELECT t2."CategoryID", MAX(t2."UnitPrice") AS "C1" FROM public."Products" t2 GROUP BY t2."CategoryID" ) t1 LEFT OUTER JOIN public."Categories" t3 ON t1."CategoryID" = t3."CategoryID"
SELECT t3.CategoryName AS Categoryname, t1.C1 AS C1 FROM ( SELECT t2.CategoryID AS CategoryID, MAX(t2.UnitPrice) AS C1 FROM Products t2 GROUP BY t2.CategoryID ) t1 LEFT OUTER JOIN Categories t3 ON (t1.CategoryID = t3.CategoryID) OR ((t1.CategoryID IS NULL) AND (t3.CategoryID IS NULL))
The most expensive product in Beverages category costs 263,5
The most expensive product in Condiments category costs 43,9
The most expensive product in Confections category costs 81
...
Usage of grouping and aggregating functions is easy when LinqConnect is applied.
These simple examples should help one to make some ideas of how to use LinqConnect as a convenient way to work with different databases.