LINQ expression error after upgrading to .Net7

Question

After upgrading from .Net2.2 to .Net7, the following LINQ expression fails with this error "LINQ expression could not not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly":

string excludeString = "XX";
string excludeString2 = "XX";
var groupfilter = await _db.UserGroup.Where(q => q.UserName == currUserName && q.IsActive == false && q.WorkGroupName == "BB").ToListAsync();
if (groupfilter.Any())
    excludeString = "BB";

groupfilter = await _db.UserGroup.Where(q => q.UserName == currUserName && q.IsActive == false && q.WorkGroupName == "TS").ToListAsync();
if (groupfilter.Any())
    excludeString2 = "TS";

DriveListViewModel model = new DriveListViewModel()
{
    Drive = await _db.Drive
        .Where(m => m.StatusId == 5 || m.StatusId == 1010 || m.StatusId == 1012)
        .Where(m => m.LoadingComplete == null)
        .Where(m => !m.UnitCode.Contains(excludeString))
        .Where(m => !m.UnitCode.Contains(excludeString2))
        .Include(s => s.DriveStatus)
        .Include(d => d.Location)
        .Include(f => f.Item)
        .GroupBy(m => m.RegistrationNumber)
        .Select(m => m.FirstOrDefault())
        .OrderBy(m => m.DriverToLoad)
        .ToListAsync(),
    machineryList = await _db.Machinery.ToListAsync(),
    cleaningList = await _db.Cleaning.ToListAsync(),
};

Disabling these 3 lines removes the error:

    .GroupBy(m => m.RegistrationNumber)
    .Select(m => m.FirstOrDefault())
    .OrderBy(m => m.DriverToLoad)

...however, I need this list to be distinct on "RegistrationNumber", so I need a modified query to obtain the same result.

Any ideas how to solve that in .Net7 ?

The bug was in EF Core 2, not EF Core 7. EF Core 2 was so limited that many operations couldn't be translated to SQL so it silently loaded all on the client and processed them using LINQ-to-Objects. Since EF Core 3.1 silent client-side evaluation if off by default and expressions that can't be translated raise errors — Panagiotis Kanavos, Jan 13 '23 at 10:03
What is `.Select(m => m.FirstOrDefault())` supposed to do? What it *does*, if evaluated on the client side, is return a single random element from the group. There's no implicit order in database tables or results unless an `ORDER BY` enforces it. What this query did, was load *all* the rows that matched the filter expressions, *partition* them by `RegistrationNumber` *with no specific order* and then pick the first *object*, whatever that is. — Panagiotis Kanavos, Jan 13 '23 at 10:07
What is this query supposed to do? If there are multiple Drives with the same RegistrationNumber, which one of them do you want to load? The first by DriverToLoad ? After `GroupBy`, `m` is actually a *group*, so `g` would be a better letter. As the duplicate shows, you can use `g.OrderBy(m=>m.DriverToLoad).Take(1)` to load the first item per group — Panagiotis Kanavos, Jan 13 '23 at 10:10
@PanagiotisKanavos, you telling right things but not solution for this issue. This query should be translatable by EF Core 7, if even it is not right from data perspective. — Svyatoslav Danyliv, Jan 13 '23 at 10:23
Which EF Core version do you use? I know that in .NET 7 you can use EF Core 3.1.x — Svyatoslav Danyliv, Jan 13 '23 at 10:35
OT for those groupfilters, there is also an [`AnyAsync()`](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.entityframeworkqueryableextensions.anyasync?view=efcore-7.0) method - then you don't need to load the whole matching list (ToListAsync()), only to see whether it contains something — Hans Keﬆing, Jan 13 '23 at 12:29
Does this answer your question? https://stackoverflow.com/questions/66526675/error-while-flattening-the-iqueryablet-after-groupby/66529949#66529949 — Amy B, Jan 13 '23 at 13:28
Your problem (taking top 1 per group) is a subset of the more general problem of taking top N per group, hence the duplicate. As usual for such problems, the only difference is the usage of `Take(N)` vs `First{OrDefault}` at the end. — Ivan Stoev, Jan 14 '23 at 04:12

score 1 · Answer 1 · answered Jan 13 '23 at 10:24

The EF Core 2.2 query wasn't working from the start. Instead of warning you though, EF Core silently loaded all matching rows in memory and partitioned them on the client. EF Core 2 was so limited that many important operations like GroupBy couldn't be translated to SQL, so silent client-side evaluation was used. I suspect the application is already logging EF warnings about this.

EF Core 3.0 added support for all important operations so client-side evaluation was disabled by default. Loading 10000 rows when you only wanted the SUM by 10 categories wasn't a very nice experience. Since EF Core 3, expressions that can't be translated to SQL throw an exception.

The original query doesn't do what it seems to do either, and produces somewhat random results. There's no implicit order in a table or query results unless an ORDER BY clause is used. There's no such clause in .Select(m => m.FirstOrDefault()) though, so the first object returned from the database is used, whatever that is.

If the intention is to return the first Driver by DriverToLoad for each RegistrationNumber, the following query should work:

var statuses=new[]{5,1010,1012};

var drives=await _db.Drive
        ...
        .GroupBy(m => m.RegistrationNumber)
        .Select(g => g.OrderBy(m=>m.DriverToLoad).Take(1))
        .ToListAsync()

The Where clauses can be simplified quite a bit too, and contain some performance problems.

This condition translates to LIKE '%%XX%% which can't use any indexes and can result in a full table scan :

    .Where(m => !m.UnitCode.Contains(excludeString))

A prefix search on the other hand can use indexes as it's essentially a range search between the prefix and the next largest string. If possible, use

    .Where(m => !m.UnitCode.StartsWith(excludeString))

Wrong `.Select(g => g.OrderBy(m=>m.DriverToLoad).First())` should work at least from EF Core 6. — Svyatoslav Danyliv, Jan 13 '23 at 10:27
That did the trick! :-) Thank you so much all of you for your kind and quick help. I really appreciate it. @Svyatoslav: Can you please make your comment as an answer? Then I can mark it is solved. — René Wester, Jan 13 '23 at 11:01

LINQ expression error after upgrading to .Net7

1 Answers1