Case sensitive search using Entity Framework and Custom Annotation

downloadEF6 has some really cool features. I was especially interested in the ability to use custom attributes to affect migrations. In this article, I will use the example of changing whether a column is case sensitive to illustrate how to use this feature.

In sql server when we are writing a WHERE condition, it is case insensitive by default. But there can be situations which we need to perform case sensitive search. We can achieve that by changing the default collations (SQL_Latin1_General_CP1_CI_AS )  of the column to SQL_Latin1_General_CP1_CS_AS.

Case sensitivity issue in EF
At the moment of writing this article there is no built in feature to change collation of a column in entity framework code first approach. So if we run below test case, it will fail.

[TestFixture]
public class SoftwareTests
{
    [Test]
    public void ActivationKeysCaseSensitivityTest()
    {
        var db = new ApplicationDbContext();
        db.Softwares.RemoveRange(db.Softwares.ToList());

        var word = new Software { Name = "Word", ActivationsKey = "AcaT" };
        var excel = new Software { Name = "Excel", ActivationsKey = "acat" };
        db.Softwares.AddRange(new List<Software> { word, excel });
        db.SaveChanges();

        db.Softwares.Where(s => s.ActivationsKey == "acat").ToList()
            .Should().Contain(excel)
            .And.NotContain(word);
    }
}

We need a programmable way of changing the collation. The fragile way of doing this, is by adding a new migration file and then add following code inside the “Up” method.

public override void Up()
{
  Sql("ALTER TABLE Softwares ALTER COLUMN ActivationsKey VARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL");
}

I think above approach is fragile because of the following reasons.

  • If you change the name of the column you have to do modify the existing code and It violates the open close principle.
  • If you need this feature for another column, you have to add a new script to change collations of those fields. This will ended up with high density of code duplication when it comes to an enterprise level application
  • Sometimes we delete and recreate migrations files and then the scripts that we added manually will be missed.
  • In development stage we normally use auto migrations. So we have to maintain those alterations inside the seed method.

Proposed Solution (EF6 Onwards Only)
But with the “HasColumnAnnotation” method which is provided by the Fluent Migration API, we can change collations in an elegant way. What we are going do is that adding a custom annotation called “CaseSensitive” using the “HasColumnAnnotation” method and then allows migrations pipeline to alter column collation to SQL_Latin1_General_CP1_CS_AS if the column has “CaseSensitive” annotation.
I have added following fluent configuration for “Software” entity.

public class SoftwareConfiguration : EntityTypeConfiguration<Software>
{
    public SoftwareConfiguration()
    {
        Property(p => p.ActivationsKey)
        .HasColumnAnnotation("CaseSensitive", true);
    }
}

Then I have added the “CustomSqlServerMigrationSqlGenerator” class which is inherited from SqlServerMigrationSqlGenerator which knows how to do relevant changes if the column has “CaseSensitve” Annotation. More info about SqlServerMigrationSqlGenerator.
I have put my logic after the “base.Generate(alterColumnOperation)” method call to keep the default behaviours.

public class CustomSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(AlterColumnOperation alterColumnOperation)
    {
        base.Generate(alterColumnOperation);
        AnnotationValues values;
        if (alterColumnOperation.Column.Annotations.TryGetValue("CaseSensitive", out values))
        {
            if (values.NewValue != null && values.NewValue.ToString() == "True")
            {
                using (var writer = Writer())
                {
                    writer.WriteLine(
                        "ALTER TABLE {0} ALTER COLUMN {1} NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL",
                        alterColumnOperation.Table,
                        alterColumnOperation.Column.Name);
                    Statement(writer);
                }
            }
        }
    }
}

Finally we need to inform the migration pipeline about our “CustomSqlServerMigrationSqlGenerator” Class . For that we can use “CustomDbConfiguration” Class which is inherited from “DbConfiguration” Class. More info about CustomDbConfiguration.

public class CustomApplicationDbConfiguration:DbConfiguration
{
    public CustomApplicationDbConfiguration()
    {
        SetMigrationSqlGenerator(
            SqlProviderServices.ProviderInvariantName,
            ()=>new CustomSqlServerMigrationSqlGenerator());
    }
}

Now If we add new migrations using “add-migration” command, it should add following code.

public override void Up()
{
    AlterColumn("dbo.Softwares", "ActivationKey", c => c.String(
        annotations: new Dictionary<string, AnnotationValues>
        {
            {
                "CaseSensitive",
                new AnnotationValues(oldValue: null, newValue: "True")
            },
        }));
}

public override void Down()
{
    AlterColumn("dbo.Softwares", "ActivationKey", c => c.String(
        annotations: new Dictionary<string, AnnotationValues>
        {
            {
                "CaseSensitive",
                new AnnotationValues(oldValue: "True", newValue: null)
            },
        }));
}

Then we can update the database and run the test. and test will pass. 🙂

This method works even if you use auto migrations. But the limitation is our overridden “Genarated” method runs when a column is altered. That means if we add a new column and at the same time if we add the “CaseSensitive” annotation for that column, the collation change will not be applied when we do migrations. To enable it, we need to override “ Generate(AddColumnOperation addColumnOperation)” method and “Generate(CreateTableOperation createTableOperation)” method. We can put the same logic after “base.Generate(addColumnOperation)” and “base.Generate(createTableOperation)” methods.

Use “CaseSensitive” as an attribute rather than Fluent Configuration

It is also possible to create a .NET attribute and couple it to a Code First convention such that adding the attribute into a property will cause the corresponding column to be annotated.
I have added the “CaseSensitiveAttribute” Class which is inherited from “Attribute” class.

[AttributeUsage(AttributeTargets.Property, AllowMultiple = true)]
public class CaseSensitiveAttribute : Attribute
{
    public CaseSensitiveAttribute()
    {
        IsEnabled = true;
    }
    public bool IsEnabled { get; set; }
}

Then add a convention to use this attribute to create column annotations inside the “OnModelCreating” method of the “ApplicationDbContex” class. More details about Custom Code First Conventions.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Add(new AttributeToColumnAnnotationConvention<CaseSensitiveAttribute, bool>(
            "CaseSensitive",
            (property, attributes) => attributes.Single().IsEnabled));
    base.OnModelCreating(modelBuilder);
}

Now we can decorate our model properties using “CaseSensitiveAttribute”

public class Software
{
    public int Id { get; set; }
    public string Name { get; set; }

    [CaseSensitive]
    public string ActivationsKey { get; set; }
}

The main goal of this article is introduce set of new features on EF6 using an example. You can use this features as you want to transform your data access code to readable and reusable code.

Advertisements

3 thoughts on “Case sensitive search using Entity Framework and Custom Annotation

  1. Pingback: Let’s polish tests until they shine – part 1 | Optimism

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s