Entity Framework CTP 4.0 – Database Initialization

Posted: October 14, 2010 in .NET, ADO.NET Entity Framework, C#
Tags: ,

In the last post we discussed about the basic steps involved in working with the new Entity Framework CTP 4.0 Code First Approach.Now we will take a look at each of these steps in a bit more detail.We will start with database initialization.

The framework provides an interface IDatabaseInitializer and set of classes as shown below under the System.Data.Entity.Infrastructure namespace.

 

dbinit

The purpose of these classes is to seed the database with initialization data and decide on the database creation strategy.The following out of the box implementations are available :

  • AlwaysRecreateDatabase – Every time the context is initialized database will be recreated.
  • CreateDatabaseOnlyIfNotExists – Every time the context is initialized, database will be recreated if it does not exist.
  • RecreateDatabaseIfModelChanges – Every time the context is initialized database will be recreated, if there is mismatch between entity model and the database model.

The database initializer can be set using the SetInitializer method of the Database class.If nothing is specified it will use the CreateDatabaseOnlyIfNotExists class to initialize the database.

Let’s use the model for the Person table created in the last post to examine the behavior of each of these classes.

The following piece of code tries to insert two records in the Person table in a database named “test” and by default uses CreateDatabaseOnlyIfNotExists to initialize database.

static void Main(string[] args) 
{ 
     Database.DefaultConnectionFactory = new 
         SqlConnectionFactory("Server=SANKARSAN;Database=School;Trusted_Connection=True;"); 
     ModelBuilder builder = new ModelBuilder(); 
     builder.Configurations.Add(new EmployeeConfiguration()); 

     DbModel model = builder.CreateModel(); 
     using (var context = new SchoolDBContext(model,"test")) 
     { 
         Employee e = new Employee { 
             FirstName = "Sankarsan", 
             LastName = "Bose" , 
             HireDate = DateTime.Now, 
             EnrollmentDate=DateTime.Now}; 
         context.Employees.Add(e); 
         context.SaveChanges(); 
         e = new Employee 
         { 
             FirstName = "Sankarsan", 
             LastName = "Bose", 
             HireDate = DateTime.Now, 
             EnrollmentDate = DateTime.Now 
         }; 
         context.Employees.Add(e); 
         context.SaveChanges(); 
         Console.Read(); 
     } 
} 

Using the SQL Profiler we can see that following queries gets fired in starting with first call of the SaveChanges method.

  • Checks if database exists and if not found it creates the database and tables
SELECT Count(*) FROM sys.databases WHERE [name]=N'test'

create database [test]

create table [dbo].[EdmMetadata] ( 
    [Id] [int] not null identity, 
    [ModelHash] [nvarchar](4000) null, 
    primary key ([Id]) 
); 
create table [dbo].[Person] ( 
    [EnrollmentDate] [datetime] not null, 
    [FirstName] [nvarchar](4000) not null, 
    [HireDate] [datetime] not null, 
    [LastName] [nvarchar](4000) not null, 
    [PersonId] [int] not null identity, 
    primary key ([PersonId]) 
); 
  • The table EdmMetadata is table created by the framework to store the HashCode of the model.This is the first time database is created so it inserts the hash code.
exec sp_executesql N'insert [dbo].[EdmMetadata]([ModelHash]) 
values (@0) 
select [Id] 
from [dbo].[EdmMetadata] 
where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 
nvarchar(4000)',@0=N'682E6AFFD524702D8257BA17DF91292598D2B889E7CEA10779C4301E968CCB08'
  • Finally the INSERT statements for inserting records into the Person table will be executed which I am not showing here.

Next time when we execute the program the database will not be recreated and the EdmMetadata table will be queried to get the latest hash code of the model.

SELECT TOP (1) 
[Extent1].[Id] AS [Id], 
[Extent1].[ModelHash] AS [ModelHash] 
FROM [dbo].[EdmMetadata] AS [Extent1] 
ORDER BY [Extent1].[Id] DESC 

Now let’s make a little change in the model as shown below by changing the column name from FirstName to Name.

