Unique Constraint in Entity Framework Code First

Question

As far as I can tell, there's no way to do this with Entity Framework at the moment. However, this isn't just a problem with unique constraints... you may want to create indexes, check constraints, and possibly triggers and other constructs too. Here's a simple pattern you can use with your code-first setup, though admittedly it's not database agnostic:

public class MyRepository : DbContext {
    public DbSet<Whatever> Whatevers { get; set; }

    public class Initializer : IDatabaseInitializer<MyRepository> {
        public void InitializeDatabase(MyRepository context) {
            if (!context.Database.Exists() || !context.Database.ModelMatchesDatabase()) {
                context.Database.DeleteIfExists();
                context.Database.Create();

                context.ObjectContext.ExecuteStoreCommand("CREATE UNIQUE CONSTRAINT...");
                context.ObjectContext.ExecuteStoreCommand("CREATE INDEX...");
                context.ObjectContext.ExecuteStoreCommand("ETC...");
            }
        }
    }
}

Another option is if your domain model is the only method of inserting/updating data in your database, you could implement the uniqueness requirement yourself and leave the database out of it. This is a more portable solution and forces you to be clear about your business rules in your code, but leaves your database open to invalid data getting back-doored.

Starting with EF 6.1 it is now possible:

[Index(IsUnique = true)]
public string EmailAddress { get; set; }

This will get you a unique index instead of unique constraint, strictly speaking. For most practical purposes they are the same.

Not really related to this but it might help in some cases.

If you're looking to create a unique composite index on let's say 2 columns that will act as a constraint for your table, then as of version 4.3 you can use the new migrations mechanism to achieve it:

• http://msdn.microsoft.com/en-us/library/hh770484(v=vs.103).aspx
• http://blogs.msdn.com/b/adonet/archive/2012/02/09/ef-4-3-code-based-migrations-walkthrough.aspx

Basically you need to insert a call like this in one of your migration scripts:

CreateIndex("TableName", new string[2] { "Column1", "Column2" }, true, "IX_UniqueColumn1AndColumn2");

Something like that:

namespace Sample.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class TableName_SetUniqueCompositeIndex : DbMigration
    {
        public override void Up()
        {
            CreateIndex("TableName", new[] { "Column1", "Column2" }, true, "IX_UniqueColumn1AndColumn2");
        }

        public override void Down()
        {
            DropIndex("TableName", new[] { "Column1", "Column2" });
        }
    }
}

I do a complete hack to get SQL executed when the database is being created. I create my own DatabaseInitializer and inherit from one of the provided initializers.

public class MyDatabaseInitializer : RecreateDatabaseIfModelChanges<MyDbContext>
{
    protected override void Seed(MyDbContext context)
    {
        base.Seed(context);
        context.Database.Connection.StateChange += new StateChangeEventHandler(Connection_StateChange);
    }

    void Connection_StateChange(object sender, StateChangeEventArgs e)
    {
        DbConnection cnn = sender as DbConnection;

        if (e.CurrentState == ConnectionState.Open)
        {
            // execute SQL to create indexes and such
        }

        cnn.StateChange -= Connection_StateChange;
    }
}

That's the only place I could find to wedge in my SQL statements.

This is from CTP4. I don't know how it works in CTP5.

Just trying to find out if there was a way to do this, only way I found so far was enforcing it myself, I created an attribute to be added to each class where you supply the name of the fields you need to be unique:

    [System.AttributeUsage(System.AttributeTargets.Class, AllowMultiple=false,Inherited=true)]
public class UniqueAttribute:System.Attribute
{
    private string[] _atts;
    public string[] KeyFields
    {
        get
        {
            return _atts;
        }
    }
    public UniqueAttribute(string keyFields)
    {
        this._atts = keyFields.Split(new char[]{','}, StringSplitOptions.RemoveEmptyEntries);
    }
}

Then in my class I'll add it:

[CustomAttributes.Unique("Name")]
public class Item: BasePOCO
{
    public string Name{get;set;}
    [StringLength(250)]
    public string Description { get; set; }
    [Required]
    public String Category { get; set; }
    [Required]
    public string UOM { get; set; }
    [Required]
}

Finally, I'll add a method in my repository, in the Add method or when Saving Changes like this:

private void ValidateDuplicatedKeys(T entity)
{
    var atts = typeof(T).GetCustomAttributes(typeof(UniqueAttribute), true);
    if (atts == null || atts.Count() < 1)
    {
        return;
    }
    foreach (var att in atts)
    {
        UniqueAttribute uniqueAtt = (UniqueAttribute)att;
        var newkeyValues = from pi in entity.GetType().GetProperties()
                            join k in uniqueAtt.KeyFields on pi.Name equals k
                            select new { KeyField = k, Value = pi.GetValue(entity, null).ToString() };
        foreach (var item in _objectSet)
        {
            var keyValues = from pi in item.GetType().GetProperties()
                            join k in uniqueAtt.KeyFields on pi.Name equals k
                            select new { KeyField = k, Value = pi.GetValue(item, null).ToString() };
            var exists = keyValues.SequenceEqual(newkeyValues);
            if (exists)
            {
                throw new System.Exception("Duplicated Entry found");
            }
        }
    }
}

Not too nice as we need to rely on reflection but this so far is the approach that works for me! =D

Also in 6.1 you can use the fluent syntax version of @mihkelmuur's answer like so:

Property(s => s.EmailAddress).HasColumnAnnotation(IndexAnnotation.AnnotationName,
new IndexAnnotation(
    new IndexAttribute("IX_UniqueEmail") { IsUnique = true }));

The fluent method isnt perfect IMO but at least its possible now.

More deets on Arthur Vickers blog http://blog.oneunicorn.com/2014/02/15/ef-6-1-creating-indexes-with-indexattribute/

An easy way in visual basic using EF5 Code First Migrations

Public Class Sample

    Public Property SampleId As Integer

    <Required>
    <MinLength(1),MaxLength(200)>

    Public Property Code() As String

End Class

The attribute MaxLength is very important for unique index of string type

Run cmd: update-database -verbose

after run cmd: add-migration 1

in the generated file

Public Partial Class _1
    Inherits DbMigration

    Public Overrides Sub Up()
        CreateIndex("dbo.Sample", "Code", unique:=True, name:="IX_Sample_Code")
    End Sub

    Public Overrides Sub Down()
        'DropIndex if you need it
    End Sub

End Class