首先是实体框架代码——让它执行“CREATE schema”;没有删除数据库

本文关键字：schema CREATE 数据库 删除 执行 实体 框架 代码 | 更新日期: 2023-09-27 17:50:56

我正在尝试做更好的数据整合性能&备份统一，允许单独的项目在一个数据库中使用单独的模式。

但是我被卡住了，实体框架在它的一个database . create()函数中执行两个关注点-数据库创建然后表对象创建。

是否有一种方法，只是得到表对象创建活动没有数据库重新创建?我希望每个项目共享一个数据库，但具有良好定义的模式所有权。

这个代码的主要项目是首先使用代码，这样我们的团队可以同时在模型的各个部分上工作。此外，该项目不使用迁移，因为我们已经在所有部署到生产环境的部署中使用了智能默认值。

下面是我到目前为止创建的代码。"//TODO:"部分是我卡住的地方。

的问候伊恩。

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

首先是实体框架代码——让它执行“CREATE schema”;没有删除数据库

我不太清楚为什么要这样做，但是如果模式的重建是问题，也许这可以帮助您:

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

好的，我已经想出了一个解决办法。我找到了命令拦截器主题，然后实现了一个将"CREATE DATABASE"更改为"SELECT 0"语句。

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

详细信息请参见MSDN -记录和拦截数据库操作(EF6起)

和Github - julielerman/EF6Interceptors

注意，我认为拦截器应该用于单元测试，所以我不建议将其作为问题类型的首选解决方案。

相关文章：