Download source - 1.7 MB

Introduction

Migration is really a good way to work with entity framework code first. It enables the database to hold information like how many migrations have been made and when. Plus in migration files, we can find out what changes were made. But sometimes, it is little bit tricky to configure.

Here, we will see how we can handle database scheme changes with normal SQL scripts, without migration in Entity Framework code first.

Background

Let’s say we already have a code first database like:

model:

public class Customer
{
    public long Id { get; set; }
    public string Name { get; set; }        
}

models configuration:

internal class CustomerConfiguration: EntityTypeConfiguration<Customer>
{
    public CustomerConfiguration()
    {
        ToTable("Customer");

        HasKey(x => x.Id);
        Property(x => x.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        Property(x => x.Name)
            .IsRequired();
    }
}

context:

public class CmsContext : DbContext
{
	public DbSet<Customer> Customers { get; set; }

	static CmsContext()
	{
		//check connection string
		string connectionStringOld = ConfigurationManager.ConnectionStrings["DbCms"].ConnectionString;
		string connectionStringUpdated = 
              ConfigurationManager.ConnectionStrings["DbCmsUpdated"].ConnectionString;
		Database.SetInitializer(new CmsContextInitializer());
	}

	public CmsContext()
		: base(nameOrConnectionString: "DbCms")             //connection string old database
		//: base(nameOrConnectionString: "DbCmsUpdated")    //connection string updated database
	{
	}

	protected override void OnModelCreating(DbModelBuilder modelBuilder)
	{
		modelBuilder.Configurations.Add(new CustomerConfiguration());
	}
}

contexts initializer:

internal class CmsContextInitializer : CreateDatabaseIfNotExists<CmsContext>
{
    protected override void Seed(CmsContext context)
    {
        //add seeds
    }
}

After creating the database, we will have a table with the name __MigrationHistory.

Here is the clue, our solution is inside the Binary data of Model column.

Let’s say a live project is already currently using this database. And the Customer table is full of data. But a new requirement has been issued which forces us to add a new column to the Customer Table.

Make Change in Database Schema

Add a new property at Customer model:

public class Customer
{
    public long Id { get; set; }
    public string Name { get; set; } 
       
