EF6 Code First Adding Indexes

Code first is a real great way of developing your data model with Entity Framework. But to add some things into the migrations-files isn’t handy in my opinion. Because sometimes its necessary to remove or rebuild all migrations from scratch and you just remove all Migrations-files and the __MigrationHistory table in the database and use Add-Migration Initial again.

If you also wanted to add some manual indexes to your tables without changing the migrations files? Then just add following lines into the Configurations.cs in your Migrations folder:

    protected override void Seed(projectname.EntityFramework.BWBNGContext context)
    {
        context.Database.ExecuteSqlCommand("IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_indexname') CREATE INDEX <IX_indexname> ON .(, ) ");
    }

Example:

    protected override void Seed(projectname.EntityFramework.BWBNGContext context)
    {
        context.Database.ExecuteSqlCommand("IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_Regeling_Bwbid') CREATE INDEX IX_Regeling_Bwbid ON BWBNG.Regeling (BwbId) ");
    }

Of course make sure that you don’t remove the configurations files also when rebuilding migrations from scratch ;-D

An other way is to alter the migrations file with an SQL() command into the Up() and Down() methods within the specific migrations file.

    public override void Up()
    {
        Sql("IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_Regeling_Bwbid') CREATE INDEX IX_Regeling_Bwbid ON BWBNG.Regeling (BwbId)");
    }
    public override void Down()
    {
        Sql("IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_Regeling_Bwbid') DROP INDEX IX_Regeling_Bwbid");
    }

 

Have fun,

Martijn

Geef een antwoord

Het e-mailadres wordt niet gepubliceerd.

*
*
*

Back To Top