Linq performing case insensitive comparison

Question

I want user object only when exact password along with case matches. However this query fetches result even when case for password is not same:

db.Users.Where(u => u.Username.ToLower() == username.ToLower() &&
                        u.Password == password).FirstOrDefault();

What am I missing?

Answer 1

Simplest way is to do the username matching in the DB under it's case-insensitve rules and the password matching in .NET under its case-sensitive rules:

db.Users.Where(u => u.Username == username).ToList().Where(u => u.Password == password).FirstOrDefault();

The ToList() moves from db-based LINQ to object-based LINQ, and since there would only be one matching case anyway, the performance impact of doing so is negligible.

Still has the problem of storing a password in a database though!

Answer 2

If the database is configured as case-insensitive, then you cannot get it to do a case-sensitive comparison on the password (unless you resort to TSQL tricks). However! You should not be storing a password - you should be storing a salted hash (ideally salted per user). And the hash (as a blob) should be fine to compare this way.