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() }

欢迎任何意见!

此查询:

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() }

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]

所以,我差不多完了!

费边