Linq, double left connection and double counter

Question

I want to translate this SQL statement to a well-functioning and executable LINQ command. I managed to start the first account using the count of grouping and group members, but I don’t know how to get the second account.

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 is what I have 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 appreciated!

EDIT: 03/24/2010 09.41

This request:

 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 ready!

Fabian

Answer 1

Wouldn’t it be like this job?

 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 automatically generate child relations so that you can access main.Details for each detail related to the main and main.Messages for each message related to the main.