我有一个看起来(简化)的数据库,如下所示:
var reports = new[] {
new { ReportId = 1, Title = "Report A" },
new { ReportId = 2, Title = "Report B" },
};
var testCases = new[] {
new { TestId = 1, Title = "Test A" },
new { TestId = 2, Title = "Test B" },
new { TestId = 3, Title = "Test C" },
new { TestId = 4, Title = "Test D" },
new { TestId = 5, Title = "Test E" },
};
var testRuns = new[] {
new { TestId = 1, ReportId = 1 },
new { TestId = 2, ReportId = 1 },
new { TestId = 1, ReportId = 2 },
new { TestId = 2, ReportId = 2 },
new { TestId = 3, ReportId = 2 },
new { TestId = 4, ReportId = 2 },
};
结果,我想获取与相应的
testCases
分组的reports
列表,即:Test A => [Report A, Report B]
Test B => [Report A, Report B]
Test C => [Report B]
Test D => [Report B]
Test E => []
我不确定如何用SQL或LINQ来表述。我想我需要一个组联接或一个左外部联接,或者它被调用了,但是我还不能弄清楚正确的语法。我尝试过类似的方法,但仍然错过了一部分:
var result = tests.GroupJoin(reports, t => t.TestId, ???, (t, rs) => new { Test = t, Reports = rs });
也许有完全不同的方式来表达该查询。
编辑:并非
testCases
中的每个条目都在testRuns
中引用。 最佳答案
如果没有GroupJoin就可以生活,则可以通过2个简单的连接和GroupBy
来做到这一点:
var temp = from c in testCases
join ru in testRuns
on c.TestId equals ru.TestId into left
from l in left
join re in reports
on l.ReportId equals re.ReportId into foo
from f in foo
select new {
Test = c.Title,
Report = f
};
//Dump only in LinqPad!
temp.GroupBy(x => x.Test).Dump();
/ EDIT:如果您还想要空结果,则需要使用
DefaultIfEmpty()
:public class Report
{
public int ReportId { get; set; }
public string Title { get; set; }
}
public class TestCase
{
public int TestId { get; set; }
public string Title { get; set; }
}
public class TestRun
{
public int TestId { get; set; }
public int ReportId { get; set; }
}
var temp = from c in testCases
join ru in testRuns
on c.TestId equals ru.TestId into left
from l in left.DefaultIfEmpty(new TestRun())
join re in reports
on l.ReportId equals re.ReportId into foo
from f in foo.DefaultIfEmpty()
select new {
Test = c.Title,
Report = f
};
temp.Dump();
演示代码:http://share.linqpad.net/t3osbj.linq
具有DefaultIfempty的强类型演示:http://share.linqpad.net/fsov4p.linq
带有DefaultIfEmpty的宽松类型示例:http://share.linqpad.net/7k2lq4.linq
关于c# - 如何制定GroupJoin()语句,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/30298192/