LINQ标准查询操作符（二）——Join、GroupJoin、GroupBy、Concat、

最新推荐文章于 2026-04-23 10:53:50 发布

转载最新推荐文章于 2026-04-23 10:53:50 发布 · 1.2k 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

原文链接：https://blog.csdn.net/WuLex

标签

#linq #数据库 #sql

LINQ|表达式树专栏收录该内容

96 篇文章

订阅专栏

本文详细解析了LINQ中的Join和GroupJoin操作，展示了如何通过这两个操作符进行数据源的关联和分组，包括内连接示例、外连接以及返回'主键-外键集合'。此外，还介绍了分组操作符GroupBy的应用，以产品类别为例，对数据进行分类汇总。

四、联接操作符

联接是指将一个数据源对象与另一个数据源对象进行关联或者联合的操作。这两个数据源对象通过一个共同的值或者属性进行关联。

LINQ有两个联接操作符：Join和GroupJoin。

1. `Join`

Join操作符类似于T-SQL中的inner join，它将两个数据源相联接，根据两个数据源中相等的值进行匹配。例如，可以将产品表与产品类别表相联接，得到产品名称和与其相对应的类别名称。以下的代码演示了这一点：

//查询语法
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();

生成的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)

//方法语法
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();

生成的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]

以上代码为表述清晰加入了一个条件“where p.CategoryID == 1”，即仅返回产品类别ID为1的所有产品。

生成的sql语句略有不同。

2. `GroupJoin`

GroupJoin操作符常应用于返回“主键对象-外键对象集合”形式的查询，例如“产品类别-此类别下的所有产品”。以下的代码演示了这一点：

//查询语法
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();
//方法语法
var q =
    db.Categories
    .GroupJoin
    (
       db.Products,
       c => c.CategoryID,
       p => p.CategoryID,
       (c, p) => new
       {
           c.CategoryName,
           Products = p
       }
    )
    .ToList();

生成的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

返回的结果为：
在这里插入图片描述

五、分组操作符

分组是根据一个特定的值将序列中的元素进行分组。LINQ只包含一个分组操作符：GroupBy。

下面的示例中使用了产品表，以CategoryID作为分组关键值，按照产品类别对产品进行了分组。

//查询语法
var query =
    (from p in db.Products
     group p by p.CategoryID).ToList();
//方法语法
var q =
    db.Products
    .GroupBy(p => p.CategoryID)
    .ToList();

生成的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

在这里插入图片描述
执行GroupBy得到的序列中包含的元素类型为IGrouping<TKey, T>，其Key属性代表了分组时使用的关键值，遍历IGrouping<TKey, T>元素可以读取到每一个T类型。在此示例中，对应的元素类型为IGrouping<int, Products>，其Key属性即为类别ID，遍历它可以读取到每一个产品对象。

六、串联操作符

串联是一个将两个集合联接在一起的过程。在LINQ中，这个过程通过Concat操作符来实现。

在下面的示例中，将会把类别名称串联在产品名称之后：

//方法语法
            var q =
                db.Products
                .Select(p => p.ProductName)
                .Concat
               (
                    db.Categories.Select(c => c.CategoryName)
                )
                .ToList();

生成的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]