Take the 2-minute tour ×

We have two tables - Tasks and TasksUsers (users assigned to task). Task has EntityCollection called TaskUsers.

This query returns number of tasks per 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:

Administrator 11
LukLed 5

I want it to return:

Administrator 11
LukLed 5
null 10

Some of tasks don't have any assignment, but I still want them in my result set. Normally, in SQL, it is achieved by changing join to left join. In Linq, outside EF, I could use DefaultIfEmpty(). How can it be done in linq to entities?

share|improve this question
@LukLed - I deleted my answer because it wasn't helpful. Sorry... –  David Stratton Mar 4 '10 at 4:45
Are you sure you can't use DefaultIfEmpty()? See msdn.microsoft.com/en-us/vcsharp/ee908647.aspx#leftouterjoin –  Foole Mar 4 '10 at 7:32
@Foole: I wrote, I could use it, if it wasn't EntityFramework, but DefaultIfEmpty doesn't work with EF. –  LukLed Mar 4 '10 at 7:36
DefaultIfEmpty works in EF4, but not in EF1. –  Craig Stuntz Mar 4 '10 at 13:22
add comment

2 Answers

up vote 2 down vote accepted

My first try would be:

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 along those lines. Or just use two queries. I don't know if this is the best way, though. As I noted in comments, this is far easier in EF 4.

share|improve this answer
add comment

I would suggest looking here which goes through left outer joins with EF:


share|improve this answer
Thanks, but how does it apply to my question? –  LukLed Mar 5 '10 at 6:20
Right down the bottom he explains the situations where a join cannot be done in this manner as one of the above says its easier to do this in EF 4, we had to resort to SPs to do some of our joins before that. –  krystan honour Mar 5 '10 at 9:12
Where does he explain that this is not possible? –  LukLed Mar 5 '10 at 17:06
point 4, 5 and 6 discuss limitations due to contains not being supported before EF 4 –  krystan honour Mar 8 '10 at 9:03
add comment

Your Answer


