Developing Software

Mastering Software Craftsmanship

Using Case Sensitive Fields In Entity Framework Code First

by

For the past couple of days I have been working on a project that is using Entity Framework Code First and requires some data to be case sensitive. The problem is, by default you can’t do a query that is case sensitive with Entity Framework Code First. The following queries would bring back the same record.

// record exists with URL: developingsoftware.com
var url = context.Urls.SingleOrDefault(s => s.Url.Equals("developingsoftware.com"));

// brings back same record as above: developingsoftware.com
var url = context.Urls.SingleOrDefault(s => s.Url.Equals("DEVELOPINGSOFTWARE.COM"));

This is because the collation of the data stored in the database is case in-sensitive by default (SQL_Latin1_General_CP1_CI_AS). For the above query to be case sensitive, the collation of the Url column needs to be changed to SQL_Latin1_General_CP1_CS_AS.

How to Change the Collation?

With Code First migrations there is currently no way to set the collation of a field in the CreateTable method of the DbMigration class. This means you need to run raw SQL using the Sql method.

To change the collation we need to make sure migrations are enabled and then add a new migration. The following code should be put into the Up method of the migration:

public override void Up()
{
    Sql("ALTER TABLE Urls ALTER COLUMN Url VARCHAR(2000) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL);
}

Now run the Update-Database command and the above query should now work as expected.

But What if the Field Is Unique?

The above command works fine in most cases. But if the field has a unique index or is a primary key you will get an error message that’s something like: “ALTER TABLE ALTER COLUMN Url failed because one or more objects access this column”. To get around this, the unique constraint must be dropped first and then re-added. See below.

public override void Up()
{
    // drops the existing index name UIX_Url from the Url column
    DropIndex(table: "dbo.Urls", name: "UIX_Url");

    // alter the collation of the Url column
    Sql("ALTER TABLE dbo.Urls ALTER COLUMN Url VARCHAR(2000) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL");

    // re-create the index
    CreateIndex(table: "dbo.Urls", column: "Url", unique: true, name: "UIX_Url");
}

What if the Field is a Primary Key?

In most cases you will probably never encounter this because most primary keys are auto incrementing integers. The project I was working on stored a unique string for the primary key (In hindsight I would of done differently. Thinking about it, I’m going to change it now. Damn OCD!).

Lets say the Url in the previous examples was the primary key. To get around this, the primary key needs to be dropped first and then re-added after the collation change. See below:

public ovveride Up()
{
    // drops the existing primary key named PK_dbo.Urls
    Sql("ALTER TABLE dbo.Urls DROP CONSTRAINT [PK_dbo.Urls]");

    // change the collation
    Sql("ALTER TABLE dbo.Urls ALTER COLUMN Url VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL");

    // re-add the primary key to the Url column
    Sql("ALTER TABLE dbo.Urls ADD CONSTRAINT [PK_dbo.ShortUrls] PRIMARY KEY (Url)");
}

Now that the collation has been changed to case sensitive its also possible to store both developingsoftware.com and WWW.DEVELOPINGSOFTWARE.COM in the Url field even though it has a unique index.

Further Reading

More detailed discussion on Entity Framework can be found in the following publications:

Share this on: