EF Migrations series
- Using Entity Framework to Create a Database
- Adding Indexes with EF Migrations
- Updating a Table with EF Migrations
- Indexes in Code-Based EF Migrations
- EF Migrations and a Merge Conflict
- Prevent EF Migrations from Creating or Changing the Database
- EF Code First Change Tracking
- EF Migrations Command Reference
- EF Code First Navigation Properties and Foreign Keys
- Update-Database MSI Custom Action
In the Adding Indexes with EF Migrations post I showed how to add indexes to a database created by EF Code First with a separate migration step. When using code-based migrations to add a table, there is another, better way to add indexes, if it is done together with the table. If you’ve read my Type Safe SelectList Factory post you know my feelings about code that contains class or property names as strings. I was considering writing a similar post about indexes in EF Migrations until I found out that there is a built in index creation, using lambdas.
First I’ll scaffold up an ordinary code-based migration.
PM> Add-Migration People
Scaffolding migration 'People'.
The Designer Code for this migration file includes a snapshot of
your current Code First model. This snapshot is used to calculate
the changes to your model when you scaffold the next migration. If
you make additional changes to your model that you want to include
in this migration, then you can re-scaffold it by running
'Add-Migration 201202232002181_People' again. |
The file added contains the table creation code.
namespace TestLib.Migrations { using System.Data.Entity.Migrations; public partial class People : DbMigration { public override void Up() { CreateTable( "People", c => new { PersonId = c.Int(nullable: false, identity: true), BirthYear = c.Int(nullable: false), }) .PrimaryKey(t => t.PersonId); } public override void Down() { DropTable("People"); } } } |
To add an index, just add a call to Index() after the call to PrimaryKey().
public override void Up() { CreateTable( "People", c => new { PersonId = c.Int(nullable: false, identity: true), BirthYear = c.Int(nullable: false), }) .PrimaryKey(t => t.PersonId) .Index(p => p.BirthYear); } |
To add an index containing multiple columns, create an anonymous type containing those columns.
.Index(p => new { p.BirthYear, p.PersonId }); |
When adding the indexes together with the table, this is a better way than a separate call to CreateIndex(). The fluent syntax method to create an index is completely type safe which is always good. I still can’t understand why functions like DbMigration.CreateIndex() only accepts strings and not lambdas to select columns.
What must I write in the Down method to drop this Index?
You don’t have to do anything separate for the index. It is enough to drop the table with
DropTable(...). When the table is removed, any associated indexes are automatically removed too.Thx for your reply. In my case I need the posibillity to switch between releases, therefor I need a proper Rollback (Down) method :-)
If you want to drop just the index, without dropping the table, you can use the
DropIndexmethod.Before you do that, you should keep in mind that EF Migrations is designed to have the
UpandDownmethods being exact opposites of each other. If you create the table and the index together in theUpmethod, both should be removed in theDownmethod.Okay, that’s a clear answer. Then I have to use the non-typesafe methods Create- and DropIndex. That’s a pitty… Thanks again for your replies.