Why I am getting different result from two almost equal expressions to get data from database using Entity Framework context

Question

I am validating username (case-insensitive) and password (case-sensitive) from database

I am using Entity Framework 5.0 to interact with database

In the database password is

"0x11A46971EFF1E1A2CA228CF592CA37DC77E7CCF759602D53629C22B693AEEE96CCD9F889D8E9A92C19391E6BD2DD07E741E9B7AA07E391ACDC939B993C9D7F5D"

I am expecting null in return for my following code block when I change case of password to lower i.e.

"0x11a46971eff1e1a2ca228cf592ca37dc77e7ccf759602d53629c22b693aeee96ccd9f889d8e9a92c19391e6bd2dd07e741e9b7aa07e391acdc939b993c9d7f5d"

but it does not fail and return proper user entity.

using (var context = new MyDbContext(ConnectionString))
{
  return context.Users.Where(x => (x.Name.Equals(userName, StringComparison.OrdinalIgnoreCase) && x.Password.Equals(password))).FirstOrDefault();
}

Whereas if I get all Users and then compare it gives proper output i.e. user == null

using (var context = new MyDbContext(ConnectionString))
{
  var users = context.Users.ToList();

  return users.Where(x => (x.Name.Equals(userName,StringComparison.OrdinalIgnoreCase) && x.Password.Equals(password))).FirstOrDefault();
}

This is Strange? Why it is happening? How to write case sensitive query to SQL from LINQ?

Answer 1

SQL is not case sensitive. If you looked at the generated SQL it would look something like this:

EXISTS(SELECT * FROM Users WHERE userName = 'foo' AND Password = '0x11a46971eff1e1a2ca228cf592ca37dc77e7ccf759602d53629c22b693aeee96ccd9f889d8e9a92c19391e6bd2dd07e741e9b7aa07e391acdc939b993c9d7f5d')

It will return true regardless of case. The second example calls ToList so it is now doing a .net string compare what IS case sensitive.

Answer 2

When using

return context.Users.Where(x => (x.Name.Equals(userName, StringComparison.OrdinalIgnoreCase) && x.AuthenticationSecret.Equals(password))).FirstOrDefault();

you work on a lazyloaded EntityCollection. Because SQL is not case sensitive it will return true every time.

If you are using

var users = context.Users.ToList();

you switch from LINQ To Entities to LINQ To Objects, so you can now make a case sensitive comparison.

The big disadvantage is that everytime you are using ToList() your Query will be executed immediatly and you will load the COMPLETE List of Users from the Database.