• Article

January 2014

Volume 29 Number 1

Data Points : Code First Goodies in Entity Framework 6

Julie Lerman

Julie LermanIn my December 2013 article, “Entity Framework 6: The Ninja Edition” (msdn.microsoft.com/magazine/dn532202), I described many of the new features in Entity Framework 6 (EF6). I wasn’t able to delve into every feature, though, so this month I’ll drill into some of the EF6 enhancements specific to Code First. Two of the features I’ll discuss are relevant to Code First mappings, and the others relate to Code First migrations.

Load Many Fluent API Mappings at Once

There are two ways to specify Fluent mappings (from your classes to your database) for a model. One is directly in the OnModel­Creating method of the DbContext class, like this:

modelBuilder.Entity<Casino>()
  .Property(c=>c.Name).IsRequired().HasMaxLength(200);
modelBuilder.Entity<PokerTable>()
  .Property(c => c.SerialNo).HasColumnName("SerialNumber");

When you have a lot of mappings, you can organize them by type into individual EntityTypeConfiguration classes, which you then add into the model builder using code like this:

modelBuilder.Configurations.Add(new CasinoConfiguration());
modelBuilder.Configurations.Add(new PokerTableConfiguration());

However, if you have a lot of mappings for a lot of entities, you can end up with many repetitive modelBuilder.Configurations.Add methods in OnModelCreating. To eliminate this drudgery, you can now load all EntityTypeConfigurations from a given assembly with a single method. Here I use the new AddFromAssembly method to load configurations that are specified in the executing assembly for the running application:

modelBuilder.Configurations
  .AddFromAssembly(Assembly.GetExecutingAssembly())

A nice feature of this method is that it isn’t restricted by the scope of the configurations it will load. The custom EntityTypeConfiguration classes can even be marked private and the method will find them. Moreover, AddFromAssembly also comprehends inheritance hierarchies in EntityTypeConfigurations.

AddFromAssembly is one of a number of community contributions from Unai Zorrilla. See Zorrilla’s blog post, “EF6: Setting Configurations Automatically,” at bit.ly/16OBuJ5 for more details—and leave him a note of thanks while you’re there.

Define Your Own Default Schema

In my March 2013 Data Points column, “Playing with the EF6 Alpha” (msdn.microsoft.com/magazine/jj991973), I talked a bit about schema support for Code First. One new feature is a mapping you can configure in OnModelCreating: HasDefaultSchema. This lets you specify the database schema for all of the tables a context maps to, rather than using the EF default of dbo. In the “Entity Framework 6: The Ninja Edition” article, I executed some raw SQL in the discussion about DbTransactions:

("Update Casino.Casinos set rating= " + (int) Casino.Rating)

You may have noticed the Casino schema I specified in the SQL. The reason I have a schema named Casino is because I specified it  in the OnModelCreating method of my DbContext (CasinoSlotsModel):

modelBuilder.HasDefaultSchema("Casino");

I also talked about the new support in EF6 for Code First migrations that are being run against a database that has different schemas in it. Because that hasn’t changed since EF6 Alpha, I’ll let you read about it in my earlier column.

Migrations Scripts to Rebuild the Database from Any Point

One of the new features for Code First migrations listed in the specs (and in every article that simply reiterates the specs) is “idempotent migrations scripts.” Now, you may be someone with a comp sci degree under your belt, or perhaps you’re a DBA. But I am neither and I had to look up the meaning of “idempotent.” According to Wikipedia, which references an IBM engineer (bit.ly/9MIrRK): “In computer science, the term ‘idempotent’ is used … to describe an operation that will produce the same results if executed once or multiple times.” I also had to look up how to pronounce it. It’s eye-dem-poe-tent.

In the database world, idempotent can be used to describe a SQL script that always has the same impact on a database regardless of its state. With Code First migrations, before running a migration, such a script will check to see if that migration has already been run. This feature is specific to the -script parameter of Update-Database.

EF has always provided the ability to create scripts that run through all of the migration steps from a particular starting point (source) and optionally to an explicit endpoint (target). This NuGet command evokes that behavior:

Update-Database -Script
  –SourceMigration:NameOfStartMigration
  –TargetMigration:NameOfEndMigrationThatIsntLatest

What’s new is that the generated script is much smarter now when you call the command in a particular way:

Update-Database -Script -SourceMigration $InitialDatabase

This also happens to work if you replace $InitialDatabase with 0, though there’s no guarantee this alternative will be supported in future versions.

In response, the script starts with the initial migration and carries through to the latest migration. This is why the syntax does not explicitly supply the names of the target or source migrations.

But with this particular command, EF6 adds logic to the script that checks to see which migrations have already been applied before executing the SQL for a particular migration. Here’s an example of code you’ll see in the script:

