CODEWITHSUNDEEP
c#.netentity-frameworklambda
Imran Rizvi
Imran Rizvi

Reputation: 7438

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

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?

Upvotes: 7

Views: 1722

Answers (6)

qujck
qujck

Reputation: 14578

As everyone else has already mentioned, the cause of the problem is that SQL Server defaults to not case sensitive and C# defaults to case sensitive.

You can change the password field from varchar to varbinary to get around this problem.

Why the anonymous down votes? Using a string to store and compare what appears to be a 64 byte hash value is not the best approach. There's no need to base 64 encode a hash to store it (unless Entity Framework can't handle it?).

http://support.microsoft.com/kb/307020 - How to compute and compare hash values by using Visual C# Comparing hash passwords

Upvotes: -1

juharr
juharr

Reputation: 32296

The SQL look-up is case insensitive and when you do the ToList first you end up doing a case sensitive comparison. But the ToList will return everything from the DB. instead do the Where on the db and then a further comparison to make sure the password is the correct case.

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

Upvotes: 3

Olly
Olly

Reputation: 6026

As @nmclean mentioned, this is because your database is configured to perform case insensitive comparisons. The following small program shows how EF completely ignores the StringComparison values passed to String.Equals. It also shows how changing the collation of the database column achieves what you need.

This is an imporant concept when working with EF (and other LINQ providers too). Your query as it is written looks like normal .NET code. However, it isn't! Many providers translate the code to something completely different, in this case SQL (for SQL Server, Transact-SQL). Only a sub-set of the available .NET methods will work at all (which is why, for example, you cant use Regex in an EF query), and those that do work may exhibit subtly different behaviour compared to what you would expect.

Where you use ToList() on a query, then apply your filter, you are actually using LINQ to Objects, not EF, to apply the filter. LINQ to Objects is "real" .NET, so you have the full range of .NET functionality at your disposal. Of course, you lose the benefit of having your code run on the SQL Server, so this isn't usually an option.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;

namespace ConsoleApplication2
{
    public class Initializer<T> : DropCreateDatabaseAlways<T> where T : DbContext
    {
        protected override void Seed(T context)
        {
            base.Seed(context);

            // Comment out this line and note how the count changes.
            context.Database.ExecuteSqlCommand("ALTER TABLE MyEntity ALTER COLUMN MyString nvarchar(MAX) COLLATE Latin1_General_CS_AS");
        }
    }

    [Table("MyEntity")]
    public class MyEntity
    {
        [Key]
        public virtual int MyEntityId { get; set; }

        [Required]
        public virtual string MyString { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<MyEntity> Entities
        {
            get { return this.Set<MyEntity>(); }
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            var e = modelBuilder.Entity<MyEntity>();
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer(new Initializer<MyContext>());

            using (MyContext context = new MyContext())
            {
                context.Entities.Add(new MyEntity { MyString = "aaa" });
                context.Entities.Add(new MyEntity { MyString = "AAA" });

                context.SaveChanges();
            }

            using (MyContext context = new MyContext())
            {
                var caseSensitiveQuery = from e in context.Entities
                                         where e.MyString.Equals("aaa", StringComparison.Ordinal)
                                         select e;

                var caseInsensitiveQuery = from e in context.Entities
                                           where e.MyString.Equals("aaa", StringComparison.OrdinalIgnoreCase)
                                           select e;

                // Note how the StringComparison parameter is completely ignored.  Both EF queries are identical.
                Console.WriteLine("Case sensitive query (count = {0}):\r\n{1}", caseSensitiveQuery.Count(), caseSensitiveQuery);
                Console.WriteLine();

                Console.WriteLine("Case insensitive query (count = {0}):\r\n{1}", caseInsensitiveQuery.Count(), caseInsensitiveQuery);
            }

            Console.ReadLine();
        }
    }
}

Upvotes: 2

Johannes Wanzek
Johannes Wanzek

Reputation: 2875

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.

Upvotes: 7

nmclean
nmclean

Reputation: 7734

There is a more detailed answer to this here:

https://stackoverflow.com/a/3843382/933416

If you need to, you can configure the column to be case sensitive on the server side. But in your case, I don't see why you even need to query the password. Surely there is only one password for each username, and a username is meant to be case insensitive -- so why not just query by username and then check the password on the result?

Upvotes: 1

pingoo
pingoo

Reputation: 2064

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.

Upvotes: 12

Related Questions