Entity Framework code first - make it do "CREATE SCHEMA" without a drop database?

c# entity-framework-6

Question

I am trying to do better data consolidation performance & backup unity by, allowing separate project to use separate schema within one database.

But I am stuck and the point where entity framework performs two concerns - database creation then table object creation - within its one Database.Create() function.

Is there a way to just get the table object creation activity without database re-creation? I hope to have every project sharing one database but with well defined schema ownership.

The main project of this code is using code first so our team can work on various parts of the model at the same time. Plus, the project does not use migrations because we already use smart defaults on all deployments to production.

Below is the code I have created so far. The "//TODO:" part is where I am stuck.

Regards Ian

namespace app1.Models
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure.Interception;
    using System.Diagnostics;
    using System.Linq;

    public class Model1 : DbContext
    {
        public Model1()
            : base("name=Model1")
        {
            // Log database activity
            this.Database.Log = DebugWrite;
        }
        private void DebugWrite(string s) { Debug.Write(s); } // Avoiding Compiler Error CS1618

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema("b1");
            base.OnModelCreating(modelBuilder);
        }

        //public virtual DbSet<blog1> blog1 { get; set; }
        //public virtual DbSet<common> common { get; set; }
    }


    public class DbB1SchemaInitializer : IDatabaseInitializer<Model1>
    {
        public void InitializeDatabase(Model1 context)
        {
            context.Database.Log = DebugWrite;

            if (context.Database.Exists())
            {
                if (!context.Database.CompatibleWithModel(true))
                {
                    context.Database.Delete();  // TODO: remove this and make delete the schema and its objects
                    context.Database.Create();  // TODO: remove this and make delete the schema and its objects

                    // Reinstall, create schema and application role.
                    context.Database.ExecuteSqlCommand("CREATE SCHEMA b1");
                    context.Database.ExecuteSqlCommand("CREATE APPLICATION ROLE blog1 WITH PASSWORD = 'Pwd0123456', DEFAULT_SCHEMA = b1");
                    context.Database.ExecuteSqlCommand("GRANT SELECT, UPDATE, INSERT, DELETE, EXECUTE on SCHEMA::b1 to blog1");
                }
            }
            else
            {
                // Fresh install, create the database, schema and application role.
                context.Database.Create(); // Create will make database and make the tables.
                context.Database.ExecuteSqlCommand("CREATE APPLICATION ROLE blog1 WITH PASSWORD = 'Pwd0123456', DEFAULT_SCHEMA = b1");
                context.Database.ExecuteSqlCommand("GRANT SELECT, UPDATE, INSERT, DELETE, EXECUTE on SCHEMA::b1 to blog1");
            }

            // Do database connection interception so database application security is used rather than database user security from this point on.
            //DbInterception.Add(new EFDBConnectionApplicationRoleInterception("blog1", "Pwd0123456"));
        }
        private void DebugWrite(string s) { Debug.Write(s); } // Avoiding Compiler Error CS1618
    }
}
1
4
5/28/2015 9:26:40 AM

Popular Answer

Its not totally clear to me why you want to do this, but if the recreation of the schema is the issue, maybe this can help you:

var command = "IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'b1')) " +
            "BEGIN" +
            "  EXEC ('CREATE SCHEMA B1');" +
            "  EXEC ('CREATE APPLICATION ROLE blog1 WITH PASSWORD = ''Pwd0123456'', DEFAULT_SCHEMA = b1');" +
            "  EXEC ('GRANT SELECT, UPDATE, INSERT, DELETE, EXECUTE on SCHEMA::b1 to blog1');" +
            "END";
context.Database.ExecuteSqlCommand(command);
2
5/28/2015 9:42:02 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow