EF Core rewrite the query in a form that can be translated after upgrading-CodePudding

This query was working fine before we upgraded:

var appUsers = await _masterDbContext
                      .Users
                      .Include(x => x.UserCustomers)
                      .AsNoTracking()
                      .Select(AppUserDto.Projection)
                      .Where(user => !user.IsDeleted && user.UserCustomers.Any(x => x.CustomerId == tenant.Id && x.UserId == user.Id))
                      .DistinctBy(x => x.Id)
                      .ToDataSourceResultAsync(request.GridOptions, cancellationToken: cancellationToken);

We're now getting the error:

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'.

It appears that the DistinctBy is the offender but being fairly new to LINQ I can't figure out how to rewrite it so that it works.

CodePudding user response：

Looks you have upgraded from EF Core 2.x, which loads full filtered set into the memory. DistinctBy is not translatable by EF Core 6.

Try the following solution:

var filtered = _masterDbContext.Users
    .Select(AppUserDto.Projection)
    .Where(user => !user.IsDeleted && user.UserCustomers.Any(x => x.CustomerId == tenant.Id && x.UserId == user.Id));

var query = 
    from d in filtered.Select(d => new { d.Id }).Distinct()
    from u in filtered.Where(u => u.Id == d.Id).Take(1)
    select u;

var appUsers = await query.ToDataSourceResultAsync(request.GridOptions, cancellationToken: cancellationToken);

CodePudding user response：

Change DistinctBy to Distinct() and move that and the predicate before the Select. I also shifted the AsNoTracking() up:

var appUsers = await _masterDbContext
    .AsNoTracking()
    .Users
    .Include(x => x.UserCustomers)
    .Where(user => 
        !user.IsDeleted 
        && user.UserCustomers
            .Any( x => x.CustomerId == tenant.Id ) )
    .Distinct()
    .Select(AppUserDto.Projection)
    .ToDataSourceResultAsync(request.GridOptions, cancellationToken: cancellationToken);