LINQ version of Where In Subquery With Max and Group By

Question

How would you use LINQ to Entities to accomplish the following query.

SELECT [id] ,[value1], [value2], [dateValue]
FROM table
WHERE dateValue IN (
    SELECT MAX(dateValue)
    FROM table
    GROUP BY id
)

There are multiple entries in the table with the same id, so I just want one of each id with the greatest date value. Also, the dateValue includes a time component down to the milliseconds and wont be duplicated.

I know LINQ To Entities doesn't support the IN keyword however I've read you can still work with subqueries by using the context.tableName.Any() method or by adding a query inside the Select part.

Using context = ContextProvider.GetContext()
    Dim table = context.tableName

    Dim query = _
        From rows In table _
        Select New ClassName With _
               { _
                   .Id = rows.Id, _
                   .Value1 = rows.Value1, _
                   .Value2 = rows.Value2, _
                   .DateValue = rows.DateValue _
               }

    Return query.ToList()
End Using

Given the above, I'm not sure if there should be a Where table.Any(... in the query, or a .DateValue = subquery in the Select, to get what I want. And in either situation I'm not sure how to format the expression.

Answer 1

Actually your sql is already incorrect because there is no relation between the subquery and the main-query other than the date. If you have an ID with a dateValue that is the max-dateValue of another ID you get this record with the wrong ID.

So if you want the record with the greatest Date per ID i would do:

WITH CTE AS
(
    SELECT rn = ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [dateValue] DESC),
          [id] ,[value1], [value2], [dateValue]
    FROM table
)
SELECT [id] ,[value1], [value2], [dateValue]
FROM CTE
WHERE RN = 1

In LINQ this should work (i'm not that familiar with Linq-To-Entities):

Dim query = From row In table
            Group row By row.Id into idGrp
            Let MaxDatePerID = idGrp.Max(Function(r) r.DateValue)
            From idRow In idGrp
            Where idRow.DateValue = MaxDatePerID 
            Select New ClassName With 
            { 
               .Id = idRow.Id, 
               .Value1 = idRow.Value1, 
               .Value2 = idRow.Value2, 
               .DateValue = idRow.DateValue 
            }