an SQL query in LINQ

Question

I have a table with these columns: CombinationID, IndexID, Value There is a sql query:

SELECT     CombinationID
FROM         CombinationIndex
where IndexID <> 4
group by CombinationID 
order by sum(case indexid when 1 then -Value else Value end) desc

How could I write this query in c# by linq?

Answer 1

I think this should just about do it for you (it's all off the top of my head so there could be slight syntactical errors):

var results = context
              .CombinationIndexes
              .Where(i => i.IndexID != 4)
              .GroupBy(i => i.CombinationID)
              .OrderBy(g => g.Sum(i => i.IndexID == 1 ? -i.Value : i.Value))
              .Select(g => g.Key);