Why "The LINQ expression 'x' could not be translated" ? I'm not using "Where()"

Question

When I execute following code, I get an error:

System.InvalidOperationException: The LINQ expression 'DbSet.Where(u => u.NormalizedEmail == __ToLower_0 && u.PasswordHash.SequenceEqual(__pass_1))' could not be translated

But it was working fine in .NET Core 2.2. Error is raised in .NET Core 3.1.

loginCreds.Password = loginCreds.Password.ToLower();
var pass = Helper.ComputeHash(loginCreds.Password);
var usr = await _context.Users
                        .FirstOrDefaultAsync(u => u.NormalizedEmail == loginCreds.Email
                                                  && u.PasswordHash.SequenceEqual(pass));

But, when I replace u.PasswordHash.SequenceEqual(pass) with u.PasswordHash == new byte[16] (just for testing), it works. So, problem is the SequenceEqual(byte[] byte) method.

How can I solve this?

Answer 1

You may have the answer here EF Core 3.0 in the "Restricted client evaluation"

For example, if EF Core 2.2 couldn't translate a predicate in a Where() call, it executed an SQL statement without a filter, transferred all the rows from the database, and then filtered them in-memory

....

In EF Core 3.0, we've restricted client evaluation to only happen on the top-level projection (essentially, the last call to Select()). When EF Core 3.0 detects expressions that can't be translated anywhere else in the query, it throws a runtime exception.

With EF Core 2.2 the part of the query with SequenceEqual was not actually done in SQL.

You should try to do this :

var usr = await _context.Users
.Where(u => u.NormalizedEmail == loginCreds.Email)
.ToListAsync()
.FirstOrDefault(u => u.PasswordHash.SequenceEqual(pass));

Answer 2

You said it works in dotnet core 2.2 but not in dotnet core 3.1. I'm assuming this means you're also using Entity Framework Core 3 in the dotnet core 3.1 version, and it looks like this is a breaking change in Entity Framework Core 3. See here.

Old behavior

Before 3.0, when EF Core couldn't convert an expression that was part of a query to either SQL or a parameter, it automatically evaluated the expression on the client. By default, client evaluation of potentially expensive expressions only triggered a warning.

New behavior

Starting with 3.0, EF Core only allows expressions in the top-level projection (the last Select() call in the query) to be evaluated on the client. When expressions in any other part of the query can't be converted to either SQL or a parameter, an exception is thrown.

SequenceEqual cannot be converted to SQL or a parameter, so in version 2.2 it was automatically executing that on the client. Now in the 3.1 version, it's throwing an InvalidOperationException. Using the equality operator works, because that can be translated to a SQL statement.

In order to fix it, why not select by Email then compare the password?

loginCreds.Password = loginCreds.Password.ToLower();
var pass = Helper.ComputeHash(loginCreds.Password);
var usr = await _context.Users
    .FirstOrDefaultAsync(u =>u.NormalizedEmail == loginCreds.Email));
bool validUser = false;
if (usr != null)
{
    validUser = usr.PasswordHash.SequenceEquals(pass);
}
// if validUser is true, then the credentials were valid.