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.

So for each device ID you want the location from the record with the highest date? — D Stanley
– D Stanley, Commented Mar 12, 2013 at 14:49
You shouldn't edit the question to include the answer; you should mark the one you think is correct or help you the most. — rae1
– rae1, Commented Mar 12, 2013 at 15:33

rae1 · Accepted Answer · 2013-03-12 15:35:45Z

7

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

edited Mar 12, 2013 at 15:35

rae1

6,1425 gold badges29 silver badges49 bronze badges

answered Mar 12, 2013 at 14:59

DotNetWala

6,6101 gold badge22 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dansv130 Over a year ago

Looks like this works the way I want! I will update the question with your solution, accredited to you!

D Stanley · Accepted Answer · 2013-03-12 15:07:03Z

1

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

edited Mar 12, 2013 at 15:07

answered Mar 12, 2013 at 15:01

D Stanley

153k12 gold badges190 silver badges257 bronze badges

2 Comments

dansv130 Over a year ago

This looks like a possible solution aswell, but since the answer from DotNetWala worked I will use it for now and investigate other solutions should I encounter problems down the road.

CB-Dan Over a year ago

This will not work with NHibernate and will throw the following SqlException: Column 'Location.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Mikael Östberg · Accepted Answer · 2013-03-12 15:08:36Z

0

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           ....

edited Mar 12, 2013 at 15:08

answered Mar 12, 2013 at 15:01

Mikael Östberg

17.2k6 gold badges67 silver badges80 bronze badges

2 Comments

dansv130 Over a year ago

This looks like a possible solution aswell, but since the answer from DotNetWala worked I will use it for now and investigate other solutions should I encounter problems down the road.

Mikael Östberg Over a year ago

They are essentially the same. The key concept is grouping.

Collectives™ on Stack Overflow

Select all rows with distinct column value using LINQ

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related