C# 我如何最优雅地将聚合 SQL 的左连接表达为 LINQ 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/832/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I most elegantly express left join with aggregate SQL as LINQ query
提问by vzczc
SQL:
查询语句：
SELECT
u.id,
u.name,
isnull(MAX(h.dateCol), '1900-01-01') dateColWithDefault
FROM universe u
LEFT JOIN history h
ON u.id=h.id
AND h.dateCol<GETDATE()-1
GROUP BY u.Id, u.name
采纳答案by vzczc
A solution, albeit one that defers handling of the null value to the code, could be:
一种解决方案，尽管是一种将空值的处理推迟到代码的解决方案，可能是：
DateTime yesterday = DateTime.Now.Date.AddDays(-1);
昨天的日期时间 = DateTime.Now.Date.AddDays(-1);
var collection=
from u in db.Universe
select new
{
u.id,
u.name,
MaxDate =(DateTime?)
(
from h in db.History
where u.Id == h.Id
&& h.dateCol < yesterday
select h.dateCol
).Max()
};
This does not produce exactly the same SQL, but does provide the same logical result. Translating "complex" SQL queries to LINQ is not always straightforward.
这不会产生完全相同的 SQL，但会提供相同的逻辑结果。将“复杂”的 SQL 查询转换为 LINQ 并不总是那么简单。
回答by AdamB
This isn't a full answer for you, but on the left join piece you can use the DefaultIfEmpty operator like so:
这对您来说不是一个完整的答案，但在左侧连接部分，您可以像这样使用 DefaultIfEmpty 运算符：
var collection =
from u in db.Universe
join history in db.History on u.id = history.id into temp
from h in temp.DefaultIfEmpty()
where h.dateCol < DateTime.Now.Date.AddDays(-1)
select u.id, u.name, h.dateCol ?? '1900-01-01'
I haven't had the need to do any groupbycommands yet, so I left that out as to not send you down the wrong path. Two other quick things to note. I have been unable to actually join on two parameters although as above there are ways to get around it. Also, the ?? operator works really well in place of the isnullin SQL.
我还不需要执行任何groupby命令，所以我把它省略了，以免让你走错路。另外两个需要注意的快速事项。我一直无法实际加入两个参数，尽管如上所述有办法绕过它。还有，?? 运算符代替isnullin SQL 的效果非常好。
回答by Orion Adrian
You're going to want to use the join intoconstruct to create a group query.
您将要使用该join into构造来创建组查询。
TestContext db = new TestContext(CreateSparqlTripleStore());
var q = from a in db.Album
join t in db.Track on a.Name equals t.AlbumName into tracks
select new Album{Name = a.Name, Tracks = tracks};
foreach(var album in q){
Console.WriteLine(album.Name);
foreach (Track track in album.Tracks)
{
Console.WriteLine(track.Title);
}
}
回答by Orion Adrian
var collection=
from u in db.Universe
select new
{
u.id,
u.name,
MaxDate =(DateTime?)
(
from h in db.History
where u.Id == h.Id
&& h.dateCol < yesterday
select h.dateCol
).Max()
};
Just youse the above code and this should work fine!
只需使用上面的代码，这应该可以正常工作！
