Entity Framework Code First And SQL Server DACPAC

Entity Framework Code First Migrations allows you to handle changes in your model (C#) by generating “migration scripts” for keeping the DB in sync with the code. It works very well. But I never used it in a project with more than 1 developer. I think we could run in different problems. And managing all the generated migration scripts requires discipline. There is maybe a solution which handles all this problems very well. But in this example here, I’m explaining a slightly different way for the migration scenario.

Idea:
While developing

• Every developer has its own development database.
• The EF configuration is set “auto migration”.
• The developer doesn’t create any migration scripts.

On Build Server

• Step 1: Server sets up the new database model.
• Step 2: Creating a new extract (DACPAC) with SqlPackage.exe.
• Step 3: Comparing the extract with the previous database model. (Writing a report)
• Step 4: Generating a migration SQL script. (Handle data loss manually.)
• Step 5: Publish migration script.

For a PoC, I ran all steps on my development machine. Here is the result:

The model:

public class Person
{
    [Key]
    public int Id { get; set; }
    [Required]
    public string Firstname { get; set; }
    [Required]
    public string Lastname { get; set; }
}

The data context:
In DEBUG the EF uses the MigrateDatabaseToLatestVersion initializer. In all other cases
the ValidationInitializer (custom class) is being used, which only verifies if the current model is compatible with the database model.

public class DataContext : DbContext
{
    public DbSet People { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
#if DEBUG
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<DataContext, Configuration>());
#else
        Database.SetInitializer(new ValidationInitializer());
#endif
        base.OnModelCreating(modelBuilder);
    }
}

public class ValidationInitializer : IDatabaseInitializer
{
    public void InitializeDatabase(DataContext context)
    {
        if (!context.Database.CompatibleWithModel(false))
        {
            throw new ModelNotCompatibleException();
        }
    }
}

The code above creates/updates automatically the database structure.

class Program
{
    static void Main(string[] args)
    {
        var db = new DataContext();
        // Creates database structure if not exists.
        db.People.ToList();
    }
}

Now we change the person model. (New property “Fullname” and the properties “Firstname” and “Lastname” deleted.)

public class Person
{
    [Key]
    public int Id { get; set; }
    [Required]
    public string Fullname { get; set; }
}

I changed the connectionstring so that next time I execute the application a new database is being generated. (I need to this, because I run all steps on my development machine. I need two DBs for schema comparison.)

Now the following steps have to executed on the build server! (In a real/professional environment)

This step generates a DACPAC file based on the new database model.

SqlPackage.exe /Action:Extract /SourceConnectionString:"Data Source=.\;Initial Catalog=DataDb_Release_2; Integrated Security=SSPI;" /TargetFile:"E:\Schema_Release_2.dacpac"

The next step generates the deployment report. (The DB schema won’t be changed!)

SqlPackage.exe /Action:DeployReport /TargetConnectionString:"Data Source=.\;Initial Catalog=DataDb_Release_1; Integrated Security=SSPI;" /SourceFile:"E:\Schema_Release_2.dacpac" /OutputPath:"E:\Schema_Release_2.xml"

The generated XML file contains 3 data issues.

Now we create a migration SQL script:

SqlPackage.exe /Action:Script /TargetConnectionString:"Data Source=.\;Initial Catalog=DataDb_Release_1; Integrated Security=SSPI;" /SourceFile:"E:\Schema_Release_2.sql"

In this script, we can implement the handling for the 3 reported data issues. Then we can ran the script.

sqlcmd -S .\ -i "Schema_Release_2.sql"

Now we delete the MigrationHistory table in the migrated database! (You need to do this only the first time)

DROP TABLE dbo.__MigrationHistory;

This is it.

So… Now I change the connectionstring in my VS.NET project to the migrated database and I change the project context to “Release”.
EF will do only the model validation and NO “auto migration”. If model isn’t compatible with the DB structure, an exception will be thrown.

Caution: This is only a PoC. I’ve never used it in a real project.