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

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));

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.

Before 3.0, the expressions that EF Core could not translate would result in everything being pulled into client, then filtered there, which could become very slow. In 3.0, EF Core now throws an exception, which is aimed at developers to optimize their queries.

So, your error means that EF Core is unable to translate u.PasswordHash.SequenceEqual(pass) into SQL. You should find another way to filter your query.

There is a limited set of methods that EF Core translates into SQL (eg. string.StartsWith(), list.Contains()).

So I've also struggled with this problem when I desperately tried to create a fairly simple Where() LINQ expression to filter out my Orders in a table based on UserID and date of the order...

It turns out that in Where() function you can use only super simple expressions that are able to translate into SQL queries.

As a workaround I filtered it like this:

var orders = await _dataContext.Entities<Order>()
            .Where(order => order.UserID == query.UserID)
            .ToListAsync(); // WHERE must use only expressions that can be translated into simple SQL query

return orders
        .FindAll(order => IsBetweenDatesFromQuery(order.DateCreated, query))
        .ToList(); // This is workaround to further filter orders that are in between specified dates

IsBetweenDatesFromQuery() is my made-up function that returns a boolean based on a specified date. You can replace it with any kind of validation to filter out your results.