Strange behaviour in Entity Framework Core with sqlite

Question

I have a simple DbContext with a Set<> of users.

I've got an ASP.NET Core Web API with authentication.

public User Authenticate(string username, string password)
{
    var user = _ctx.Users.FirstOrDefault(x => 
            x.Username.Equals(username, StringComparison.OrdinalIgnoreCase) && 
            x.Password.Equals(password, StringComparison.OrdinalIgnoreCase));

    bool exists = _ctx.Users.Any(x =>
            x.Username.Equals(username, StringComparison.OrdinalIgnoreCase) &&
            x.Password.Equals(password, StringComparison.OrdinalIgnoreCase));

    bool usernameCorrect = _ctx.Users.First().Username.Equals(username, StringComparison.OrdinalIgnoreCase);
    bool passwordCorrect = _ctx.Users.First().Password.Equals(password, StringComparison.OrdinalIgnoreCase);

    if (user == null)
    {
        return null;
    }
    [...]
}

The problem is that _ctx.Users.FirstOrDefaultis returning null.

You might think this is simple: "the condition does not match"

But:

What is going on here?

Jamal Salman · Accepted Answer · 2019-10-01 08:40:46Z

1

What's happening is that the comparison with OrdinalIgnoreCase works only with materialized object. (Your IQueryable provider does not support it). It is working in the 3rd and 4th case because you materialized the object first.

try instead using string.Compare(string str1, string str2, StringComparison.OrdinalIgnoreCase) == 0

answered Oct 1, 2019 at 8:40

Jamal Salman

4076 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Felix D. Over a year ago

This does not work ;( I get an exception by entityframework sayin it cannot be translated.

Felix D. Over a year ago

But I get the point. _ctx.Users.SingleOrDefault(x => x.Username.Equals(username) && x.Password.ToLower().Equals(password.ToLower())); is working fine!

Jamal Salman Over a year ago

StringComparison.OrdinalIgnoreCase is usually a bad practice for comparing usernames and passwords, but if you really want it so you can convert your strings to lower case and use the normal == to compare your strings as a workaround

Felix D. Over a year ago

I'm working with md5 hashes right now. I guess it's fine to lower them :P

Collectives™ on Stack Overflow

Strange behaviour in Entity Framework Core with sqlite

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related