Entity Framework 5: Extracting Schema (be careful)

Entity Framework 5 (EF5) Code First is a fantastic addition when you are designing the model. EF5 Code First supports the following options for creating a database:

1. drop and create the database, 
2. drop and create database if model changes or 
3. use an existing database

This is great for when developing the application. However I think dropping and creating databases in production environment is something that any sane person will discourage  Normally prior to executing a script (especially creating a database) in production, it has to be reviewed and validated. So, how can you extract the schema SQL from EF5 Code First model.

How to

Lets create a very simple data context using EF5 Code First. See the following.

    /// <summary>
    /// This class represents a simple context.
    /// </summary>
    public class DataContext : DbContext
    {
        public DataContext(string connectionString)
            : base(connectionString)
        {
        }   
 
        public DbSet<School> Schools { get; set; }
    }

    /// <summary>
    /// This class represents a School.
    /// </summary>
    public class School
    {
        public int Id { get; set; }
 
        public string Name { get; set; }
    }

I have created a console application to put everything together.

class Program
    {
        static void Main(string[] args)
        {
            // Database strategy
            Database.SetInitializer<DataContext>(new DropCreateDatabaseIfModelChanges<DataContext>()
);
 
            // ConnectionString
            var connString = "Server=.; Database=EFGenSchema; Trusted_Connection=yes";
 
            var dataContext = new DataContext(connString);
 
            var objectContext = (IObjectContextAdapter)dataContext;
 
            var scripts = objectContext.ObjectContext.CreateDatabaseScript();
 
            Console.WriteLine(scripts);
 
            Console.ReadKey();
        }
    }

I have highlighted the critical parts of the code. The "DataContext" (which is derived from DbContext) is cast to "IObjectContextAdapter". Then we get a handle of the old "ObjectContext" (i.e. from EF4). Thereafter the "CreateDatabaseScript()" method is called. Following is what you see in the console window.

Well... perfect!, you might say... But wait!!!

Got-ya

Initially I thought EF5 inspects the classes registered in the data context and then generates the appropriate SQL. I was wrong. 

Open the SQL Server Profiler and then re-execute the above code to generate the schema. Following is what you will see.

Wow!, EF5 is creating the database and doing its work. All I asked was to create the SQL script. 

To be honest, this was really puzzling to me. The API is clearly named "CreateDatabaseScript()" and there is nothing to indicate that as a side-effect, the database is created. I am bit surprised why this behavior is not documented.

Now instead of using the "Drop Create Database when model changes" you can set the database initialisation strategy to "null". See below:

            // Database strategy
            Database.SetInitializer<DataContext>(null);

If you now execute the above schema generation code (with database initilisation strategy set to null), you will not see any attempts by EF5 make a connection with the database server in SQL Server Profiler.

Therefore if there is a requirement to extract the schema from EF5 Code First model, make sure to set the correct database initialisation strategy.