LINQ Entity Framework 4 SELECT COUNT LEFT OUTER JOIN IN GROUP BY

Question

Using LINQ to Entities I'm having trouble getting a count of child objects given a list of parent ids. Here's how I'd do it in straight SQL:

SELECT COUNT(Child.ParentId) ,Parent.Parentd
FROM Parent
LEFT OUTER JOIN Child ON Child.ParentId = Parent.ParentId
WHERE
    Parent.ParentId IN (
        8417
        ,118458
        ,120567
        ,121596
        )
GROUP BY Parent.ParentId

Here's what I've tried in LINQ to Entities:

var counts = from Parent in context.Parents
    join Child in context.Children on Parent.ParentId equals Child.ParentId 
    into children
    from jn in children.DefaultIfEmpty()
    where iEnumerableParentIds.Contains(parent.parentId)
    group parent by parent.parentId
    into g
    select new {count = g.Count(), parentId = g.Key};

This is pretty close but I'm counting the grouped parent instead of the child which gives me a count of 1 when there are no children on the parent.

Do you have a "navigation property" for your `Parent` entity, like a `Children` property? — Jacob, Jun 28 '13 at 17:34
no. this project was a "data-first" setup and there was not a formal sql foreign key relationship setup. we never setup the foreign key on the db because we knew there were orphans that we could not eliminate from the child table — David Droddy, Jun 28 '13 at 17:37
looks like the answer might be here: http://stackoverflow.com/questions/695506/linq-left-join-group-by-and-count?rq=1 Trying it out now... — David Droddy, Jun 28 '13 at 17:51

score 0 · Answer 1 · answered Jun 28 '13 at 17:36

0

I think you have to change the grouping source here:

             group new { parent, jn } by parent.parentId
             into g
             select new {count = g.Select(x => x.jn).Count(), parentId = g.Key};

answered Jun 28 '13 at 17:36

MarcinJuraszek

124,003
15
196
263

score 0 · Answer 2 · answered Jun 29 '13 at 06:32

var counts = from Parent in context.Parents join Child in context.Children on Parent.ParentId equals Child.ParentId into children where iEnumerableParentIds.Contains(parent.parentId) select new {count = children.Count(), parentId = Parent.ParentId};

With out testing (responding from my iPod ;)), I am pretty sure this will give you the results you want. I can't remember exactly what I do off the top of my head but I think that should work. Basically, you've already grouped the join so you shouldn't have to group everything a second time.

LINQ Entity Framework 4 SELECT COUNT LEFT OUTER JOIN IN GROUP BY

2 Answers2