Unique Constraint in Entity Framework Code First

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.

Unique Constraint in Entity Framework Code First

Related

Recent Posts