postgresql (npgsql 2.2.5) with entity framework 6 and code first

1.3k views Asked by At

I am having issues right now with initializing the database using code first. I'm basically having problems on how to trigger my initializer if my database does not exist.

I've tried the following,

https://stackoverflow.com/a/28960111/639713

but my problem with this is I have to call this method on my initial page for it to trigger the create database. Even with that, the tables will not be created unless I manually do it. This will be an issue if I'm going to integrate this with an app that is already using sql server and already have about 50 tables on the dbcontext.

Anyway, here's my code:

DbContext

public class TestMigrationsDatabase : DbContext
    {
        public TestMigrationsDatabase()
            : base(nameOrConnectionString: "TestMigrations.Domain.Entities.TestMigrationsDatabase")
        {
            //Database.SetInitializer<TestMigrationsDatabase>(null);
            Database.SetInitializer<TestMigrationsDatabase>(new TestMigrations.Domain.TestMigrationsInitializer());
        }
        public DbSet<Base> Bases { get; set; }
        public DbSet<Fighter> Fighters { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema("public"); // postgresql specific
            base.OnModelCreating(modelBuilder);
        }
        public override int SaveChanges()
        {
            return base.SaveChanges();
        }
    }

Initializer:

public class TestMigrationsInitializer : CreateDatabaseIfNotExists<TestMigrationsDatabase>
    {
        protected override void Seed(TestMigrationsDatabase context)
        {

            this.CreateDatabase(ConfigurationManager.ConnectionStrings["TestMigrations.Domain.Entities.TestMigrationsDatabase"].ToString());

            base.Seed(context);

            LoadTestTables(context);
        }


        private void LoadTestTables(TestMigrationsDatabase context){
            Base base = new Base();
            base.Name = "Test 1 Base";

            context.Bases.Add(base);

            context.SaveChanges();
        }

        public void CreateDatabase(string connectionString)
        {
            var builder = new NpgsqlConnectionStringBuilder(connectionString);
            var databaseName = "TestMigrations"; // REMEMBER ORIGINAL DB NAME
            builder.Database = "postgres"; // TEMPORARILY USE POSTGRES DATABASE

            // Create connection to database server
            using (var connection = new NpgsqlConnection(builder.ConnectionString))
            {
                connection.Open();

                // Create database
                var createCommand = connection.CreateCommand();
                createCommand.CommandText = string.Format(@"CREATE DATABASE ""{0}"" ENCODING = 'UTF8'", databaseName);
                createCommand.ExecuteNonQuery();

                connection.Close();
            }
        }
    }

Controller

public class HomeController : Controller
    {
        public TestMigrationsDatabase _context = new TestMigrationsDatabase();

        //
        // GET: /Home/

        public ActionResult Index()
        {
            TestMigrations.Domain.TestMigrationsInitializer initializer = new Domain.TestMigrationsInitializer();
            initializer.CreateDatabase(ConfigurationManager.ConnectionStrings["TestMigrations.Domain.Entities.TestMigrationsDatabase"].ToString());
     return View();
}
}

So with all that, my questions are: 1. Is putting that initializer on the first controller to trigger it correct? Or should I just instantiate the context so that the constructor will trigger teh initializer? 2. How do I properly create the tables after the database is created?

Thanks!!!

0

There are 0 answers