4, Join operator

Join refers to the operation of associating or associating one data source object with another data source object. The two data source objects are associated by a common value or attribute.

LINQ has two Join operators: Join and GroupJoin.

1. Join

The Join operator is similar to the inner join in T-SQL. It joins two data sources and matches them according to the equal values in the two data sources. For example, you can Join the product table with the product category table to obtain the product name and its corresponding category name. The following code demonstrates this:

//Query syntax
var query =
    (from p in db.Products
     join c in db.Categories on p.CategoryID equals c.CategoryID
     where p.CategoryID == 1
     select new { p.ProductID, p.ProductName, c.CategoryID, c.CategoryName }).ToList();

Generated sql:

SELECT 
    [Extent1].[ProductID] AS [ProductID], 
    [Extent1].[ProductName] AS [ProductName], 
    [Extent2].[CategoryID] AS [CategoryID], 
    [Extent2].[CategoryName] AS [CategoryName]
    FROM  [dbo].[Products] AS [Extent1]
    INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
    WHERE (1 = [Extent1].[CategoryID]) AND ([Extent1].[CategoryID] IS NOT NULL)

//Method syntax
var q =
    db.Products
    .Join
    (
        db.Categories,
        p => p.CategoryID,
        c => c.CategoryID,
        (p, c) => new { p.ProductID, p.ProductName, c.CategoryID, c.CategoryName }
    )
    .Where(p => p.CategoryID == 1)
    .ToList();

Generated sql:

SELECT 
    [Extent1].[ProductID] AS [ProductID], 
    [Extent1].[ProductName] AS [ProductName], 
    [Extent2].[CategoryID] AS [CategoryID], 
    [Extent2].[CategoryName] AS [CategoryName]
    FROM  [dbo].[Products] AS [Extent1]
    INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
    WHERE 1 = [Extent2].[CategoryID]

For clarity, the above code adds a condition "where p.CategoryID == 1", that is, only all products with product category ID of 1 are returned.

The generated sql statements are slightly different.

2. GroupJoin

The GroupJoin operator is often used to return queries in the form of "primary key object - foreign key object collection", such as "product category - all products under this category". The following code demonstrates this:

//Query syntax
var query =
    (from c in db.Categories
     join p in db.Products on c.CategoryID equals p.CategoryID into r
     select new
     {
         c.CategoryName,
         Products = r
     }).ToList();
//Method syntax
var q =
    db.Categories
    .GroupJoin
    (
       db.Products,
       c => c.CategoryID,
       p => p.CategoryID,
       (c, p) => new
       {
           c.CategoryName,
           Products = p
       }
    )
    .ToList();

Generated sql:

