Unique constraint with EFCodeFirst and SqlCe4

Question

I'm writing an ASP.net MVC3 application using Entity Framework Code First and SqlCe4.

I have been designing several models, and found one that is turning out to be interesting. This is what I have so far:

public class Preference
    {
        public int PreferenceId { get; set; }

        [Required]
        public int StudentId { get; set; }
        public virtual Student Student { get; set; }

        [Required]
        public int PresentationId { get; set; }
        public virtual Presentation Presentation { get; set; }

        [Required]
        public int Rank { get; set; }
    }

I however, need a unique constraint or index, because for a particular student, I want them to have to have a list of preferences, but the PresentationId needs to be unique for each student. Is there a way to do this via Code First or some validation attribute?

This sounds like I'm going down the branch of a many to many relationship with the Preference object being an intermediary, to add the Rank property. However, I can't seem to find how to make sure that the values are unique. Is the best way really to manually just add a unique index to the database outside of EF?

score 3 · Answer 1 · answered Jul 18 '11 at 01:05

3

Currently(ie EF 4.1) EF does not have an Attribute or configuration mechanism to create unique indexes.

However if you are using Database Initializer you can create it manually

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

            context.ObjectContext.ExecuteStoreCommand("CREATE INDEX IX_Preference_PresentationId ON Preference ( PresentationId )");
        }
    }
}

Or execute it outside the Initializer.

answered Jul 18 '11 at 01:05

Eranga

32,181
5
97
96

I was currently using the built in initializer: `Database.SetInitializer(new DropCreateDatabaseIfModelChanges());` However, in trying to implement your solution, it fails to compile at the line containing ModelMatchesDatabase() – mandreko Jul 18 '11 at 02:06
1

This answer worked for me and is in my production version currently – Yablargo Jul 20 '11 at 12:44

score 1 · Answer 2 · answered Jun 07 '13 at 16:38

I'm not sure how long the feature has existed, but in EF5, you can use the CreateIndex and DropIndex method in your Up() and Down() methods. This allows you to create unique indexes, which function almost identically to unique constraints, in EF's own language.

public partial class UniqueIndexMigration
{
    public override void Up()
    {
        // Create new unique index
        this.CreateIndex("dbo.TableName", new[] { "Col1", "Col2", "Col3" }, true, "IX_TableName_Col1_Col2_Col3");
    }

    public override void Down()
    {
        // Drop unique index
        this.DropIndex("dbo.TableName", "IX_TableName_Col1_Col2_Col3");
    }
}

The true, seen above, is the parameter that specifies that it's a unique constraint. Sadly, these constraints are not honored when making further migrations later on (EF won't drop them for you if you change the underlying schema), but my hope is that by using the EF API, your code will get an upgrade for free when this feature is finally added.

score 0 · Answer 3 · edited May 23 '17 at 11:57

0

See answear that implements the Unique constraint with a Custom ValidationAttribute: https://stackoverflow.com/a/10566485/1133338

edited May 23 '17 at 11:57

Community

1
1

answered May 12 '12 at 22:09

Joao Leme

9,598
3
33
47

score 0 · Accepted Answer · answered Jul 18 '11 at 02:27

Going with what Eranga was saying, I ended up making it work similarly, but like this:

I used the following code in my DataContext class:

public class StudentRegistrationContext : DbContext
{
    public StudentRegistrationContext()
    {
        Database.SetInitializer(new StudentRegistrationDatabaseInitializer());
    }

    public DbSet<Student> Students { get; set; }
    public DbSet<Teacher> Teachers { get; set; }
    public DbSet<Presenter> Presenters { get; set; }
    public DbSet<Presentation> Presentations { get; set; }
}

In the Database Initializer class, I used the following:

public class StudentRegistrationDatabaseInitializer : DropCreateDatabaseIfModelChanges<StudentRegistrationContext>
{
    protected override void Seed(StudentRegistrationContext context)
    {
        base.Seed(context);
        context.Database.ExecuteSqlCommand("CREATE UNIQUE INDEX UX_Preferences_StudentId_PresentationId ON Preferences (StudentId, PresentationId)");
    }
}

Unique constraint with EFCodeFirst and SqlCe4

4 Answers4

Linked