LINQ to Entiites, left outer join of a group by

Question

I can build a SQL query easily that does the following.

For each row of 'Table1' I want a count of the number of related 'Table2' records that have a 'Status' not equal to 5. There might be no matches so I use a 'left outer join' and then within that use a 'group by' in order to find the total number of matches.

The following query works as SQL. It outputs either a null for the count because there are no matches at all or an actual integer count if there is at least one match.

select
    Table1.Id,
    Table2Outer.Count
from
    Table1
    left outer join
    (
        select
            Table2.Id,
            COUNT(*) as Count
        from
            Table2
        where
            Table2.Status != 5
        group by
            Table2.Id
    ) as Table2Outer on Table2Outer.Id = Table.Id

Unfortunately I cannot work out how to convert this to LINQ to Entities. The following does not even compile and I am stuck!

var x = (from t1 in ctx.Table1
         join t2 in ctx.Table2 on { t1.Id, t2.Status } equals new { t2.Id, Status != 5 } into t2Outer
         from t2OuterB in t2Outer.DefaultIfEmpty()
         group t2Outer by ?);

Any ideas?

Answer 1

Use a subquery to count child records in the projection.

var x = from t1 in ctx.Table1
        select new
        {
            t1.Id,
            Count = (from t2 in ctx.Table2
                     where t2.Status != 5
                     where t2.Id == t1.Id
                     select t2).Count()
        };