Saturday, August 11, 2012

Entity Framework Migration with Model First

Entity Framework 4.3 Code First's Migration allows developers to generate database schema migration functionality as code rather than using t-sql to update the schema.  All the example I have found so far uses Power Shell command to generate migration classes and apply schema update through Power Shell command.  In the article I will show you how to have your program apply the migration classes automatically at run-time.  You will still need to generate the migration classes using command, but that's a good thing because you will want to review the changes first before letting your program update the schema.  As a bonus I will also show you how to use Migration feature with Model First approach through t4.

Assumptions:

  • You are using Entity Framework 4.5/5.0
  • You added Entity Framework to your project using NuGet
  • EF entities are used as database objects and nothing more
  • Fluent Api for entity configuration is used instead of attributes
  • You want to use Repository pattern (aka Table Module)
  • You have basic understanding of Entity Framework migrations feature by reading this blog
I am using fluent api for schema generation, through EntityTypeConfiguration, because it give us more flexibility to configure how the entity is translated to tables and there are things the attributes will not be able to do, such as specify precision for decimal datatype.  Complex types are supported in the latest version of templates.

Where to get the templates

You get get the templates either through NuGet (the preferred method) or github.

How to use the templates

Once you added the templates to your project through NuGet, you will need to run the custom tools manually when you update your model.

Under the hood

Context.tt

This template creates the DbContext subclass that contains the repositories for accessing table data and DbMigrationsConfiguration class.
For DbContext subclass, the generated constructor will initialize the repositories by passing the concrete DbSet objects to the repositories' constructors. See repository.tt section for more information on repositories. The subclass also overrides OnModelCreating method to use entity configurations and calls a partial method CustomOnModelCreating method that can be implemented to contain additional model creation logic.
The DbMigrationsConfiguration class is used for the Db Migration feature of Entity Framework v4.3.1 and later. The generated partial class contains instruction on how to create migration classes in the comments section.

Entity.tt

This template generates poco for the entities defined in the model.

ComplexType.tt

This template generates poco for the complex types defined in the model.

Enum.tt

Only apply to .Net Framework version 4.5 and up. Generate enums used in the model.

EntityTypeConfiguration.tt

This template generates EntityTypeConfiguration classes for the entities that configures the model using Fluent Api. This template will handles cases that weren't well supported using by other templates such as row version or decimal type.

ComplexTypeConfiguration.tt

This template generates ComplexTypeConfiguration classes for the entities that configures the model using Fluent Api. This template will handles cases that weren't well supported using by other templates such as row version or decimal type.

Repository.tt

This template generates classes that implements .Net version of repository pattern (aka Table Data Gateway pattern from Folwer). The repository inheriates IDbSet and is a wrapper for DbSet class. The repository adds additional method based on the entity such as if the entity contains RowVersion it will create GetMaxRowVersion for the repository.

Migration

Once the project is setup with t4, you will find DbMigrationsConfiguration.partial.cs under project root folder.  Open the file and you will see the remarks about how to use this class.  What you want to do is run the Add-Migration command in Package Manager Console.  The DbMigrationsConfiguration class is setup for you to generate all the migration classes under Migrations folder under the {parent}.Migrations namespace.

For example to create the initial schema run the following command, assuming the name of your project is Data and you have a local database server running.

Add-Migration InitialCreate -ProjectName "Data" -StartUpProjectName "Data" -ConfigurationTypeName "Data.Migrations.DbMigrationsConfiguration" -ConnectionString "Server=localhost;Database=EF;Trusted_Connection=True;" -ConnectionProviderName "System.Data.SqlClient"

After you have created migrations classes, you just need to call the static method DbMigrationsConfiguration.UpdateSchema(connectionString).  The method will
  1. Create the database if not exist
  2. Update the schema to the latest version
Depend on your upgrade strategy your might call the method on Program.Main or Application_Start, or you might write an utility program that will update all your databases.

4 comments:

Thanawut said...

Hi,

I tried to migrate data by using your guide, but I have found the exception as the below.


PM > Add-Migration InitialCreate -ProjectName "DataAccessTest" -StartUpProjectName "DataAccessTest" -ConfigurationTypeName "DataAccessTest.Model1ContainerMigrationsConfiguration" -ConnectionString "Data Source=localhost;Initial Catalog=TestDB;Integrated Security=True" -ConnectionProviderName "System.Data.SqlClient"
System.InvalidOperationException: Can not override the connection for this context with a standard DbConnection because the original connection was an EntityConnection.
at System.Data.Entity.Internal.LazyInternalContext.OverrideConnection(IInternalConnection connection)
at System.Data.Entity.Infrastructure.DbContextInfo.ConfigureContext(DbContext context)
at System.Data.Entity.Infrastructure.DbContextInfo..ctor(Type contextType, DbProviderInfo modelProviderInfo, AppConfig config, DbConnectionInfo connectionInfo)
at System.Data.Entity.Infrastructure.DbContextInfo..ctor(Type contextType, DbConnectionInfo connectionInfo)
at System.Data.Entity.Migrations.DbMigrator..ctor(DbMigrationsConfiguration configuration, DbContext usersContext)
at System.Data.Entity.Migrations.DbMigrator..ctor(DbMigrationsConfiguration configuration)
at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.GetMigrator()
at System.Data.Entity.Migrations.Design.ToolingFacade.GetPendingMigrationsRunner.RunCore()
at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run()

Peijen said...

Hi Thanawut,

Base on the error message, I am guessing you will need to use Entity Framework connection string instead. It should looks something like

<add name="StatisticsContainer" connectionString="metadata=res://*/Statistics.csdl|res://*/Statistics.ssdl|res://*/Statistics.msl;provider=System.Data.SqlClient;provider connection string='data source=.;initial catalog=Statistics;integrated security=True;multipleactiveresultsets=True;App=EntityFramework'" providerName="System.Data.EntityClient" />

Anonymous said...

a have this error using Enable-Migration over my DB Context that throw this error :

Creating a DbModelBuilder or writing the EDMX from a DbContext created using Database First or Model First is not supported. EDMX can only be obtained from a Code First DbContext created without using an existing DbCompiledModel.

computmas said...

Hi Thanawut. I'm hearing problems, I found the exception:
The migrations configuration type 'Data.Migrations.DbMigrationsConfiguration' was not be found in the assembly 'img_site'.