none
Stop Database Creation RRS feed

  • Question

  • Hello,

    I need to use EF4.1 with an existing database.

    1 - How can I avoid the Context to not create the database?

    2 - And could I set up a custom create database script in:

    public class MyDbInitializer : DropCreateDatabaseIfModelChanges<MyContext>
    {
      protected override void Seed(MyContext context)
      {
    
        context.Database.ExecuteSqlCommand("CREATE DATABASE ....");
    
      }
    }
    


    Thank You,

    Miguel

    Tuesday, July 5, 2011 7:01 PM

Answers

  • I believe you are right - if you want to use FileStream attribute for you blob column or some advanced settings then you need to create the db on your own to account for these.

    With CompatibleWithModel things are easier and more complicated at the same time. First, Let's take a look at how we check whether a model is compatible with the database. The first step is to calculate the hash of the model. There is a public API in the System.Data.Entity.Infrastructure that allows you to do this:

    EdmMetadata.TryGetModelHash(ctx)
    
    

    When DbContext creates the database it creates an additional table called EdmMetadata. This table has just two columns: Id (which is not really interesting) and ModelHash. The ModelHash contains the hash of the model calculated with EdmMetadata.TryGetModelHash() method. Now to compare whether the model is compatible with the database we calculate the hash and compare to the value stored in the database. As you can see we don't try to compare the database schema with your model since it would be to costly. One thing worth noting is that the bool flag passed to Database.CompatibleWithModel(bool) is pretty important. If the flag is set false and the EdmMetadata table is not in the database the Database.CompatibleWithModel(bool) returns true (so, if you had a database that was not created with code first it may always be "compatible" with your model just by lacking EdmMetdata table) otherwise an exception will be throw.

    Now that we looked at how Database.CompatibleWithModel() method works we need to make it actually work for us. If you try using it without "playing by the rules" it just won't work. This is pretty straightforward - when creating a database "manually" we just need to make sure that the EdmMetadata table gets added and the hash for the current model is populated.

    Now a few words about "context.Database.Create();". This creates a new database for you and adds all the tables inferred from the CodeFirst model. I don't think using ExecuteSqlCommand with "CREATE DATABASE" after one the database has been created will work for you. I have not tried but either you will get an exception or you will end up having two databases. Trying creating a new database after deleting the database with the same context will not work either. The reason for it is that DbContext will try reconnecting to the database you have just deleted and it will fail.

    Last but not least. Trying to create a database entirely manually may defeat the purpose when using CodeFirst approach. The reason for this is that each time you change your entities you will have to go and fix your database script to reflect the changes. The code first approach is about actually not having to do this. However what is reasonable to do is to modify the database script generated for your model and add missing stuff (e.g. in your case FileStream attribute).

    I prepared a short example showing how you can achieve what you need (except for modifying the database script part):

    using System;
    using System.Linq;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Data.SqlClient;
    
    namespace ConsoleApplication14
    {
      public class Entity
      {
        public int Id { get; set; }
        public string Name { get; set; }
      }
    
      public class MyContext : DbContext
      {
        public DbSet<Entity> Entities { get; set; }
      }
    
      public class DbInitializer : IDatabaseInitializer<MyContext>
      {
        public void InitializeDatabase(MyContext context)
        {
          bool databaseExists = false;
          using (var initDbCtx = new MyContext())
          {
            databaseExists = initDbCtx.Database.Exists();
            if (databaseExists && !initDbCtx.Database.CompatibleWithModel(true))
            {
              initDbCtx.Database.Delete();
              databaseExists = false;
            }
          }
    
          if (!databaseExists)
          {
            var masterDbConnectionStringBuilder = new SqlConnectionStringBuilder(context.Database.Connection.ConnectionString);
            masterDbConnectionStringBuilder.InitialCatalog = "master";
    
            using (var createDbCtx = new DbContext(masterDbConnectionStringBuilder.ConnectionString))
            {
              createDbCtx.Database.ExecuteSqlCommand("create database [ConsoleApplication14.MyContext]");
            }
    
            using (var initDbCtx = new MyContext())
            {
              initDbCtx.Database.ExecuteSqlCommand(
                // you want to modify this script programmatically here
                  ((IObjectContextAdapter)initDbCtx).ObjectContext.CreateDatabaseScript()
                /*
                @"create table [dbo].[EdmMetadata] (
                  [Id] [int] not null identity,
                  [ModelHash] [nvarchar](max) null,
                  primary key ([Id])
                );
                create table [dbo].[Entities] (
                  [Id] [int] not null identity,
                  [Name] [nvarchar](max) null,
                  primary key ([Id])
                );"*/);
    
              initDbCtx.Set<EdmMetadata>().Add(new EdmMetadata() { ModelHash = EdmMetadata.TryGetModelHash(initDbCtx) });
              initDbCtx.SaveChanges();
            }
          }      
        }
      }
    
      class Program
      {
        static void Main(string[] args)
        {
          Database.SetInitializer<MyContext>(new DbInitializer());
    
          using (var ctx = new MyContext())
          {
            Console.WriteLine(EdmMetadata.TryGetModelHash(ctx));
            Console.WriteLine(ctx.Entities.FirstOrDefault());       
          }
        }
      } 
    }

     

    Some interesting points here are:
    - if the database does not exist I need to create a separate context that connects to "master" database to create a new database
    - you can drill into the ObjectContext instance to get the Sql script generated for your CodeFirst model
    - I populate metadata hash by creating a DbSet dynamically

    Database.SetInitializer (hey, we have moved on - there is no DbDatabase anymore - you can get the RTM version here: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=8363 (dubbed Entity Framework 4.1) or a June CTP released just last week here: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26660) sets values a static dictionary so you do it just once for an AppDomain regardless of how many contexts of the same type you are actually creating.

    Using initializers in production environment - I don't think you really want to do this if you delete your database in the initializer. You probably don't want to loose all the production data just because something in your model changed. What you may want to have in a production environment is an initializer that creates a database if one does not exist and throws if the one that exists does not match your model to avoid mismatches between model and the database.
    We are working on a better solution for this problem. You can take a look here: http://blogs.msdn.com/b/efdesign/archive/2010/10/22/code-first-database-evolution-aka-migrations.aspx if you are interested.

    Pawel

    Wednesday, July 6, 2011 7:17 PM

