System.InvalidOperationException: The LINQ expression.... 'could not be translated. ASP.NET 6 ...Translation of method 'DecodeFrom64' failed

Question

In ASP.NET Core 6 Web API Project, I'm trying to retrieve and decode the password from the database before comparing it with the entered password.

I have this code:

public async Task<User> GetUser(string email, string password)
{
     return await _context.Set<User>().Where(e => e.Email == email
                    && DecodeFrom64(e.Password) == password).FirstOrDefaultAsync();
}

I got this error:

System.InvalidOperationException: The LINQ expression 'DbSet<User>()
    .Where(u => u.Email == __email_0 && 
DbHelper.DecodeFrom64(u.Password) == __password_1)'
could not be translated. Additional information: 
Translation of method 'UserOnboardingApi.Model.DbHelper.DecodeFrom64' > failed.

How do I get this resolved?

Thanks

Expected to retrieve and decode the password from the database and compare to what the user entered

EF translates LINQ queries to SQL. It can't convert local function calls. Besides, passwords are meant to be salted and hashed *multiple* times with a strong cryptographic function made for this. ASP.NET Identity hashes passwords 10K times although the current guidance is to use 300K iterations — Panagiotis Kanavos, Dec 05 '22 at 13:04
What is `DecodeFrom64`? The error is telling you that it can't be translated into the target query language (likely T-SQL in this case). — David, Dec 05 '22 at 13:04
Just add `.ToList()`: `return await _context.Set().ToList().Where(e => e.Email == email && DecodeFrom64(e.Password) == password).FirstOrDefaultAsync();` — Roman Ryzhiy, Dec 05 '22 at 13:04
@RomanRyzhiy: Sprinkling `.ToList()` everywhere as a magic solution will cause other problems. Always materializing *the entire database table* into code for any query won't scale well. — David, Dec 05 '22 at 13:05
@RomanRyzhiy this is a serious problem, not a `just add ToList()`. In the best case this would only cover up the bad query problem and result in bad performance. In this case it's a data breach waiting to happen — Panagiotis Kanavos, Dec 05 '22 at 13:06

Guru Stron · Accepted Answer · 2022-12-05T13:43:36.583

EF translates LINQ (when working with IQueryable) into SQL and it does not know anything about your method DecodeFrom64 and can't translate it. Options are

Encoding password and checking it server-side (db side):

public async Task<User> GetUser(string email, string password)
{
     return await _context.Set<User>()
          .Where(e => e.Email == email
                    && e.Password == YourEncodeMethod(password))
          .FirstOrDefaultAsync();
}

Fetching the user by email (I suppose it should be unique) and check password client-side. Something along this lines:

public async Task<User> GetUser(string email, string password)
{
     var user = await context.Set<User>()
          .FirstOrDefaultAsync(e => e.Email == email);
     if(user is not null && DecodeFrom64(user.Password) == password)
     {
          return user;
     }
     return null;
}

Implementing the decode function on the db side and mapping it.

But in general you should consider storing password hashes (see this question), not the encoded ones and check them.

Thank you all very much for your contributions....really leaned a lot. @guru-stron your solved my issue thanks very much — step ofori, Dec 05 '22 at 13:21

System.InvalidOperationException: The LINQ expression.... 'could not be translated. ASP.NET 6 ...Translation of method 'DecodeFrom64' failed

1 Answers1