LINQ To SQL - get latest records that match a where clause

Question

Given something like the following (just thrown together as an example)

Employee ID | Time In       | Position | Shift Length  
1           | April 1 08:00 | Manager  | 8  
0           | April 1 08:00 | Maint    | 8  
1           | April 2 08:00 | Manager  | 8  
2           | April 1 08:00 | Clerk    | 4  
1           | April 3 08:00 | Manager  | 8  
0           | April 2 12:00 | Maint    | 1  
0           | April 3 12:00 | Maint    | 4  

If given a specific date, I need to be able to retrieve the latest record for each Employee ID that happened prior to that date (just based on Time In)

So if the given date was, for instance

April 3 5:00

I would expect to see the following rows come back

Employee ID | Time In       | Position | Shift Length  
1           | April 2 08:00 | Manager  | 8  
2           | April 1 08:00 | Clerk    | 4  
0           | April 2 14:00 | Maint    | 1  

I need to return the entire record for each match.

I also need to make sure the records are evaluated against the given date before they are filtered by Employee ID. I don't want to get the latest records for each employee and then test those against the date.

Thanks for the help.

Answer 1

It sounds like you want something like:

var query = from row in context.Whatever
            where row.TimeIn < specifiedDate
            group row by row.EmployeeID into g
            select g.OrderByDescending(r => r.TimeIn).First();

So the steps are:

• Filter out anything we don't want
• Group by employee
• In each group, order by the date in descending order (i.e. latest first) and take the first entry

(An alternative would be to use g.OrderBy(r => r.TimeIn).Last() - I don't know what difference that would make to the generated SQL.)