Linq, double left join and double count

Question

Hi!

I'm looking to translate this SQL statement to a well working & performant LINQ command. I've managed to have the first count working using the grouping count and key members, but don't know how to get the second count.

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

Here's what I've done so far:

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

Any advice is welcome!

EDIT: 24/03/2010 09.41

This query:

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

generates this SQL statement:

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]

So, I'm almost done!

Fabian

Answer 1

Wouldn't something like this work?

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

If you have foreign key constraints, LINQ should generate the child-relationships automatically, so you could access main.Details for each Detail associated with main, and main.Messages for each Message associated with main.