count + group by + where

Question

I'm using Entity Framework 4 and having trouble with a query.

I have two objects:

Instruction Component

Between these objects there's a many-to-many relation. An Instruction van point to one or more Components. And a Component can be referenced by multiple Instructions.

I am trying to get how many 'finished' (status = 6) Instructions there are for each Component.

In SQL this is easy:

select c.id, COUNT(*)
from Component c
inner join InstructionComponents ic on c.Id = ic.Component_Id
inner join Instructions i on i.Id = ic.Instruction_Id
where i.StatusValue = 6
group by c.id

I'm having trouble doing this in EF. This is what I've tried:

var result =
   from component in Copmponents
   where component.Instructions.Any(s => s.Status == 6)
   group component by component.Id
   into componentGroup
   select new { compId = onderdeelGroup.Key, count = componentGroup.Count() };

But his query doesn't return the correct count. I don't know how the count the number of instructions.

Answer 1

How about this?

var query = Components.Select(c => new 
            { 
                c.Id, 
                Count = c.Instructions.Count(i => i.Status == 6)
            });