EF5 Code First: Detect if it creates a database so I can run ALTER statements

Question

I am new with Entity Framework 5. Our team is using Code First workflow.

Before I'll start with my main question, let me first show you what I have tried ^{(the ultimate comment of all time :D)}.

public class MyDBContext : CDBContext
{
    public MyDBContext() : base(connString) { }

    public MyDBContext(string connStr) : base(connStr) { }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // removes some conventions
        modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
        // ........

        // model configurations which contains mappings
        modelBuilder.Configurations.Add(new AccountConfiguration());
        // ........

        // calls base OnModelCreating
        base.OnModelCreating(modelBuilder);
    }

    // list of all Entity
    public DbSet<Account> Account { get; set; }
}

MyDBContext is the class I have created that inherits from CBDContext that contains override methods and which also inherits from DBContext. One of the problems I have encountered is that entity framework doesn't handle field uniqueness. I have already read the article on Configuring/Mapping Properties and Types with the Fluent API on their site and I can't find any configuration to set a property into unique.

So what I did in order to set the field unique is to manually run several ALTER sql statements during creation,

using (MyDBContext _context = new MyDBContext(connString))
{
    if (_context.Database.CreateIfNotExists()) 
    {
        _context.Database.ExecuteSqlCommand("ALTER TABLE Account ADD CONSTRAINT UQ_Account_AccountNumber UNIQUE(AccountNumber)");
        _context.Database.ExecuteSqlCommand("ALTER TABLE Account ADD CONSTRAINT UQ_Account_GUID UNIQUE(GUID)");
        // .... more on this in the following lines ...
    }
}

My Questions:

Am I right that entity framework don't have any configuration or data annotations to set the field unique?
Is there a way to detect or know during runtime if EF creates a database or not so I can move or hide this statement if (_context.Database.CreateIfNotExists()) somewhere to an available method that can be overriden?

What I really want is to remove if (_context.Database.CreateIfNotExists()) from the using statemnt and put it somewhere else or inside MyDBContext so my code will look like this,

using (MyDBContext _context = new MyDBContext(connString))
{
    Account _acc = new Account()
    // ...Account properties ...

    _context.Account.Add(_acc);
    _context.SaveChanges();
}

Thanks.

If you don't use EF migrations (where you can define unique index), you can use custom [database initializer](http://stackoverflow.com/questions/5701608/unique-key-with-ef-code-first/5701702#5701702). — Ladislav Mrnka, Apr 18 '13 at 14:05

score 4 · Answer 1 · edited May 23 '17 at 12:04

You should take a look at Code First Migrations, more specific at the Data Motion / Custom SQL and later sections - this is might the way to achieve your desired result. Your migration class can look like this:

public partial class AddUniqueConstrains : DbMigration
{
    public override void Up()
    {
        Sql("ALTER TABLE Account ADD CONSTRAINT UQ_Account_AccountNumber UNIQUE(AccountNumber)");
        Sql("ALTER TABLE Account ADD CONSTRAINT UQ_Account_GUID UNIQUE(GUID)");
    }

    public override void Down()
    {
        Sql("ALTER TABLE Account DROP CONSTRAINT UQ_Account_AccountNumber UNIQUE");
        Sql("ALTER TABLE Account DROP CONSTRAINT UQ_Account_GUID");
    }
}

You can also explore other options described in answers to this question: Unique Constraint in Entity Framework Code First

Thanks for the answer but this is not the time to use Migrations. I am slowly taking steps on these things. But honestly, this is a good answer `:)` +1. — John Woo, Apr 18 '13 at 15:14

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

If you don't use (or cannot use) EF migrations you can use custom initializer as mentioned in this answer. The custom initializer will execute a Seed method after creating the database = only once when database doesn't exist. If you need to incrementally develop the database initializer itself will not help you (that is what migrations are for).

edited May 23 '17 at 11:57

Community

1
1

answered Apr 18 '13 at 15:09

Ladislav Mrnka

360,892
59
660
670

This is not the time for me to use Migrations. As i've mentioned on the chat I'm slowly understanding this and soon to migrate to EF migrations. Your answer is really what I am looking for. Thanks. – John Woo Apr 18 '13 at 15:16

EF5 Code First: Detect if it creates a database so I can run ALTER statements

2 Answers2

Linked