IF @CurrentMigration < '201310311821192_AddedSomeNewPropertyToCasino'
BEGIN
  ALTER TABLE [Casino].[Casinos] ADD [AgainSomeNewProperty] [nvarchar](4000)
  INSERT [Casino].[__MigrationHistory]([MigrationId], [ContextKey], [Model], [ProductVersion])
  VALUES (N'201310311821192_AddedSomeNewPropertyToCasino',
    N'CasinoModel.Migrations.Configuration', HugeBinaryValue , 
    N'6.1.0-alpha1-21011')
END

The code checks in the ­_MigrationHistory table to see if the AddedSomeNewPropertyToCasino script has been run on the database yet. If it has, the SQL from that migration won’t be exe­cuted. Prior to EF6, the script would just run the SQL without checking to see if it had already been run.

Provider-Friendly Migration History Tables

EF6 lets you customize how the _MigrationHistory table is defined using a feature called the Customizable Migrations History Table. This is important if you’re using third-party data providers that have different requirements than the defaults. Figure 1 shows the default schema of the table.

Here’s an example of how this can be useful. On CodePlex, a developer noted that because each char in the two PKs can be greater than 1 byte, he was getting an error that the length of the compound key created from MigrationId and ContextKey exceeded the allowed key length for a MySQL table: 767 bytes (bit.ly/18rw1BX). To resolve the issue, the MySQL team is using the HistoryContext internally to alter the lengths of the two key columns while working on the EF6 version of MySQL Connector for ADO.NET (bit.ly/7uYw2a).

Notice in Figure 1 that the __MigrationHistory table gets the same schema I defined for the context using HasSchema: Casino. You may have conventions that say non-data tables should be used by a schema with limited permissions, so you might want to change the schema name of that table. You can do that with the HistoryContext, for example to specify that the “admin” schema be used.

Default Schema for the __MigrationHistory Table
Figure 1 Default Schema for the __MigrationHistory Table

HistoryContext derives from DbContext, so the code should be somewhat familiar if you’ve worked with DbContext and Code First in the past. Figure 2 shows a HistoryContext class I defined to specify the admin schema.

Figure 2 Custom HistoryContext to Redefine __MigrationHistory Table

public class CustomHistoryContext : HistoryContext
{
  public CustomHistoryContext
   (DbConnection dbConnection, string defaultSchema)
     : base(dbConnection, defaultSchema)
  {
  }
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    base.OnModelCreating(modelBuilder);
    modelBuilder.Entity<HistoryRow>()
      .ToTable("__MigrationHistory", "admin");
  }
}

You can also use familiar API calls like Property().HasColumnType, HasMaxLength or HasColumnName. For example, if you need to change the length of the ContextKey, you can do this:

modelBuilder.Entity<HistoryRow>()
  .Property(h => h.ContextKey).HasMaxLength(255);

If you’ve already read last month’s arti­cle, you should be familiar with EF6 DbConfiguration. This is what you use to let your model know about the CustomHistory­Context file. In your custom DbConfiguration’s constructor, you need to specify the HistoryContext to be used. Here I set the context for the SQL Server provider to use CustomHistoryContext:

SetHistoryContext(
  SqlProviderServices.ProviderInvariantName,
     (connection, defaultSchema) =>
  new CustomHistoryContext(connection,
     defaultSchema));

Database initialization and migrations functionality will see this additional context and construct SQL accordingly. The table in Figure 3 was created using the custom HistoryContext to change the schema name of the __MigrationHistory table to admin. (I didn’t include the sample code for changing the column length.)

Customized __MigrationHistory Table
Figure 3 Customized __MigrationHistory Table

HistoryContext is a powerful feature but it needs to be used carefully. Hopefully the database provider you’re using will have already used it to specify a __MigrationHistory table that’s relevant to the target database, and you won’t even need to think about this. Otherwise, I recommend checking the MSDN document on this feature and heeding its guidance (bit.ly/16eK2pD).

Create Custom Migration Operations

If you’ve used migrations before—not automatically but by explicitly creating and executing migrations from the Package Manager Console window—you may have explored the migration files created by add-migration. If so, you might have discovered that Code First migrations have a strongly typed API to describe each change to make to the database schema: System.Data.Entity.Migrations.DbMigration.

Figure 4 shows an example of the Create­Table method that sets a number of attributes.

Figure 4 DbMigrations.CreateTable Method

CreateTable(
  Casino.SlotMachines",
  c => new
    {
      Id = c.Int(nullable: false, identity: true),
      SlotMachineType = c.Int(nullable: false),
      SerialNumber = c.String(maxLength: 4000),
      HotelId = c.Int(nullable: false),
      DateInService = c.DateTime(nullable: false),
      HasQuietMode = c.Boolean(nullable: false),
      LastMaintenance = c.DateTime(nullable: false),
      Casino_Id = c.Int(),
    })
  .PrimaryKey(t => t.Id)
  .ForeignKey("Casino.Casinos", t => t.Casino_Id)
  .Index(t => t.Casino_Id);

