Translate SQL to LINQ query - group/join/filter

Posted by Paddy on Stack Overflow See other posts from Stack Overflow or by Paddy
Published on 2010-04-22T15:41:40Z Indexed on 2010/04/22 15:53 UTC
Read the original article Hit count: 193

Filed under:
|
|

I have the following query:

SELECT S.[FlowOrder], S.[DESCRIPTION], COUNT(I.ID)
FROM WorkFlowStatus AS S
INNER JOIN Item AS I
    ON S.ID = I.StatusID
WHERE I.Installation = '1'
GROUP BY S.[Description], S.[FlowOrder]
ORDER BY S.[FlowOrder]

Which gives me the count of an item, grouped by a foreign key to workflow, outputting the descriptive name from my FK table.

I've go this far with the LINQ query (using LINQ-to-SQL) in the background:

var items = from s in _entities.WorkflowStatus
    join i in _entities.Items on s.ID equals i.StatusId
    into Statuses
    orderby s.FlowOrder
    select new {s.Description, ItemCount = Statuses.Count() };

How do I get the where clause in the SQL into this LINQ query?

© Stack Overflow or respective owner

Related posts about linq-to-sql

Related posts about tsql