First() could not be translated when accessing first group element (Linq GroupBy)

Question

I am using EF Core with SQLite to store a bunch of Weather Reports:

public class WeatherReport
{
    [Key]
    public string ReportId { get; set; }
    public float Temperature { get; set; }
    public DateTime ReportDate { get; set; }
}

In my API controller, I return them like so:

IEnumerable<Models.TemperatureGetDTO> weatherReports = await _db.WeatherReports
    .Select(g => new Models.TemperatureGetDTO {
        ReportDate = g.ReportDate,
        Temperature = g.Temperature
    })
    .ToListAsync();

return Ok(weatherReports);

which returns the following JSON'ified data:

{"reportDate":"2021-03-13T23:56:14.0135403","temperature":22},
{"reportDate":"2021-03-13T23:57:14.1441771","temperature":22},
{"reportDate":"2021-03-13T23:58:14.2924322","temperature":22},
{"reportDate":"2021-03-13T23:59:14.4499289","temperature":21.9},
{"reportDate":"2021-03-14T00:00:14.651818","temperature":22},
{"reportDate":"2021-03-14T00:01:14.7563863","temperature":22},
{"reportDate":"2021-03-14T00:02:14.886777","temperature":22},
{"reportDate":"2021-03-14T00:03:15.0797178","temperature":22},
{"reportDate":"2021-03-14T00:04:15.2898459","temperature":22}
...

Now, however, I'd like to group the temperatures by hour, and get the first weather report for every hour (grouping). I have tried writing the query like so:

var weatherReports = await _db.WeatherReports
    .GroupBy(w => w.ReportDate.Hour)
    .Select(g => new {
        Hour = g.Key,
        Temperature = g.OrderBy(w => w.ReportDate).First().Temperature
    })
    .ToListAsync();

This, however, produces the following error:

The LINQ expression 'GroupByShaperExpression: KeySelector: CAST(strftime('%H', w.ReportDate)) AS INTEGER), ElementSelector:EntityShaperExpression: EntityType: WeatherReport ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False

.Select(s => s.Temperature) .First()' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

What am I doing wrong here? To get the first item from a group, should I not use First()?

Answer 1

It means EF cannot translate the expression into SQL so that it can be executed server side.. In the old days EF would just go and download all the data to the client and do the data manipulation there, but nowadays expressions that cannot be translated throw an exception instead.

Either force the data to be downloaded before the non-translatable expression, with something like:

(await _db.WeatherReports.GroupBy(w => w.ReportDate.Hour).ToListAsnc())
  .Select(g => new {
    Hour = g.Key,
    Temperature = g.OrderBy(w => w.ReportDate).First().Temperature
  });

Or

(await _db.WeatherReports.ToListAsync())
  .GroupBy(w => w.ReportDate.Hour)
  .Select(g => new {
    Hour = g.Key,
    Temperature = g.OrderBy(w => w.ReportDate).First().Temperature
});

Or perhaps drop to raw SQL for this, as it's a reasonably advanced use:

SELECT * FROM
(
  SELECT *, ROW_NUMBER() OVER(PARTITION BY strftime('%Y%m%d%H', ReportDate) ORDER BY ReportDate) rn
  FROM WeatherReports
) WHERE rn = 1

---

By the way, be careful grouping by only the hour as you have done in your LINQ if the data in the table spans more than a day; data from 1300hours yesterday will pollute 1300 today..