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

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
    }
}

score 2 · Answer 1 · answered May 28 '15 at 09:42

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);

Well, its the create table statements out the context.Database.Create() that I want, but on a database that already exists with other schema s like [dbo] [b2] [b3] etc. So, I want to disable the database create but allow the table create. — Ian Tubman, May 28 '15 at 11:44

score 0 · Answer 2 · answered Jun 01 '15 at 05:48

Ok, I have worked out a solution. I found the command interceptors topic then implemented one to change "CREATE DATABASE" into a "SELECT 0" statement.

                // context.Database.Create();  // TODO: remove this and make delete the schema and its objects
                var HandleNoCreateDatabase = new HandleNoCreateDatabase();
                DbInterception.Add(HandleNoCreateDatabase);
                context.Database.Create();
                DbInterception.Remove(HandleNoCreateDatabase);

...

public class HandleNoCreateDatabase : IDbCommandInterceptor
{

    public void NonQueryExecuting(
        DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        LogIfNonAsync(command, interceptionContext);
    }

    public void NonQueryExecuted(
        DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        LogIfError(command, interceptionContext);
    }

    public void ReaderExecuting(
        DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        LogIfNonAsync(command, interceptionContext);
    }

    public void ReaderExecuted(
        DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        LogIfError(command, interceptionContext);
    }

    public void ScalarExecuting(
        DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        LogIfNonAsync(command, interceptionContext);
    }

    public void ScalarExecuted(
        DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        LogIfError(command, interceptionContext);
    }

    private void LogIfNonAsync<TResult>(
        DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
    {
        if (!interceptionContext.IsAsync)
        {
            Debug.Print("Non-async command used: {0}", command.CommandText);

            // If EF is checking for database existance, tell it that it does not exist
            if (command.CommandText.ToLower().Contains("select count(*) from sys.databases where [name]="))
            {
                command.CommandText = "SELECT 0";
            }

            // If EF is creating the database, disable the create request
            if (command.CommandText.ToLower().Contains("create database"))
            {
                command.CommandText = "SELECT 0";
            }
        }
    }

    private void LogIfError<TResult>(
        DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
    {
        if (interceptionContext.Exception != null)
        {
            Debug.Print("Command {0} failed with exception {1}",
                command.CommandText, interceptionContext.Exception);
        }
    }
}

For more details see MSDN - Logging and Intercepting Database Operations (EF6 Onwards)

And Github - julielerman/EF6Interceptors

Note, I think interceptors should be use for unit testing so I do not recommend using this as the first choice solution for types of problems.

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

2 Answers2

Linked