    /*added a new field to table*/
    public bool IsActive { get; set; }
}

Rebuild the project and create a new database in the local machine. It will also create __MigrationHistory table.

But there is really some difference between the Binary data of the model column of old database and currently created database.

Update the Old Database

Let’s try to update old database and use the newly created DLLs with update context to the old project.

Try1: Failed

To change the structure of the old Customer table and to maintain old data, we are using some SQL script like:

/*adds the column as not required*/
ALTER TABLE Customer
    ADD IsActive BIT NULL;
GO
/*updates old rows, assign value to newly added column*/
UPDATE Customer
    SET IsActive = 1;
GO
/*make the newly added column as required*/
ALTER TABLE Customer
    ALTER COLUMN IsActive BIT NOT NULL;

Now if we try to run the project, it would throw an error, pointing the current context and the database doesn’t match.

Try2: Success

Now let’s get the values of ContextKey, Model and ProductVersion from the __MigrationHistory table of currently created database, and add them to old database’s __MigrationHistory table.

INSERT [dbo].[__MigrationHistory] ([ContextKey], [Model], [ProductVersion])
VALUES (N'201407292021367_InitialCreate', N'Db.Contexts.CmsContext', 
0x1F8B0800000000000400CD57DB6EDB46107D2FD07F20F6A9051CAE9D14456A50091CC92E8446B6113A795F92237
AD1BDB0BB4B43FAB63EF493FA0B9DE55DA424CB4D5014020471399733676EABBFFFFC2B7ABF9122780263B9563372
119E930054AA33AEF21929DDFAD55BF2FEDDF7DF45D799DC045F5AB9375E0E35959D9147E78A4B4A6DFA0892D950F
2D468ABD72E4CB5A42CD3F4F5F9F92FF4E282029A20682B08A24FA5725C42F5808F73AD52285CC9C44A67206C738E
6FE2CA6A70CB24D882A530238B244471071B67497025384308318835099852DA3187002F3F5B889DD12A8F0B3C60E
2615B00CAAD99B0D000BFECC54F8DE1FCB58F81F68AADA9B4B44ECB171ABC78D39042C7EAFF8A5AD29186B45D23BD
6EEBA3AEA89B9179E5020C09C6CE2EE7C278C119A9B80FEB0C84ADC259501D9C75B9C712F19FB3605E0A571A98292
89D6128715F2682A7BFC1F641FF0E6AA64A2186A01016BEDB39C0A37BA30B306EFB09D60DD4654602BAAB47C78A9D
DA40A70E62A9DCCF3F91E0169DB3444097F341C0B1D3067E05058639C8EE99736094B701156B13EF235FFEBBF5864
5868D428215DB7C0495BB4764916D4870C33790B5270D82CF8A635FA1923325EC4178DCEBD25EA58E3F759E3F682D
80A967EC44B4AF84697DF83E621C79688B44DAA6B5F69409B6545329B60961176F6D320637AA38ECD21E43DDAA615
F8DFBC076B0FA3140EB39D0CE0B7A6060442B56149890C100694E82B89E1EF357F1CBBB4BD636686AF7345987B6F3
84E5C57218BD45D788F4861BEB16CCB184F954CD333915EB937080E0D6D198E7716BF5B4B71AFE77AD3598A27D364
6067AF66E302089DD51C5061D90C15899A856B39B0966F674EA5C8B52AA43DD7E4CBBEEBDA17E7D72BA85BE8F7650
74A7534B111DB130E6994E881E4DAF71E68ED5FC58A4F3DED5FEA8C6A3A6DE9EDF9C9302AC454880143DF1CC175FB
CB50E64E805C2F80F31171CE3ED05564CF13558570F7882FDF176B483FF3FFB905A9B891397E27FBEA5129E734FEC
B3ABE8855B62B89BD41333E923333F48B6F9F1ABF74DC2DDB7DD35936978DA3A39BA4DEA829E912CD188BB46FAD5B
B66DA5F111DDE5FA305589EF726FC6D5641EA0BB737DACA2CD55AB74C63704344ADC828112B702C438EAE8CE36B96
3A7C9D82B5D5B5E30B13258A5CCB04B2A5BA2B5D51BA2B6B412662E71A13D1E3FEAB85BA8B39BA2BFC93FD1621204
C8E21C09DFA50729175B86FF614D10113BE5C9A3E415478ED4273F9B6B374ABD589861AFA165080F25DF600B21068
CCDEA998F9727F3936BC197D849CA5DB764C1E36F27C2276698F169CE58649DBD8E8F5FD7F32EAFF94BDFB07BD221
534C60D0000, N'6.1.1-30610')

If you are facing a problem like, how would I get the values form the __MigrationHistory table of currently created database, to insert them into the old one. You can try this or can Google for a little time to get a suitable answer.

http://www.databasejournal.com/features/mssql/article.php/3852881/Scripting-data-out-of-a-table-in-SQL-Server-2008.htm

After adding the new row to the __MigrationHistory table of the old database, it would look like:

Now if we try to run the project, it would work just fine with the database change.

What else can we do rather than adding row?

We can just update the Model field with the binary data from the currently created database.
Or just drop the __MigrationHistory from the old database, and recreate it with __MigrationHistory and its data from the currently created database.

But I prefer adding, because it would let us know how many changes took place in the database.

Limitations

Find the example solution of Visual Studio 2010 with framework 4 projects, in the attachment. During demonstration, it would create two databases (CMS, CMSUpdated) on the local machine. You change it for the app.config file of the console project (which is Set as StartUp Project).

Entity Framework Code First Migrations Alternative