Linq、双左连接和双计数

Question

我希望将这个 SQL 语句转换为一个运行良好的 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 关联的每个详细信息的 main.Details，以及与 main 关联的每个消息的 main.Messages。