EF Core: Unable to translate set operation after client projection has been applied

Question

I'm currently working on a project using Entity Framework Core and I've come across an issue with a LINQ query. The query worked fine in previous versions of EF (EF -6) but after upgrading to the latest version (EF core -7), I'm getting the following exception:

"Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last 'Select' call."

Here is the problematic code

var qTypes = DbRead.datas.AsNoTracking().Where(x => SelectionTypes.Contains(x.type));

var q1 = qTypes
    .GroupBy(x => x.type)
    .Select(g => g.Where(x => x.timestamp <= fromTimestamp).OrderByDescending(x => x.timestamp).FirstOrDefault());

var q2 = qTypes.Where(x => x.timestamp > fromTimestamp && x.timestamp <= toTimestamp);

var qResult = q1.Union(q2).OrderBy(x => x.timestamp).ThenBy(x => x.type);

var entries = new List<SelectionEntry>();

foreach (var entity in qResult)
{
    if (entity is null)
    {
        continue; // skip - caused by the FirstOrDefault in q1 above
    }

    entries.Add(FromSelectionEntity(entity));
}
return entries;

I have tried different approaches to restructure the query but without success. Any suggestions or insights on how to resolve this issue would be greatly appreciated.

Thank you in advance for your help!

Answer 1

Try the following workaround:

var qTypes = DbRead.datas.AsNoTracking().Where(x => SelectionTypes.Contains(x.type));

var q1Filtered = qTypes.Where(x => x.timestamp <= fromTimestamp);
var q1 = 
    from d in q1Filtered.Select(d => new { d.Type }).Distinct()
    from q in q1Filtered
      .Where(q => q.Type == d.Type)
      .OrderByDescending(x => x.timestamp)
      .Take(1)
    select q;

var q2 = qTypes.Where(x => x.timestamp > fromTimestamp && x.timestamp <= toTimestamp);

var qResult = q1.Union(q2).OrderBy(x => x.timestamp).ThenBy(x => x.type);

var entries = qResult.Select(entity => FromSelectionEntity(entity)).ToList();
return entries;