All replies

  • I think the question is why you would like to hack like this if the DbContext can create a database for you?

    Anyways what you are trying to do will probably not work. There are two reasons for this is:

    1) Seed() is being invoked after the database has been already been created. So you either ended up having an exception because you are trying to create a database with a name that is the same as the already existing database or you would create a database but if it will have a different name than the one specified in the connection string and EF will not use it.

    2) DropCreateDatabaseIfModelChanges adds some additional data to the database to be able to recognize if the model changed or not. Since you probably won't do it you may end up seeing weird problems because of this.

    If you want to be fully in charge of creating the database you need actually implement IDatabaseInitializer<TContext> interface (http://msdn.microsoft.com/en-us/library/gg696323(v=VS.103).aspx) instead of deriving from DropCreateDatabaseIfModelChanges class. In this case you have a full control of what is happening (note that this also means you need to figure out if the database schema changed or not to know whether to delete and recreate the database or not)

    You set the initializer by calling:

    Database.SetInitializer() method (http://msdn.microsoft.com/en-us/library/gg679461(v=VS.103).aspx)

    Pawel

    Tuesday, July 5, 2011 10:10 PM
  • I think the question is why you would like to hack like this if the DbContext can create a database for you?

    For a few reasons:

    A) DbContext won't create FileStream columns that I need. Am I wrong?

    B) A database might already exist or need custom setup as File Groups, etc.

    About your answer to (2). I followed your tips went to MSDN and did the following:

     public class Context : DbContext {
    
      public Context() : base() { 
      } // Context
    
      public Context(DbConnection connection) : base(connection, true) { 
    
       this.Configuration.LazyLoadingEnabled = false;
    
       Database.SetInitializer<Context>(new ContextInitializer());
    
      } // Context
    
      public DbSet<Role> Roles { get; set; }
      public DbSet<User> Users { get; set; }
    
      protected override void OnModelCreating(DbModelBuilder builder) {
    
       // Define tables mappings and relationships
    
      } // OnModelCreating
    
     } // Context
    
     public class ContextInitializer : IDatabaseInitializer<Context> {
    
      public void InitializeDatabase(Context context) {
    
       if (context.Database.Exists()) {
    
        if (!context.Database.CompatibleWithModel(true)) {
         context.Database.Delete();
         context.Database.Create();
         context.Database.ExecuteSqlCommand("CREATE DATABASE ....");
        }
    
       } else {
    
        context.Database.Create();
        context.Database.ExecuteSqlCommand("CREATE DATABASE ....");
    
       }
    
      }
     }
    
    


    This is still a little bit confusing to me ...

    Since I am using FileStream the model will never be compatible with the database ... Am I wrong?

    Should I initialize the database only in a test?

    public void Should_be_able_to_initialize_database()
    {
      var initializer = new ContextInitializer();
      DbDatabase.SetInitializer(initializer);
      var target = new Context();
      target.Database.Initialize(true);
    }
    

    I am not sure how the initialization really works ...

    It makes sense in debug but it makes no sense to me in release.

    Am I doing something wrong?

    Thank you,

    Miguel

     

    Tuesday, July 5, 2011 11:16 PM
  • I believe you are right - if you want to use FileStream attribute for you blob column or some advanced settings then you need to create the db on your own to account for these.

    With CompatibleWithModel things are easier and more complicated at the same time. First, Let's take a look at how we check whether a model is compatible with the database. The first step is to calculate the hash of the model. There is a public API in the System.Data.Entity.Infrastructure that allows you to do this:

    EdmMetadata.TryGetModelHash(ctx)
    
    

    When DbContext creates the database it creates an additional table called EdmMetadata. This table has just two columns: Id (which is not really interesting) and ModelHash. The ModelHash contains the hash of the model calculated with EdmMetadata.TryGetModelHash() method. Now to compare whether the model is compatible with the database we calculate the hash and compare to the value stored in the database. As you can see we don't try to compare the database schema with your model since it would be to costly. One thing worth noting is that the bool flag passed to Database.CompatibleWithModel(bool) is pretty important. If the flag is set false and the EdmMetadata table is not in the database the Database.CompatibleWithModel(bool) returns true (so, if you had a database that was not created with code first it may always be "compatible" with your model just by lacking EdmMetdata table) otherwise an exception will be throw.

    Now that we looked at how Database.CompatibleWithModel() method works we need to make it actually work for us. If you try using it without "playing by the rules" it just won't work. This is pretty straightforward - when creating a database "manually" we just need to make sure that the EdmMetadata table gets added and the hash for the current model is populated.

    Now a few words about "context.Database.Create();". This creates a new database for you and adds all the tables inferred from the CodeFirst model. I don't think using ExecuteSqlCommand with "CREATE DATABASE" after one the database has been created will work for you. I have not tried but either you will get an exception or you will end up having two databases. Trying creating a new database after deleting the database with the same context will not work either. The reason for it is that DbContext will try reconnecting to the database you have just deleted and it will fail.

    Last but not least. Trying to create a database entirely manually may defeat the purpose when using CodeFirst approach. The reason for this is that each time you change your entities you will have to go and fix your database script to reflect the changes. The code first approach is about actually not having to do this. However what is reasonable to do is to modify the database script generated for your model and add missing stuff (e.g. in your case FileStream attribute).

    I prepared a short example showing how you can achieve what you need (except for modifying the database script part):

    using System;
    using System.Linq;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Data.SqlClient;
    
    namespace ConsoleApplication14
    {
      public class Entity
      {
        public int Id { get; set; }
        public string Name { get; set; }
      }
    
      public class MyContext : DbContext
      {
        public DbSet<Entity> Entities { get; set; }
      }
    
      public class DbInitializer : IDatabaseInitializer<MyContext>
      {
        public void InitializeDatabase(MyContext context)
        {
          bool databaseExists = false;
          using (var initDbCtx = new MyContext())
          {
            databaseExists = initDbCtx.Database.Exists();
            if (databaseExists && !initDbCtx.Database.CompatibleWithModel(true))
            {
              initDbCtx.Database.Delete();
              databaseExists = false;
            }
          }
    
          if (!databaseExists)
          {
            var masterDbConnectionStringBuilder = new SqlConnectionStringBuilder(context.Database.Connection.ConnectionString);
            masterDbConnectionStringBuilder.InitialCatalog = "master";
    
            using (var createDbCtx = new DbContext(masterDbConnectionStringBuilder.ConnectionString))
            {
              createDbCtx.Database.ExecuteSqlCommand("create database [ConsoleApplication14.MyContext]");
            }
    
            using (var initDbCtx = new MyContext())
            {
              initDbCtx.Database.ExecuteSqlCommand(
                // you want to modify this script programmatically here
                  ((IObjectContextAdapter)initDbCtx).ObjectContext.CreateDatabaseScript()
                /*
                @"create table [dbo].[EdmMetadata] (
                  [Id] [int] not null identity,
                  [ModelHash] [nvarchar](max) null,
                  primary key ([Id])
                );
                create table [dbo].[Entities] (
                  [Id] [int] not null identity,
                  [Name] [nvarchar](max) null,
                  primary key ([Id])
                );"*/);
    
              initDbCtx.Set<EdmMetadata>().Add(new EdmMetadata() { ModelHash = EdmMetadata.TryGetModelHash(initDbCtx) });
              initDbCtx.SaveChanges();
            }
          }      
        }
      }
    
      class Program
      {
        static void Main(string[] args)
        {
          Database.SetInitializer<MyContext>(new DbInitializer());
    
          using (var ctx = new MyContext())
          {
            Console.WriteLine(EdmMetadata.TryGetModelHash(ctx));
            Console.WriteLine(ctx.Entities.FirstOrDefault());       
          }
        }
      } 
    }

     

    Some interesting points here are:
    - if the database does not exist I need to create a separate context that connects to "master" database to create a new database
    - you can drill into the ObjectContext instance to get the Sql script generated for your CodeFirst model
    - I populate metadata hash by creating a DbSet dynamically

    Database.SetInitializer (hey, we have moved on - there is no DbDatabase anymore - you can get the RTM version here: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=8363 (dubbed Entity Framework 4.1) or a June CTP released just last week here: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26660) sets values a static dictionary so you do it just once for an AppDomain regardless of how many contexts of the same type you are actually creating.

    Using initializers in production environment - I don't think you really want to do this if you delete your database in the initializer. You probably don't want to loose all the production data just because something in your model changed. What you may want to have in a production environment is an initializer that creates a database if one does not exist and throws if the one that exists does not match your model to avoid mismatches between model and the database.
    We are working on a better solution for this problem. You can take a look here: http://blogs.msdn.com/b/efdesign/archive/2010/10/22/code-first-database-evolution-aka-migrations.aspx if you are interested.

    Pawel

    Wednesday, July 6, 2011 7:17 PM
  • Hello,

    I followed your example and looked at the video and I think I was able to make this work.

    I have only a few questions if you don't mind:

    1 - "Database.SetInitializer (hey, we have moved on - there is no DbDatabase anymore"

         Sorry? I got lost ...

         At the moment I am doing the following on a Test Method:

      public void Setup_Deploy_Success() {
    
       Database.SetInitializer<Context>(new ContextInitializer());
    
      }
    


        But I also have the following:

     public class Context : DbContext {
      public Context() : base() {
       this.Configuration.LazyLoadingEnabled = false;
       Database.SetInitializer<Context>(new ContextInitializer());
      } // Context
     }
    


        Maybe I should define the Initializer inside Context?

        In fact this way I can use in my test method:

    Context context = new Context();
    context.Database.Initialize(true);
    

       What is the correct way to do this?


    2 - Should I Initialize the database in Application_Start of my ASP.NET MVC Application?

         Or should I do this in a Test Method?

         And lets say I publish the application to go online and the database starts to get data.

         Later I need to a few small changes to my model.

         When I update the application how can I do the changes on the database preserving the data?

         Do I need to make the migration using T-SQL?

    Thank You,

    Miguel      

     

     

    Wednesday, July 6, 2011 10:05 PM
  • 1) In your example above you used "DbDatabase.SetInitializer(initializer);". DbDatabase was an API in CTP5 but was changed to Database in subsequent RC and RTM releases. Since you were using DbDatabase in your code I assumed that you are still using CTP5 and wanted to point you to the newer versions.

    Initializers are cached in a static dictionary that uses the type of the context as a key you can set initializer only once - ideally before you use your context for the first time (note that you don't need an instance of a DbContext class to set the initializer - you just need the type; that is why you are able to set initializer before you even start using your context by using Database.SetInitializer(initializer)). If you are using MSTest to run your tests you probably should be looking at methods marked with AssemblyInitialize or ClassInitialize attributes (take a look at the MSDN there are some comments about using these methods for testing ASP.NET apps). Putting the initializer into the ctor will work but will keep setting the initializer to the same value all the time which is not necessary.

    2) As I said before I would not put an initializer that drops the database in the production environment. An Initializer that only creates a database if it does not exist seems OK but it will do its job only once (if the database does not exist) while it will be invoked each time you use your DbContext derived type. Changing your model does not automatically mean changing the database (e.g. if you want to change a property name in your C# app you can do this and then you can tell the DbContext that the property you renamed should be mapped to an existing column in the database). Obviously in some cases you may want to change both the model and change the database. DbContext cannot handle this for you at the moment in other way than dropping the database (which also means losing all the data) and obviously you don't want this to happen in the production. To preserve the data you need migrate the database manually - e.g. using SqlServer Management Studio or Sql script.

    Pawel

    Wednesday, July 6, 2011 10:42 PM
  • Sorry, I am still confused about a few details ...

    I think the problem is I have been finding many posts ... Some with CTP4, CTP5, RTM, ... And it gets confusing.

    I am using Entity Framework 4.1 RTM ... I installed via NuGet.

    Let me clarify something. On my solution I have 3 projects:

    App.Data (Class Library), App.Site (MVC Application) and App.Test (MS Test)

    - On App.Data I have the Entities, Context, ContextInitializer (following your code) and SQL Scripts.

    - On App.Site I have an MVC Web Application.

    - On App.Test I have a test with:

    [TestMethod, TestCategory("Database")]
    public void Database_Initialize() {
     Database.SetInitializer<Context>(new ContextInitializer());
    }
    


    The questions (I will try to make them short): 

    1 - In the posts I read, where database is created first, one of the two options where placed inside the Context constructor:

         Database.SetInitializer<Context>(new ContextInitializer());    OR    Database.SetInitializer<Context>(null);

         But if the initializer is called somewhere else and runs only once why have it in the Context constructor?

    2 - Does it make sense to have the ContextInitializer and the SQL Scripts inside the App.Data project? Should I move them to App.Test?

    3 - When you say to look at AssemblyInitialize you mean something like:

    [TestMethod, AssemblyInitialize, TestCategory("Database")]
    public void Database_Initialize() {
     Database.SetInitializer<Context>(new ContextInitializer());
    }
    

      Correct?

    4 - For me, it looks odd to have the Database.SetInitializer in Application_Start.

         I was just following the example in Scott Guthrie's blog:

         http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx

         Please, scroll down "Re-synchronizing our Model Classes with the Database".

         There is other strange issue: he uses RecreateDatabaseIfModelChanges instead of IDatabaseInitializer ...

         I think that was something lost before RTM ... Correct?

     

    In resume I am looking:

    - Use EF 4.1 with its Pocos classes but having database created first as Code First does not support a few features (DONE);

    - Have an automatic way to:

       Create/Update and Insert initial data in the database on development.

       On production I think I only need to publish the project and include the database in it.

     

    Sorry for so many questions ...

    I am just moving from EF 4 and EDMX files to EF 4.1 and I would like to have this done correctly.

    Thank you,

    Miguel

     

    Thursday, July 7, 2011 12:56 AM
  • Ideally before you use your context for the first time (note that you don't need an instance of a DbContext class to set the initializer - you just need the type; that is why you are able to set initializer before you even start using your context by using Database.SetInitializer(initializer)).

    Isn't strange that in the test method I tried:

      [TestMethod, TestCategory("Base")]
      public void Database_Initialize() {
    
       Database.SetInitializer<Context>(new ContextInitializer());
    
       Context context = new Context();
       context.Database.Initialize(false);
    
      }
    

    And Database.SetInitializer<Context> does not recreate the database but the context.Database.Initialize(false) does?

    And I change my model ... I mean, I defined a few foreign keys.

    Shouldn't both actions do the same?

    Thank You,

    Miguel

    Thursday, July 7, 2011 1:22 AM
  • Entity Framework 4.1 is the latest supported version. You are good.

    1) As I said before - initializer are kept in a static dictionary. This means that you can treat this dictionary more or less like a global variable. When you use your DbContext derived class it will go to this dictionary and check if there is any initializer for it - if there is it will use it. Setting initializer in the DbContext constructor is like setting the same global variable to the same value all the time - it does not really hurt but there is no reason to do it.

    2) As lawyers say "It depends". I definitely would be afraid of using an initializer that deletes the database in the production environment. So you can either modify your initializer so that it does not delete the database in the production environment (you probably could pass a flag in the constructor to let the initializer know whether it can delete the database or not), you can create 2 slightly different initializers - one would be used in production and one would be used by tests or you can move your initializer to tests and do not use an initializer at all (by setting it to null) in your production environment. Not using the initializer would require to create the database manually though.

    3) Yes, this is what I meant.

    4) I think setting initializer in Application_Start makes a lot of sense. If you think about the dictionary that stores initializers as of a global variable than you set it once and you don't need to set it again. Note that Database.SetInitializer() does not do anything else - it is just setting the value in the dictionary. Once you start using your DbContext derived class it will go and use the initializer (if set) when needed.
    The blog post is pretty old and is based on CTP4 (preview). In the RTM (final/official) version you are using RecreateDatabaseIfModelChanges class does not exist anymore - it was renamed to DpropCreateDatabaseIfModelChanges. In the version you are using there are 3 built-in initializers: CreateDatabaseIfNotExists, DropCreateDatabaseAlways and DropCreateDatabaseIfModelChanges. I belive that CreateDatabaseIfNotExists is the default initializer that will be used if you don't change it. Scott was able to use RecreateDatabaseIfModelChanges in his blog post since RecreateDatabaseIfModelChanges implemented IDatabaseInitializer method. This is why you could use the initializer I posted code for above or the build int initializers.

    Pawel

     

    Thursday, July 7, 2011 5:10 PM
  • Database.Setinitializer() and DbContext.Database.Initialize() are two different things. Database.SetInitializer() just sets an initializer to be used for the given type - it just sets the static dictionary (kind of global variable) that keeps initializers for context types. So Database.SetInitializer() is like saying: "hey, whenever you need to initialize your database for a given DbContext derived type use this initializer". DbContext.Database.Initialize() is on the other hand the way to initialize the database. What this method does is (more or less) it goes and checks if there is any initializer (set with Database.SetInitializer()) for the context and runs it.

    If you changed your model by adding foreign keys you would probably want to either manually add foreign keys to the database or (in test environment) drop the database and let it be recreated by your initializer.

    Pawel

    Thursday, July 7, 2011 5:19 PM
  • If you changed your model by adding foreign keys you would probably want to either manually add foreign keys to the database or (in test environment) drop the database and let it be recreated by your initializer.

    If you say to drop the database so what is the use of following?

     

    if (initDbCtx.Database.Exists() && !initDbCtx.Database.CompatibleWithModel(true))
    

     

    So when I make a change in my model (Pocos) isn't the Initialize method fired? Now I am lost ...

    At the moment I am using the following:

    1 - Application_Start: "Database.SetInitializer<Context>(new ContextInitializer());"

         When I run my project, if there is no database, then it is created.

    2 - Test Method: Context context = new Context(); context.Database.Initialize(false);

         I use this to force a reinitialization of the database.

     

    But I have encountered a really strange problem which I am trying to solve since yesterday.

    When I delete the database and start my project or run the test method, sometimes, I start to get, repeatedly, the following error:

       The underlying provider failed on Open."}
       {"Cannot open database \"TestDatabase\" requested by the login. The login failed.\r\nLogin failed for user 'XPT\\Miguel'."}

    This errors happens on the following code line:

     initDbCtx.Database.ExecuteSqlCommand("create table dbo.EdmMetadata (...)")

    My connection string is, in my opinion, correct:

       <add name="Context" providerName="System.Data.SqlClient" connectionString="Server=XPT\SQLEXPRESS;Database=TestDatabase;Trusted_Connection=true;" />

    Usually to solve this I restart VS and add a few breakpoints to ContextInitializer. Usually it solves it ...

    I have been trying to figure what is going on but no luck ... And this is really strange.

    Any idea?

    Thank You,

    Miguel

     

    Thursday, July 7, 2011 6:57 PM
  • I cannot drop the database if the database does not exist - hence the "initDbCtx.Database.Exists()". I don't want to drop the database if the database matches my model - hence the .CompatibleWithModel() check. In other words I drop the database only if the database exists and the model has changed. Feel free to change this logic if you need.

    This

    Database.SetInitializer<Context>(new ContextInitializer());"


    won't create the database. It just sets the initializer. The database is created when the instance of your DbContext derived class needs to get to the database and it figures out that the database does not exist or you call DbContext.Database.Initialize()

    The exception you are seeing is something I talked about in one of my earlier answers. In your connection string you have defined the database:

    <add name="Context" providerName="System.Data.SqlClient" connectionString="Server=XPT\SQLEXPRESS;Database=TestDatabase;Trusted_Connection=true;" />

    Now when you drop this database it does not exist. Sql Server throws because you are trying to connect to a non-existing database. This is why in my initializer I am creating a generic DbContext using a modified connection string. The modification to connection string is to change the Database to the master database since the master database is guaranteed to exist. I connect to Sql Server with the new connection string just to create the database I will be using. Once this database has been created I can use my original connection string - database exists (although it is completely empty - no tables, no nothing) so I am able to connect and run the script creating objects (tables etc.).

    Pawel


    Thursday, July 7, 2011 7:58 PM
  • I cannot drop the database if the database does not exist - hence the "initDbCtx.Database.Exists()". I don't want to drop the database if the database matches my model - hence the .CompatibleWithModel() check. In other words I drop the database only if the database exists and the model has changed. Feel free to change this logic if you need.

    Yes, the logic seems fine ...

    I am using your code which means I am using master to create the database.

    And this is why the error is strange. I tried the following:

    1 - The application is working and database is set.

    2 - I delete the database using SSMS

    3 - I restart my application which has Database.SetInitializer in Application_Start.

    4 - The moment the application uses the context ContextInitializer is fired and I get the following error:

         A transport-level error has occurred when sending the request to the server.

         (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

         The database is not even created ... And it is using master.

    5 - I try again and now the database is created but I get the following error on the second step which is using the context:

         The underlying provider failed on Open."}
         {"Cannot open database \"TestDatabase\" requested by the login. The login failed.\r\nLogin failed for user 'XPT\\Miguel'."}

    6 - I restart VS 2010 and try again an now everything works fine ...

    7 - I delete the database again, run the application, and surprising everything works fine.

         However, now and then if I delete the database (4) happens again or just (5).

    This is what seems strange to me ...   

    My code is basically yours ... I just changed some names and I am getting the SQL script from embed resources:

     public void InitializeDatabase(Context context) {
    
      using (Context mask = new Context()) {
    
      if (mask.Database.Exists() && !mask.Database.CompatibleWithModel(true)) {
       mask.Database.Delete();
      }
    
      if (!mask.Database.Exists()) {
    
       SqlConnectionStringBuilder connection = new SqlConnectionStringBuilder(mask.Database.Connection.ConnectionString);
       connection.InitialCatalog = "master";
    
       using (DbContext master = new DbContext(connection.ConnectionString)) {
    
       using (StreamReader deploy = new StreamReader(Assembly.GetExecutingAssembly().GetManifestResourceStream("App.Dat.Deploy.sql"))) {
        master.Database.ExecuteSqlCommand(deploy.ReadToEnd());
       }
    
       }
    
       using (StreamReader setup = new StreamReader(Assembly.GetExecutingAssembly().GetManifestResourceStream("App.Setup.sql"))) {
       mask.Database.ExecuteSqlCommand(setup.ReadToEnd());
       }
    
       mask.Set<EdmMetadata>().Add(new EdmMetadata() { ModelHash = EdmMetadata.TryGetModelHash(mask) });
       mask.SaveChanges();
    
      List<Role> roles = new List<Role> {
       new Role { Name = "Administrator" },
       new Role { Name = "Collaborator" }
      };
      roles.ForEach(x => context.Roles.Add(x));
      context.SaveChanges();
    
      }
    
      }
    
     } // InitializeDatabase


    So it was this random behavior that I was trying to find out.

    Thank You,

    Miguel


    Thursday, July 7, 2011 9:37 PM
  • The error message you get means that the connection is broken. It can happen for many reasons - e.g. the underlying network connection to the database stopped working. If you are deleting the database with SSMS while the connection is open it could also break the connection. AFAIK SqlClient does not recognize broken connection so if a connection gets broken it will be returned to the connection pool. Later when you try opening a connection you can get a broken connection from the pool and this is when you will see the exception. Regardless, I looked once again at the initializer I wrote and I think there is one issue that might be causing kind of problems you are seeing (I am not sure - It did not happen on my machine). The issue is that we keep the connection to the database open all the time even though in the meantime we are dropping and recreating the database. I believe the right think to do is to close the connection to the database before it is dropped and re-open the connection after the database has been created. To do this I just need two use two instances of DbContext - one before dropping the database and one after the database was created.
    Here is the updated code (I will also update the code in the post above):

        public void InitializeDatabase(MyContext context)
        {
          bool databaseExists = false;
          using (var initDbCtx = new MyContext())
          {
            databaseExists = initDbCtx.Database.Exists();
            if (databaseExists && !initDbCtx.Database.CompatibleWithModel(true))
            {
              initDbCtx.Database.Delete();
              databaseExists = false;
            }
          }
    
          if (!databaseExists)
          {
            var masterDbConnectionStringBuilder = new SqlConnectionStringBuilder(context.Database.Connection.ConnectionString);
            masterDbConnectionStringBuilder.InitialCatalog = "master";
    
            using (var createDbCtx = new DbContext(masterDbConnectionStringBuilder.ConnectionString))
            {
              createDbCtx.Database.ExecuteSqlCommand("create database [ConsoleApplication14.MyContext]");
            }
    
            using (var initDbCtx = new MyContext())
            {
              initDbCtx.Database.ExecuteSqlCommand(
                // you want to modify this script programmatically here
                  ((IObjectContextAdapter)initDbCtx).ObjectContext.CreateDatabaseScript()
                /*
                @"create table [dbo].[EdmMetadata] (
                  [Id] [int] not null identity,
                  [ModelHash] [nvarchar](max) null,
                  primary key ([Id])
                );
                create table [dbo].[Entities] (
                  [Id] [int] not null identity,
                  [Name] [nvarchar](max) null,
                  primary key ([Id])
                );"*/);
    
              initDbCtx.Set<EdmMetadata>().Add(new EdmMetadata() { ModelHash = EdmMetadata.TryGetModelHash(initDbCtx) });
              initDbCtx.SaveChanges();
            }
          }      
        }

     

    Can you try it to see if it fixes your issue?

    Pawel


    Friday, July 8, 2011 4:53 PM
  • Hello,

    I tried the new code and got the same problem.

    Basically the steps are:

    1 - I start the project with a database in place and everything works fine.

    2 - I go to SSMS and delete the database and its connections.

    3 - I restart the project and get the error:

         A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

         No database is created.

    4 - I restart the project again and I get:

         The underlying provider failed on Open.

         No database is created.

    5 - I restart the project again and I get:

         The underlying provider failed on Open.

         Now the error was in the code line where the tables should be created.

         So it means the database was created but the tables were not.

         If I restart the database at this point I get the error:

           Model compatibility cannot be checked because the database does not contain model metadata.

           Ensure that IncludeMetadataConvention has been added to the DbModelBuilder conventions.

         It makes sense since the tables where not created.

    6 - I keep trying even after closing VS 2010 and SSMS and the errors keeps going.

          This is really strange.

    7 - Finally to solve the problem I just run the following test:

     

     [TestMethod, TestCategory("Data")]
     public void Context_Initialize_Success() {
    
      try {
    
      Database.SetInitializer<Context>(new ContextInitializer());
    
      Context context = new Context();
    
      if (context.Database.Exists())
       context.Database.Delete(); 
      
      context.Database.Initialize(false);
    
      } catch (Exception ex) {
    
      Assert.Fail(ex.Message);
    
      }
    
     } // Context_Initialize_Success
    

     


    And now everything is fine ...

    I have been doing changes on the initializer but I always end up running the test ...

    Any idea?

    Thank you,

    Miguel


    Friday, July 8, 2011 6:19 PM
  • Hi Miguel,

    I was not able to repro the problem from the step "3" and I am not sure if it is related to the initializer. However I was able to repro the "underlying provider failed to Open" exception. This seems to be a timing issue since it never happens when I step through the code. I have not had a chance to investigate the root cause but it seems that the Dispose (invoked by the framework because of using "using" block) method does not close the connection immediately and as a result when we create a new context to add tables the database has not actually been created yet. The workaround that worked on my machine was to close the connection explicitly instead of relying on Dispose() method. Can you try it and let me know if it helps? You need to add just one line below the line where you create the database. The whole using block should look like this:

    using (var createDbCtx = new DbContext(masterDbConnectionStringBuilder.ConnectionString))
    {
        createDbCtx.Database.ExecuteSqlCommand("create database [ConsoleApplication14.MyContext]");
        createDbCtx.Database.Connection.Close();
    }

    Pawel 

    Thursday, July 14, 2011 5:43 PM