sql - Linq，双左连接和双计数

Question

我希望将此 SQL 语句转换为运行良好且性能良好的 LINQ 命令。我设法使用分组计数和关键成员进行了第一次计数，但不知道如何获得第二次计数。

select main.title, count(details.id) as details, count(messages.id) as messages
from main
 left outer join details on main.id = details.mainid
 left outer join messages on details.id = messages.detailid
group by main.title

这是我到目前为止所做的:

from main in Main
join detail in Details on main.Id equals detail.MainId into j1
from subdetail in j1.DefaultIfEmpty()
group main by main.Title into g
select new { Title = g.Key, Details = g.Count() }

欢迎任何建议!

编辑:24/03/2010 09.41

这个查询:

from main in Main
join detail in Details on main.Id equals detail.MainId into j1
from subdetail in j1.DefaultIfEmpty()
join message in Messages on subdetail.Id equals message.DetailId into j2
group main by main.Title into g
select new { Title = g.Key, Details = g.Count() }

生成此 SQL 语句:

SELECT COUNT(*) AS [Detail], [t0].[Title]
FROM [Main] AS [t0]
LEFT OUTER JOIN [Detail] AS [t1] ON [t0].[Id] = [t1].[MainId]
LEFT OUTER JOIN [Messages] AS [t2] ON [t1].[Id] = [t2].[DetailId]
GROUP BY [t0].[Title]

所以，我快完成了!

法比安

Answer 1

不会像这样的工作吗？

from main in Main
select new {
    Title = main.Title,
    Details = main.Details.Count(),
    Messages = main.Details.Sum( d => d.Messages.Count())
}

如果您有外键约束，LINQ 应该自动生成子关系，因此您可以访问与 main 关联的每个 Detail 的 main.Details，以及与 main 关联的每个 Message 的 main.Messages。