CODEWITHSUNDEEP
c#sql-serverlinqentity-framework
Jeff
Jeff

Reputation: 2313

Find row with max value for each key

I have a table stored in SQL Server which I hope to query using LINQ. A vague description of the table:

  ID1  |  ID2  |  SomeData
-----------------------------
   1   |   1   |  'Moo'
   1   |   2   |  'Meow'
   1   |   3   |  'Woof'
   1   |   4   |  'Cheap'
   2   |   1   |  'Quack'
   2   |   2   |  'Grrrrr'
   2   |   3   |  'Wan wan'

I am only interested in rows where ID2 is the greatest value per ID1 Example desired results:

   1   |   4   |  'Cheap'
   2   |   3   |  'Wan wan'

I have come up with this SQL code:

SELECT *
FROM SomeTable a
INNER JOIN
(
    SELECT ID1, MAX(ID2) as SomeName
    FROM SomeTable
    GROUP BY ID1
) as b
ON a.ID1 = b.ID1 and a.ID2 = b.SomeName

When implementing in LINQ, is it also require to have a sub query? That is, query the database and load a variable with the 'inner query' results, then query the same table but compare against values from the 'inner query'? I know if I tinker with this for a while, I'll get it eventually, but I feel like there is probably some fancy way to do this in LINQ really easily.

Desired answer will utilize extension method syntax.

Bonus: Is there any type of standard LINQ syntax to extension method syntax converter? What tools do you guys use to play with LINQ statements? It seems a little cumbersome to me to require an entire c# (ASP.NET MVC for me) to play with a LINQ query.

Upvotes: 5

Views: 3077

Answers (1)

driis
driis

Reputation: 164291

The straight forward way:

var result = db.SomeTable.GroupBy(x => x.ID).Select(g => g.OrderByDescending(x => x.ID2).First());

Not entirely sure how efficient SQL this translates to, so if that is important, check it with your favorite profiler !

Upvotes: 12

Related Questions