Linq for objects - How to define a left join for grouping?

Question

We have two tables: "Tasks and Tasks" (users are assigned to the task). A task has an EntityCollection called TaskUsers.

This query returns the number of tasks for the username:

model.TaskCountByAssignee = ( from t in TaskRepository.List() from tu in t.TaskUsers group tu by tu into tug select new {Count = tug.Count(), UserName = tug.Key.Username}).ToList() 

This query returns:

Admin 11
LukLed 5

I want him to return:

Admin 11
LukLed 5
null 10

Some tasks have no assignment, but I still want them in my result set. Typically in SQL, this is achieved by changing join to left join . In Linq, outside of EF, I could use DefaultIfEmpty (). How can this be done in linq for objects?

Answer 1

My first attempt:

 model.TaskCountByAssignee = ( (from t in TaskRepository.List() from tu in t.TaskUsers group tu by tu.UserName into tug select new {Count = tug.Count(), UserName = tug.Key}) .Union(from t in TaskRepository.List() where !t.TaskUsers.Any() group t by 1 into tug select new {Count = tug.Count(), UserName = null}).ToList(); 

Or something like that. Or just use two queries. However, I do not know if this is the best way. As I noted in the comments, this is much easier in EF 4.

Answer 2

I would suggest looking here, which goes through left external connections to EF:

http://geekswithblogs.net/SudheersBlog/archive/2009/06/11/132758.aspx