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]

所以，我就快完成了！

Fabian

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。