EF Linq include last-CodePudding

I have 2 tables one with cities and another with temperature/date values for the cities, a city can have multiple dates with temperature.

how could I by EF linq bring the cities the most recent temperature of each one

something like: _context.city.include(x=> x.temperature().last());

CodePudding user response：

You could achieve this by projecting the city and the last temperature to a new object, in a Select expression. The code snippet you included in your question is missing a few steps, so I'm going to take a guess at your data structures:

await _context.Cities.Select(city => new
{
    City = city,
    LastTemperature = city.Temperatures
                         .OrderByDescending(t => t.DateRecorded)
                         .FirstOrDefault(),
}).ToListAsync();

CodePudding user response：

You always should take control over which temperature is "last" by ordering them by a chosen property. Doing that, it makes sense to use Take(1) in Include, for example to get the most recent one:

_context.Cities
    .Include(x=> x.Temperatures.OrderByDescending(t => t.Date).Take(1));

Filtered/ordered Include is supported as of EF core 5.

Since you're already trying something along these lines I'm assuming you're using EFC 5 or 6.

CodePudding user response：

I'm not a fan of filtering includes as it leads to ambiguity (a filtered include's nav property is considered loaded so the user of the result must have knowledge of possibly encapsulated behavior). I'd prefer projecting to a new/anonymous type:

_context.TemperatureReading
    .GroupBy( tr => tr.City )
    .Select( g => new
    {
        City = g.Key,
        LatestTemp = g.OrderByDescending( t => t.Date ).First(),
    } );