CODEWITHSUNDEEP
linqnhibernate
dansv130
dansv130

Reputation: 317

Select all rows with distinct column value using LINQ

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.

Upvotes: 6

Views: 7748

Answers (3)

DotNetWala
DotNetWala

Reputation: 6610

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));

Upvotes: 7

Mikael &#214;stberg
Mikael &#214;stberg

Reputation: 17156

You can use a grouping to solve this.

var locations = new [] {
    new { DeviceId = "A", Date = 2, Location = ".." },
    new { DeviceId = "A", Date = 1, Location = ".." },
    new { DeviceId = "B", Date = 2, Location = "...." },
};

var lastUsedLocations = 
    from l in locations
    group l by l.DeviceId into g
    let lastUsed = g.OrderBy(x => x.Date).Last()
    select lastUsed;

// lastUsedLocations contains
// DeviceId       Date        Location
// A              2           ..
// B              2           ....

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

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;
      };

Upvotes: 1

Related Questions