"The LINQ expression ... could not be translated." error after updating AspNetCore project from .net 2.2 to .net 6

Question

After updating a legacy AspNet Core project using .NET Upgrade Assistant, most of the api works fine but the account/login endpoint is throwing the exception below:

{"statusCode":500,"success":false,"message":"The LINQ expression 'DbSet<User>()\r\n .Where(u => u.NormalizedUserName == __normalizedUserName_0)' could not be translated. Additional information: Translation of member 'NormalizedUserName' on entity type 'User' failed. This commonly occurs when the specified member is unmapped. 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.","data":null}

I already tried updating the projects' packages but the error persists. There are some deprecated packages that the project depends on too.

The exception comes from this line that is inside the login method that is called: await _userManager.CheckPasswordAsync(user, model.Password) where _userManager is an object of type UserManager from the Microsoft.AspNetCore.Identity namespace. The error message suggests to change the query but this is not an option since the CheckPasswordAsync method is from the AspNetCore.Identity package.

Also, the NormalizedUserName property of the User is already set to be ignored: builder.Ignore(x => x.NormalizedUserName); but apparently with no effect. Still, it's strange because direct requests to user (eg.: user/list) are working fine.

I'd appreciate any directions/suggestions that will possibly fix this. Thank you!

Answer 1

If Normalized is a code-computed column and/or marked as Ignore, you cannot use it in a Linq-to-entity query because EF cannot translate this into SQL. You have to either refine the query to use the raw data values, or you have to tell EF to use client-side evaluation, which comes with performance/memory use implications. This is why it "worked" in EF Core 2.2, Automatic Client-side evaluation was introduced and enabled by default. Since this was a potentially bigger performance landmine than lazy loading, they later disabled by default and changed the warning behaviour to an exception.

If your have something like:

public class User
{
   // ....
   public string FirstName { get; set; }
   public string LastName { get; set; }

   [NotMapped]
   public string NormalizedName = $"{FirstName} {LastName}";
}

You cannot write a query like:

 var user = context.Users
    .Where(x => x.NormalizedName == normalizedName)
    .FirstOrDefault();

You should ideally write this to use mapped columns:

 var user = context.Users
    .Where(x => x.FirstName == firstName && x.LastName == lastName)
    .FirstOrDefault();

You can re-enable client-side evaluation, but it cannot be activated query-by-query, it is enabled at the DbContext level:

(from: https://learn.microsoft.com/en-us/ef/core/querying/client-eval)

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFQuerying;Trusted_Connection=True;")
        .ConfigureWarnings(warnings => warnings.Throw(RelationalEventId.QueryClientEvaluationWarning));
}

To perform something like a client-side evaluation on a single query you need to materialize the results before attempting to access the ignored column or perform an operation that cannot be converted to SQL:

This would effectively be something like:

var user = context.Users
     .Where(x => /* Any conditions you can apply minus NormalizedName)
     .AsEnumerable()
     .Where(x => x.NormalizedName == normalizedName)
     .FirstOrDefault();

You want to filter as much as you can prior to calling an AsEnumerable() which will execute the query, then perform a further filtration using any fields that cannot be mapped.