LinqConnect Documentation
In This Topic
    LINQ Query Samples
    In This Topic
    LINQ Query Samples
    LinqConnect Documentation
    LINQ Query Samples
    [email protected]

    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

    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.

    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

    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.

    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

    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.

    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

    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.

    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

    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.

    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

    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.

    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.