C#•2y ago

❔ Getting the last data registration time for multiple locations

I have the following Sql table, which I access using ef core.

Id | Location | DataValue | RegistrationDate

Id | Location | DataValue | RegistrationDate

I need to find the last registration date for each different location. Currenetly I do the following:

var locations = _dbContext.Table.Select(x=>x.Location).Distinct().ToList();
foreach(var location in locations)
{
  var date = _dbContext.Table.Where(x=>x.location = location).OrderBy(x=>x.RegistrationDate).Top(1);
}

var locations = _dbContext.Table.Select(x=>x.Location).Distinct().ToList();
foreach(var location in locations)
{
  var date = _dbContext.Table.Where(x=>x.location = location).OrderBy(x=>x.RegistrationDate).Top(1);
}

This is obviously not great. What would be the propper way to do this?

7 Replies

Angius•2y ago

var dates = await _context.Locations
    .GroupBy(l => l.Location)
    .Select(l => l.OrderByDescending(i => i.RegistrationDate).First())
    .ToListAsync();

var dates = await _context.Locations
    .GroupBy(l => l.Location)
    .Select(l => l.OrderByDescending(i => i.RegistrationDate).First())
    .ToListAsync();

Something like that, maybe?

Ole (ping please)OP•2y ago

Yeah, that sounds like a way more resonable approach. Thanks! what is the difference between using orderByDec().First over Max() in this case?

Angius•2y ago

Max() uses the default comparator of the object Unless you have one such comparator implemented, it won't work OrderByDescending() meanwhile lets you order by a given property of the object

Ole (ping please)OP•2y ago

But all SQL types have a comparor right? so OrderBy(x=>x.prop) and Max(x=>x.prop) would both point to a base sql type

Angius•2y ago

Ah, I thought you meant using Max() without anything passed to it Yeah, .OrderByDescending(x => x.Prop).First() and .Max(x => x.Prop) should work the same, then Or, rather, .MaxBy(x => x.Prop) Since I don't think .Max() takes a predicate

Ole (ping please)OP•2y ago

sorry for being unprecise and thanks a lot for explaining.

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ Getting the last data registration time for multiple locations