Adding Unique Constraints with Entity Framework Code First

Entity Framework Code First is a great way to define and maintain your database within your application it self. While it poses a nice set of complementing libraries like Data Annotations and Fluent Configurations which helps you specially in defining phase of your database, it would give you a headache if you try to define a unique constraint on a column. For example in the users table of your application you could probably have an int ID column which would serve as the primary key and you might need to make your Username column a unique one.

Since you are using EF Code First you will soon find out there is no direct way to accomplish this requirement. Unfortunately fluent configurations doesn't have syntax like

HasUnique(u => u.Username);

I asked the same question in stackoverflow, but didn't get a convincing answer. Since there is no direct support from EF for this you could take one of following alternatives to achieve it.

Approach 1 : 

You can code it right inside your business logic. When you are trying to save a user from your application you can have an additional validation logic which checks if there are any users in the database with same username, needless to say that this is really a manual approach and you will have to implement this each and every place you want to validate a unique constraint which will be cumbersome.

Approach 2 :

You can use a migration and run a plain SQL command using the Sql() method to add the unique constraint.

This will be an ideal solution if you have a guarantee that you do not change the database behind and it will surely lock you to the database you are currently using. Because the plain SQL statement you write here will be a vendor specific one. Following example is written for SQL Server.

namespace EFSample.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
    
    public partial class AddUniqueToUsernameInUsers : DbMigration
    {
        public override void Up()
        {
            Sql("ALTER TABLE dbo.Users ADD CONSTRAINT UC_Username UNIQUE (Username)");
        }
        
        public override void Down()
        {
            Sql("ALTER TABLE dbo.Users DROP CONSTRAINT UC_Username");
        }
    }
}

Given that you do not ever change the database vendor this is a pretty clean solution.

And if you dig further in to what's actually happening inside you could see, that it adds a unique index on username and nothing else, our third approach relies on this fact.

Approach 3 :

Here we will use a migration and add the index directly with C# code without using the native SQL syntax. Since we are using methods provided by the Entity Framework we could hope that it would run on databases from different vendors.

namespace EFSample.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
    
    public partial class AddUniqueIndexToUsernameInUsers : DbMigration
    {
        public override void Up()
        {
            CreateIndex("dbo.Users","Username",true);
        }
        
        public override void Down()
        {
            DropIndex("dbo.Users", "Username");
        }
    }
}

By using the above migration we could achieve the same results as Approach 2, without any vendor specific SQL statements, so I would prefer the third approach over other two since it is the more durable solution.