LINQ关联3表实现GroupBy、Count和Sum及左外连接问题

场景说明 #

我尝试用LINQ关联3张DataTable但遇到问题，以下是我的表定义、目标SQL语句，以及自己编写的LINQ代码，请帮忙排查错误，同时想了解LINQ中如何实现LEFT OUTER JOIN。

DataTable定义（VB.NET） #

Dim dtTable1 As New DataTable()
dtTable1.Columns.Add("Table1Id", GetType(System.Int32))
dtTable1.Columns.Add("MainEntityName", GetType(System.String))

Dim dtTable2 As New DataTable()
dtTable2.Columns.Add("Table2Id", GetType(System.Int32))
dtTable2.Columns.Add("Table1Id", GetType(System.Int32))
dtTable2.Columns.Add("SubEntityName", GetType(System.String))
dtTable2.Columns.Add("JobType", GetType(System.Int32))
dtTable2.Columns.Add("JobValue", GetType(System.Decimal))

Dim dtTable3 As New DataTable()
dtTable3.Columns.Add("Table3Id", GetType(System.Int32))
dtTable3.Columns.Add("Table2Id", GetType(System.Int32))
dtTable3.Columns.Add("JobEntityCount", GetType(System.String))

dtTable1.Rows.Add(1, "Main Entity 1 Name")
dtTable1.Rows.Add(2, "Main Entity 2 Name")

dtTable2.Rows.Add(1, 1, "Sub Entity 1 Name", 1, 1000)
dtTable2.Rows.Add(2, 1, "Sub Entity 2 Name", 1, 2000)
dtTable2.Rows.Add(3, 1, "Sub Entity 3 Name", 1, 3000)
dtTable2.Rows.Add(4, 2, "Sub Entity 4 Name", 2, 4000)

dtTable3.Rows.Add(1, 1, 100)
dtTable3.Rows.Add(2, 1, 200)
dtTable3.Rows.Add(3, 1, 300)
dtTable3.Rows.Add(4, 3, 400)

目标SQL查询 #

SELECT COUNT(DISTINCT dbo.Table1.Table1Id) AS Table1IdCount, COUNT(DISTINCT dbo.Table2.Table2Id) AS Table2IdCount, dbo.Table2.JobType, SUM(dbo.Table2.JobValue) AS JobValue, SUM(Table3Summed.JobEntityCount) AS JobEntityCount
FROM dbo.Table1 
    INNER JOIN dbo.Table2 ON dbo.Table1.Table1Id = dbo.Table2.Table1Id 
    LEFT OUTER JOIN (  -- Create a summerised table
        SELECT Table2Id, SUM(JobEntityCount) AS JobEntityCount
        FROM dbo.Table3 AS Table3_1
        GROUP BY Table2Id
    ) AS Table3Summed ON dbo.Table2.Table2Id = Table3Summed.Table2Id
GROUP BY dbo.Table2.JobType

我编写的LINQ代码（存在错误） #

'create summerised table3
Dim table3Summed = From dr In dtTable3.AsEnumerable()
                    Group dr By Table2Id = dr.Field(Of Integer)("Table2Id") Into drg = Group
                    Select New With {
                            .Table2Id = Table2Id,
                            .JobEntityCount = drg.Count()}

Dim result = (From Table1 In dtTable1.AsEnumerable
                    Join Table2 In dtTable2.AsEnumerable
    On Table1.Field(Of Integer)("Table1Id") Equals Table2.Field(Of Integer)("Table1Id")
                    Join Table3 In table3Summed
    On Table2.Field(Of Integer)("Table2Id") Equals Table3.Table2Id
                    Group Table2 By ID = Table2.Field(Of Integer)("JobType") Into g = Group
                    Select New With
    {
    .Table1IdCount = (From x In g Select x.Field(Of Integer)("Table1Id")).Distinct.Count(),
    .Table2IdCount = (From x In g Select x.Field(Of Integer)("Table2Id")).Distinct.Count(),
    .JobType = ID,
    .JobValue = g.Sum(Function(r) r.Field(Of Decimal)("JobValue"))
    .JobEntityCount = table3Summed.JobEntityCount,
    })

错误排查 #

1. Table3聚合逻辑错误：目标SQL中是对JobEntityCount求和，但你用了drg.Count()（统计行数），而且JobEntityCount是String类型，需要先转成数值再求和，正确写法应为：

   .JobEntityCount = drg.Sum(Function(r) Convert.ToInt32(r.Field(Of String)("JobEntityCount")))
   

2. Join类型不符合需求：你用的普通Join对应SQL的INNER JOIN，会过滤掉Table2中没有匹配Table3Summed的记录，但目标SQL是LEFT OUTER JOIN，需要保留所有匹配Table1的Table2记录，不管有没有对应的Table3数据。

3. JobEntityCount取值错误：你直接引用table3Summed.JobEntityCount，这是整个集合的属性，不是当前分组对应的求和值，应该从关联的Table3数据中汇总。

4. 语法错误：JobValue字段后面缺少逗号，会导致编译失败，需要在该行末尾添加逗号。

LINQ中实现LEFT OUTER JOIN的方法 #

在VB.NET的LINQ查询语法中，使用Group Join ... Into ... DefaultIfEmpty()来实现左外连接，具体步骤是：

• 用Group Join关联右表，然后用Into创建分组集合
• 对分组集合调用DefaultIfEmpty()，确保左表的每条记录都被保留，即使右表没有匹配项

修正后的完整LINQ代码 #

' 先正确汇总Table3：转类型后求和，而非统计行数
Dim table3Summed = From dr In dtTable3.AsEnumerable()
                   Group dr By Table2Id = dr.Field(Of Integer)("Table2Id") Into drg = Group
                   Select New With {
                       .Table2Id = Table2Id,
                       .JobEntityCount = drg.Sum(Function(r) Convert.ToInt32(r.Field(Of String)("JobEntityCount")))
                   }

' 主查询：用Group Join实现左外连接Table3Summed
Dim result = From table1 In dtTable1.AsEnumerable()
             Join table2 In dtTable2.AsEnumerable()
             On table1.Field(Of Integer)("Table1Id") Equals table2.Field(Of Integer)("Table1Id")
             ' 左外连接Table3Summed
             Group Join t3Sum In table3Summed
             On table2.Field(Of Integer)("Table2Id") Equals t3Sum.Table2Id Into t3Group = Group
             From t3 In t3Group.DefaultIfEmpty()
             ' 按JobType分组
             Group table2, t3 By jobType = table2.Field(Of Integer)("JobType") Into g = Group
             Select New With {
                 .Table1IdCount = g.Select(Function(x) x.table2.Field(Of Integer)("Table1Id")).Distinct().Count(),
                 .Table2IdCount = g.Select(Function(x) x.table2.Field(Of Integer)("Table2Id")).Distinct().Count(),
                 .JobType = jobType,
                 .JobValue = g.Sum(Function(x) x.table2.Field(Of Decimal)("JobValue")),
                 .JobEntityCount = g.Sum(Function(x) If(x.t3 IsNot Nothing, x.t3.JobEntityCount, 0))
             }

内容的提问来源于stack exchange，提问作者josmond