Entity Framework 4.1 Custom Database Initializer strategy

Question

I would like to implement a custom database initialization strategy so that I can:

• generate the database if not exists
• if model change create only new tables
• if model change create only new fields without dropping the table and losing the data.

Thanks in advance

Answer 1

You need to implement IDatabaseInitializer interface.

Eg

public class MyInitializer : IDatabaseInitializer<MyDbContext>
{
    public void InitializeDatabase(MyDbContext context)
    {
        //your logic here
    }
}

And then set your initializer at your application startup

Database.SetInitializer<ProductCatalog>(new MyInitializer());

Here's an example

You will have to manually execute commands to alter the database.

context.ObjectContext.ExecuteStoreCommand("ALTER TABLE dbo.MyTable ADD NewColumn VARCHAR(20) NULL");

You can use a tool like SQL Compare to script changes.

Answer 2

There is a reason why this doesn't exist yet. It is very complex and moreover IDatabaseInitializer interface is not very prepared for such that (there is no way to make such initialization database agnostic). Your question is "too broad" to be answered to your satisfaction. With your reaction to @Eranga's correct answer you simply expect that somebody will tell you step by step how to do that but we will not - that would mean we will write the initializer for you.

What you need to do what you want?

• You must have very good knowledge of SQL Server. You must know how does SQL server store information about database, tables, columns and relations = you must understand sys views and you must know how to query them to get data about current database structure.
• You must have very good knowledge of EF. You must know how does EF store mapping information. You must be able to explore metadata get information about expected tables, columns and relations.
• Once you have old database description and new database description you must be able to write a code which will correctly explore changes and create SQL DDL commands for changing your database. Even this look like the simplest part of the whole process this is actually the hardest one because there are many other internal rules in SQL server which cannot be violated by your commands. Sometimes you really need to drop table to make your changes and if you don't want to lose data you must first push them to temporary table and after recreating table you must push them back. Sometimes you are doing changes in constraints which can require temporarily turning constrains off, etc. There is good reason why tools which do this on SQL level (comparing two databases) are probably all commercial.

Even ADO.NET team doesn't implemented this and they will not implement it in the future. Instead they are working on something called migrations.

Edit:

That is true that ObjectContext can return you script for database creation - that is exactly what default initializers are using. But how it could help you? Are you going to parse that script to see what changed? Are you going to execute that script in another connection to use the same code as for current database to see its structure?

Yes you can create a new database, move data from the old database to a new one, delete the old one and rename a new one but that is the most stupid solution you can ever imagine and no database administrator will ever allow that. Even this solution still requires analysis of changes to create correct data transfer scripts.

Automatic upgrade is a wrong way. You should always prepare upgrade script manually with help of some tools, test it and after that execute it manually or as part of some installation script / package. You must also backup your database before you are going to do any changes.

Answer 3

The best way to achieve this is probably with migrations:

http://nuget.org/List/Packages/EntityFramework.SqlMigrations

Good blog posts here and here.