public class EmployeeConfiguration :EntityConfiguration<Employee> 
    { 
        public EmployeeConfiguration() 
        { 
            MapSingleType(e => 
                            new 
                            { 
                                PersonId = e.EmployeeID, 
                                Name     = e.FirstName, 
                                e.LastName, 
                                e.HireDate, 
                                e.EnrollmentDate 
                            } 
                          ).ToTable("dbo.Person"); 
            HasKey(e=>e.EmployeeID); 
            Property(e => e.EmployeeID).IsIdentity(); 
            Property(e => e.FirstName).IsRequired(); 
            Property(e => e.LastName).IsRequired(); 
        } 
    } 

We get an InvalidOperationException with the following message:

The model backing the ‘SchoolDBContext’ context has changed since the database was created.  Either manually delete/update the database, or call Database.SetInitializer with an IDatabaseInitializer instance.  For example, the RecreateDatabaseIfModelChanges strategy will automatically delete and recreate the database, and optionally seed it with new data.

Let’s use a RecreateDatabaseIfModelChanges with the following line of code:

Database.SetInitializer<SchoolDBContext>(new RecreateDatabaseIfModelChanges<SchoolDBContext>());

Now the sequence of operations is:

  • Check if database exists
  • If exists get the hash code of the model
  • Decide if the model in code has changed based on hash code.If changed then
    • Drop the database
    • Create a new database & tables
    • Insert a new hash code record for model
  • Insert records in Person table.

If we want to work with an existing database then we need to avoid this CREATE/DROP of databases and this can be done by removing the database initializer as shown below:

Database.SetInitializer(null);
About these ads
Comments
  1. Entity Framework CTP 4.0 – Database Initialization « Sankarsan’s Journal…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. Entity Framework CTP 4.0 – Database Initialization « Sankarsan’s Journal…

    Thank you for submitting this cool story – Trackback from YOUR-SITE-TITLE…

  3. [...] more: Entity Framework CTP 4.0 – Database Initialization « Sankarsan's … No [...]

  4. [...] Entity Framework CTP 4.0 – Database Initialization – Sankarsan explores the Entity Framework CTP 4.0’s ability to create database schema for you on first use when developing using the Code First Approach, showing how to configure and use this mechanism. [...]

  5. [...] This post was mentioned on Twitter by John Katsiotis, Sankarsan Bose. Sankarsan Bose said: Entity Framework CTP 4.0 – Database Initialization http://dlvr.it/70Gbf [...]

  6. [...] Entity Framework CTP 4.0 – Database Initialization – Sankarsan [...]

  7. [...] In the last two posts I have discussed about Entity Framework CTP 4.0 Code First Approach and Database Initialization.Now we will take a look at how to define relationships and navigational properties between the [...]

  8. Mark says:

    Great article.

    I have a problem where I want to be able to use an existing EMPTY database (created by my host) with provided user ID and password in my connection string. Any idea how I create and populate the schema with EF Code First? Everything works fine and the DB is auto created via trusted_connection on my local PC but just can’t get it to work with an existing empty database. Thanks, Mark

  9. sankarsan says:

    If you use RecreateDatabaseIfModelChanges option it will recreate the empty database with new schema as per your code.
    I am not sure if this is what you want

    • Mark says:

      Hi Sankarsan.

      RecreateDatabaseIfModelChanges drops the entire database and recreates it but this wont always work in a hosted environment where you create the database first and then install your app into it? I need to use a custom stratagy to only create the schema (tables, relationships etc) from my POCO classes and insert it into the existing database.

      Thanks -Mark

  10. agog says:

    where is best location for this piece of code?

    Database.SetInitializer(new RecreateDatabaseIfModelChanges());

    • diopio says:

      I’m using in the following location and is working…

      file Global.asax.cx

      Application_Start(){

      Database.SetInitializer(…);

      }

  11. [...] your own custom implementation of IDatabaseInitializer if you want (a decent overview can be found here. But basically you are talking about the code below being used to setup your [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s