Providers then translate those API calls into database-specific SQL.

There are methods to create tables and indexes, create or alter properties, drop objects and more. It’s a fairly rich API, as you can see from the possibilities listed in Figure 5—which include the ability to simply execute some SQL directly. But, in some cases, it may not be rich enough for your needs. For example, there’s no method for creating database views, specifying permissions or plenty other operations.

DbMigrations Database Schema Operations
Figure 5 DbMigrations Database Schema Operations

Once again, the community came to the rescue. In EF6, you now have the ability to create custom migrations operations you can call by customizing the migration classes generated by add-migration. This is thanks to another developer in the CodePlex community, Iñaki Elcoro, aka iceclow.

To create your own operation, you have to perform a few steps. I’ll show the core of each step. You can see the full code and how the steps are organized in the download for this article.

  • Define the operation. Here I’ve defined a CreateView­Operation, as listed in Figure 6.
  • Create an extension method to point to the operation. This makes it simple to call from DbMigration:
public static void CreateView(this DbMigration migration,
  string viewName, string viewqueryString)
{
  ((IDbMigration) migration)
    .AddOperation(new CreateViewOperation(viewName,
       viewqueryString));
}
  • Define the SQL for the operation in the Generate method of a custom SqlServerMigrationSqlGenerator class, as shown in Figure 7.
  • Tell the DbConfiguration class to use the custom SqlServerMigrationSqlGenerator class:
SetMigrationSqlGenerator("System.Data.SqlClient",
  () => new CustomSqlServerMigrationSqlGenerator());

Figure 6 A Custom Migration Operation for Creating a Database View

public class CreateViewOperation : MigrationOperation
{
  public CreateViewOperation(string viewName, string viewQueryString)
    : base(null)
  {
    ViewName = viewName;
    ViewString = viewQueryString;
  }
  public string ViewName { get; private set; }
  public string ViewString { get; private set; }
  public override bool IsDestructiveChange
  {
    get { return false; }
  }
}

Figure 7 Custom SqlServerMigrationSqlGenerator Class

public class CustomSqlServerMigrationSqlGenerator
  : SqlServerMigrationSqlGenerator
{
  protected override void Generate(MigrationOperation migrationOperation)
  {
    var operation = migrationOperation as CreateViewOperation;
    if (operation != null)
    {
      using (IndentedTextWriter writer = Writer())
      {
        writer.WriteLine("CREATE VIEW {0} AS {1} ; ",
                          operation.ViewName,
                          operation.ViewString);
        Statement(writer);
      }
    }
  }
}

With all of this in place, you can now use the new operation in a migration file and Update-Database will know what to do with it. Figure 8 shows the CreateView operation in use, and provides a reminder that you’ll also need to create an operation to remove the view, which would be called by the Down method if you need to unwind this migration.

Figure 8 Using the New CreateView Operation

public partial class AddView : DbMigration
  {
    public override void Up()
    {
      this.CreateView("dbo.CasinosWithOver100SlotMachines",
                      @"SELECT  *
                        FROM    Casino.Casinos
                        WHERE  Id IN  (SELECT   CasinoId AS Id
                        FROM     Casino.SlotMachines
                        GROUP BY CasinoId
                        HAVING COUNT(CasinoId)>=100)");
    }
    public override void Down()
    {
      this.RemoveView("dbo.CasinosWithOver100SlotMachines");
    }
  }

After I call Update-Database, you can see the new view in my database in Figure 9.

The Newly Created View Generated by Update-Database
Figure 9 The Newly Created View Generated by Update-Database

Code First Continues to Evolve

With the core features of Code First in place, Microsoft and developers from the community took the opportunity to start putting some polish on EF6, with features that now benefit from more flexibility. But it doesn’t stop with the release of EF6. If you filter the CodePlex work items for versions beyond 6.0.1 (at bit.ly/1dA0LZf), you can see that even more polish is being added to future releases of EF6 and Code First. These items are in various states. Perhaps you’ll find one on which you’d like to work.

Julie Lerman is a Microsoft MVP, .NET mentor and consultant who lives in the hills of Vermont. You can find her presenting on data access and other Microsoft .NET topics at user groups and conferences around the world. She blogs at thedatafarm.com/blog and is the author of “Programming Entity Framework” (2010) as well as a Code First edition (2011) and a DbContext edition (2012), all from O’Reilly Media. Follow her on Twitter at twitter.com/julielerman and see her Pluralsight courses at juliel.me/PS-Videos.

Thanks to the following technical expert for reviewing this article: Rowan Miller (Microsoft)