Select all rows with distinct column value using LINQ

Question

I have a database with 4 fields that looks something like this:

ID      DeviceId       Location        Date
1           A             ...            2
2           A             ...            1
3           B             ...            2

For each DeviceId I want the location from the record with the highest date. I can get the distinct DeviceId's like this:

// get all locations
var locations = Session.Query<Location>().ToList();

//Get the latest used unique deviceId's
var distinctDeviceIdsByDate = (
      from loc in locations
      orderby loc.DeviceId, loc.Date descending
      select loc.DeviceId).Distinct().ToList();

I would then use join to get the wanted rows, but this won't do any good since I can't get anything other than the DeviceId's and therefore can't identify which rows to select. and if I try to select the following:

select loc

I can only get the rows with unique combinations of all the columns. I'm sure there's a simple solution, but I'm afraid I can't figure it out right now.

Answer 1

I guess you have to use some combination of GroupBy and Take. Try this,

var distinctDeviceIdsByDate = 
    locations.OrderByDescending(location => location.DeviceId)
             .ThenByDescending(location => location.Date)
             .GroupBy(location => location.DeviceId)
             .SelectMany(location => location.Take(1));

Answer 2

Assuming that Date is unique per DeviceId you could try

//Get the latest used unique deviceId's
var distinctDeviceIdsByDate = (
      from loc in Session.Query<Location>()
      group loc by loc.DeviceId
      into g
      select new
      {
          DeviceID = g.Key,  
          Location = g.OrderByDescending(l => l.Date).First().Location;
      };