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.
SELECT with Implicit join
SELECT with Implicit joinThis 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:
-
Generated SQL for MySQL DBMS
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
-
Generated SQL for Oracle DBMS
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"
-
Generated SQL for MS SQL Server DBMS
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]
-
Generated SQL for PostgreSQL DBMS
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"
-
Generated SQL for SQLite DBMS
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))
-
Results:
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.
SELECT with two from clauses
SELECT with two from clausesThis 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:
-
Generated SQL for MySQL DBMS
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
-
Generated SQL for Oracle DBMS
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"
-
Generated SQL for MS SQL Server DBMS
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]
-
Generated SQL for PostgreSQL DBMS
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"
-
Generated SQL for SQLite DBMS
SELECT t1.ContactName AS Contactname, t2.OrderID AS Orderid, t2.OrderDate AS Orderdate FROM Customers t1, Orders t2 WHERE t1.CustomerID = t2.CustomerID
-
Results:
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.
SELECT with two from clauses and where clauses
SELECT with two from clauses and where clausesThis 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:
-
Generated SQL for MySQL DBMS
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
-
Generated SQL for Oracle DBMS
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
-
Generated SQL for MS SQL Server DBMS
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]
-
Generated SQL for PostgreSQL DBMS
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
-
Generated SQL for SQLite DBMS
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
-
Results:
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.
SELECT with more complicated where clause
SELECT with more complicated where clauseIn 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:
-
Generated SQL for MySQL DBMS
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%
-
Generated SQL for Oracle DBMS
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%
-
Generated SQL for MS SQL Server DBMS
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%]
-
Generated SQL for PostgreSQL DBMS
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%
-
Generated SQL for SQLite DBMS
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%
-
Results:
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.
SELECT with ordering and paging
SELECT with ordering and pagingIn 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:
-
Generated SQL for MySQL DBMS
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
-
Generated SQL for Oracle DBMS
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
-
Generated SQL for MS SQL Server DBMS
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]
-
Generated SQL for PostgreSQL DBMS
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
-
Generated SQL for SQLite DBMS
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
-
Results:
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.
SELECT with grouping and aggregate functions
SELECT with grouping and aggregate functionsUsing 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:
-
Generated SQL for MySQL DBMS:
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 -
Generated SQL for Oracle DBMS:
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"
-
Generated SQL for MS SQL Server DBMS:
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]
-
Generated SQL for PostgreSQL DBMS:
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"
-
Generated SQL for SQLite DBMS:
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))
-
Results:
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.
© 2010 - 2023 Devart. All rights reserved.