使用左联接转换LINQ内部联接

Question

我正试图在查询中实现左联接，此时我得到一个"对象引用未设置为对象实例"。

该查询作为内部联接非常有效，但我希望包括左表中的所有行，不管是否找到匹配项。我试着遵循以前关于这方面的一些帖子，大多数都提到了DefaultIfEmpty((，但我没能弄清楚。

INNER JOIN - SQL
SELECT TOP (1000) 
FROM table1 as edc
inner join table2 as c on edc.Id = c.Id
inner join table3 as p on p.Id = c.Id
group by p.Description

内部联接-SQL-

SELECT TOP (1000) 
FROM table1 as edc
inner join table2 as c on edc.Id = c.Id
left join table3 as p on p.Id = c.Id
group by p.Description

内部连接-LINQ

from edc in table1
join q1 in table2 on __edc.Id equals q1__.Id
join q2 in _table3 on q2.Id equals q1.Id 
group q1 by q2.Description
into grouped
select new MyObj
{
Label = grouped.Key,
Value = grouped.Count(),
}

左联接-LINQ

from edc in table1
join q1 in table2 on __edc.Id equals q1__.Id
join q2 in _table3 on q2.Id equals q1.Id  into leftJoin
from p in leftJoin.DefaultIfEmpty()
group q1 by p.Description
into grouped
select new MyObj
{
Label = grouped.Key,
Value = grouped.Count(),
}

Answer 1

考虑以下示例。我们有三个表，表1和表2之间有一个左联接，第二个左联接到表3。您需要在两个联接上指定DefaultIfEmpty()，以包括右表中没有匹配项的行。

public class Item
{
public int Id { get; set; }
public string Description { get; set; }
}
class Program
{
static void Main(string[] args)
{
var table1 = new List<Item>
{
new Item {Id = 1, Description = "a"},
new Item {Id = 2, Description = "b"},
new Item {Id = 3, Description = "c"},
new Item {Id = 4, Description = "d"}
};
var table2 = new List<Item>
{
new Item {Id = 1, Description = "e"},
new Item {Id = 2, Description = "f"},
new Item {Id = 4, Description = "g"}
};
var table3 = new List<Item>
{
new Item {Id = 1, Description = "h"},
new Item {Id = 4, Description = "h"},
new Item {Id = 5, Description = "i"},
new Item {Id = 6, Description = "j"}
};
var leftJoin = from t1 in table1
join t2 in table2 on t1.Id equals t2.Id into firstJoin
from x in firstJoin.DefaultIfEmpty()
join t3 in table3 on x?.Id equals t3.Id into secondJoin
from y in secondJoin.DefaultIfEmpty()
select new
{
Table1Id = t1?.Id,
Table1Description = t1?.Description,
Table2Id = x?.Id,
Table2Description = x?.Description,
Table3Id =  y?.Id,
Table3Description = y?.Description
};
Console.WriteLine("Left Join:");
foreach (var i in leftJoin)
{
Console.WriteLine($"T1Id: {i.Table1Id}, T1Desc: {i.Table1Description}, " +
$"T2Id: {i.Table2Id}, T2Desc: {i.Table2Description}, " +
$"T3Id: {i.Table3Id}, T3Desc: {i.Table3Description}");
}
Console.WriteLine(string.Empty);
var grouped = from x in leftJoin
group x by x.Table3Description
into group1
select new
{
Label = group1.Key,
Count = group1.Count()
};
Console.WriteLine("Left Join Grouped:");
foreach (var i in grouped)
{
Console.WriteLine($"Label: {i.Label}, Count: {i.Count}");
}
Console.ReadLine();
}
}

运行程序会产生以下输出：

Left Join:
T1Id: 1, T1Desc: a, T2Id: 1, T2Desc: e, T3Id: 1, T3Desc: h
T1Id: 2, T1Desc: b, T2Id: 2, T2Desc: f, T3Id: , T3Desc:
T1Id: 3, T1Desc: c, T2Id: , T2Desc: , T3Id: , T3Desc:
T1Id: 4, T1Desc: d, T2Id: 4, T2Desc: g, T3Id: 4, T3Desc: h
Left Join Grouped:
Label: h, Count: 2
Label: , Count: 2

希望这能有所帮助！

Answer 2

这很容易，只需将"from p"更改为"from q2"：

from edc in table1
join q1 in table2 on __edc.Id equals q1__.Id
join q2 in _table3 on q2.Id equals q1.Id  into leftJoin
from q2 in leftJoin.DefaultIfEmpty()
group q1 by p.Description
into grouped
select new MyObj
{
Label = grouped.Key,
Value = grouped.Count(),
}