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