EF Linq include last

Question

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

Please always use a tag indicating the exact EF version because that's very important for how a question can be answered. — Gert Arnold, Feb 03 '22 at 20:40

score 1 · Answer 1 · answered Feb 03 '22 at 20:39

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

score 1 · Accepted Answer · answered Feb 03 '22 at 20:54

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.

EF Linq include last

2 Answers2