SELECT 
    [Project1].[CategoryID] AS [CategoryID], 
    [Project1].[CategoryName] AS [CategoryName], 
    [Project1].[C1] AS [C1], 
    [Project1].[ProductID] AS [ProductID], 
    [Project1].[ProductName] AS [ProductName], 
    [Project1].[SupplierID] AS [SupplierID], 
    [Project1].[CategoryID1] AS [CategoryID1], 
    [Project1].[QuantityPerUnit] AS [QuantityPerUnit], 
    [Project1].[UnitPrice] AS [UnitPrice], 
    [Project1].[UnitsInStock] AS [UnitsInStock], 
    [Project1].[UnitsOnOrder] AS [UnitsOnOrder], 
    [Project1].[ReorderLevel] AS [ReorderLevel], 
    [Project1].[Discontinued] AS [Discontinued]
    FROM ( SELECT 
        [Extent1].[CategoryID] AS [CategoryID], 
        [Extent1].[CategoryName] AS [CategoryName], 
        [Extent2].[ProductID] AS [ProductID], 
        [Extent2].[ProductName] AS [ProductName], 
        [Extent2].[SupplierID] AS [SupplierID], 
        [Extent2].[CategoryID] AS [CategoryID1], 
        [Extent2].[QuantityPerUnit] AS [QuantityPerUnit], 
        [Extent2].[UnitPrice] AS [UnitPrice], 
        [Extent2].[UnitsInStock] AS [UnitsInStock], 
        [Extent2].[UnitsOnOrder] AS [UnitsOnOrder], 
        [Extent2].[ReorderLevel] AS [ReorderLevel], 
        [Extent2].[Discontinued] AS [Discontinued], 
        CASE WHEN ([Extent2].[ProductID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[Categories] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Products] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
    )  AS [Project1]
    ORDER BY [Project1].[CategoryID] ASC, [Project1].[C1] ASC

The returned results are:

5, Grouping operator

Grouping is to group elements in a sequence according to a specific value. LINQ contains only one grouping operator: GroupBy.

In the following example, the product table is used to group products by product category with CategoryID as the grouping key value.

//Query syntax
var query =
    (from p in db.Products
     group p by p.CategoryID).ToList();
//Method syntax
var q =
    db.Products
    .GroupBy(p => p.CategoryID)
    .ToList();

Generated sql:

SELECT 
    [Project2].[C1] AS [C1], 
    [Project2].[CategoryID] AS [CategoryID], 
    [Project2].[C2] AS [C2], 
    [Project2].[ProductID] AS [ProductID], 
    [Project2].[ProductName] AS [ProductName], 
    [Project2].[SupplierID] AS [SupplierID], 
    [Project2].[CategoryID1] AS [CategoryID1], 
    [Project2].[QuantityPerUnit] AS [QuantityPerUnit], 
    [Project2].[UnitPrice] AS [UnitPrice], 
    [Project2].[UnitsInStock] AS [UnitsInStock], 
    [Project2].[UnitsOnOrder] AS [UnitsOnOrder], 
    [Project2].[ReorderLevel] AS [ReorderLevel], 
    [Project2].[Discontinued] AS [Discontinued]
    FROM ( SELECT 
        [Distinct1].[CategoryID] AS [CategoryID], 
        1 AS [C1], 
        [Extent2].[ProductID] AS [ProductID], 
        [Extent2].[ProductName] AS [ProductName], 
        [Extent2].[SupplierID] AS [SupplierID], 
        [Extent2].[CategoryID] AS [CategoryID1], 
        [Extent2].[QuantityPerUnit] AS [QuantityPerUnit], 
        [Extent2].[UnitPrice] AS [UnitPrice], 
        [Extent2].[UnitsInStock] AS [UnitsInStock], 
        [Extent2].[UnitsOnOrder] AS [UnitsOnOrder], 
        [Extent2].[ReorderLevel] AS [ReorderLevel], 
        [Extent2].[Discontinued] AS [Discontinued], 
        CASE WHEN ([Extent2].[ProductID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
        FROM   (SELECT DISTINCT 
            [Extent1].[CategoryID] AS [CategoryID]
            FROM [dbo].[Products] AS [Extent1] ) AS [Distinct1]
        LEFT OUTER JOIN [dbo].[Products] AS [Extent2] ON ([Distinct1].[CategoryID] = [Extent2].[CategoryID]) OR (([Distinct1].[CategoryID] IS NULL) AND ([Extent2].[CategoryID] IS NULL))
    )  AS [Project2]
    ORDER BY [Project2].[CategoryID] ASC, [Project2].[C2] ASC

The element type contained in the sequence obtained by executing GroupBy is igrouting < tkey, t >, and its Key attribute represents the Key value used in grouping. Each T type can be read by traversing igrouting < tkey, t > elements. In this example, the corresponding element type is igrouting < int, products >, and its Key attribute is the category ID. you can read each product object by traversing it.

6, Concatenation operator

Concatenation is the process of joining two sets together. In LINQ, this process is implemented through the Concat operator.

In the following example, the category name will be concatenated after the product name:

//Method syntax
            var q =
                db.Products
                .Select(p => p.ProductName)
                .Concat
               (
                    db.Categories.Select(c => c.CategoryName)
                )
                .ToList();

Generated sql:

SELECT 
    [UnionAll1].[ProductName] AS [C1]
    FROM  (SELECT 
        [Extent1].[ProductName] AS [ProductName]
        FROM [dbo].[Products] AS [Extent1]
    UNION ALL
        SELECT 
        [Extent2].[CategoryName] AS [CategoryName]
        FROM [dbo].[Categories] AS [Extent2]) AS [UnionAll1]

Return result 77 + 8 = 85