C#•13mo 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•13mo 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•13mo 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•13mo 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•13mo 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•13mo 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•13mo ago

sorry for being unprecise and thanks a lot for explaining.

Accord•13mo 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