21

I am doing entity framework (v 5) code first migrations, on an MVC 4 application. I would like to add a unique constraint at the database level.

I know this can be done when creating the table, but I already have a table. http://msdn.microsoft.com/en-US/data/jj591621

I have tried the following, marked as answer answer: Unique constraint with EFCodeFirst and SqlCe4

My database context differes slightly, I supply the connection name is as follows

public AppDatabaseContext() : base("MyConnectionDBContext")

When I use the Package Management Console to update the database, the overridden seed method is not called:

protected override void Seed(AppDatabaseContext context)

I have also tried the following: http://romiller.com/2010/07/31/ef-ctp4-tips-tricks-running-additional-ddl/

I did not use a nested class, this is because it seemed as if I had to registere the initializer via the app.config. I could not get it working while initializing it in code. The InitializeDatabase is called, but the following condition is never true:

(!context.Database.Exists() || !context.Database.ModelMatchesDatabase())

This is because this happens after the migrations have been run...

I also tried this at one stage: Unique Constraint in Entity Framework Code First, it was the same problem as before, this condition was never returning true.

Ideally, I would like to include some standard SQL in my migration file. Is there a way to do that? If not, where can I see how to achieve this with using code first migrations?

Thanks!

UPDATE:

Is there any reason why I can't use the SQL function? 

 public override void Up()
 {
        AddColumn("Posts", "Abstract", c => c.String());

        Sql("UPDATE Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL");
 }

Obviously using the correct SQL...

Community
  • 1
  • 1
Daryn
  • 3,394
  • 5
  • 30
  • 41

3 Answers3

33

With code first migrations, I've just used this in the Up() method to add a unique index on a single column:

CreateIndex(table: "Organisations", 
            column: "Name", 
            unique: true, // unique index
            name: "MyIndex");

...and then in the Down() method:

DropIndex(table: "Organisations", 
          name: "MyIndex");

Is that what you're after?

MarkG
  • 1,859
  • 1
  • 19
  • 21
9

Since EF 6.1 you can now do this in your model with either

an attribute

 [Index("IX_UniqueName", IsUnique = true)]
 public string Name {get;set;}

or fluent

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

The fluent method isn't perfect as its crazy verbose 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/

undefined
  • 33,537
  • 22
  • 129
  • 198
6

You can just scaffold a new migration using the Add-Migration command from your package manager console. Once the empty migration is created, in the Up method, you can use the CreateIndex method of the DbMigration class to create your new index. It would look like something like this: 

CreateIndex("dbo.Accounts", "AccessKey", unique: true);
Ameen
  • 2,576
  • 1
  • 14
  • 17