代码优先为 EF6 的多租户

本文关键字：EF6 代码 | 更新日期: 2023-09-27 18:35:56

我们的组织需要有一个单一的数据库，多租户
（按表架构，而不是按租户 ID）体系结构。

这里有一篇关于开始这种事情的很棒的文章：http://romiller.com/2011/05/23/ef-4-1-multi-tenant-with-code-first/

在文章的中间，这是这样写的：

你会注意到（可能有些沮丧）我们需要编写代码为每个实体配置表架构。诚然没有许多魔法独角兽在这个代码周围吃草......在 EF 的未来版本中我们将能够用更干净的自定义约定替换它。

我们的目标是以最干净的方式拥有单个上下文类，我们可以使用该上下文类连接到具有相同模型的多个架构。
（请注意，modelBuilder.HasDefaultSchema 似乎还不够，因为它只适用于 EF 第一次初始化上下文并运行 OnModelCreateating）

EF5 或 EF6 中是否存在上述清理器自定义约定？
或者有没有一种更清洁的方法以某种方式处理这个问题？

注意：我也在开发论坛上问过这个问题，因为它似乎与 EF 的方向更相关，但想看看这里是否有人有替代方案。

注意2：我不担心迁移，我们会单独处理。

代码优先为 EF6 的多租户

如果在DbContext上实现IDbModelCacheKeyProvider，则OnModelCreating中的属性modelBuilder.HasDefaultSchema就足够了。模型创建一次，然后由EntityFramwork在内部缓存，您可以为缓存定义自己的键。将架构名称作为模型缓存键，EF 将通过每个不同的缓存键（在本例中为架构）创建一个模型。这是我的概念验证代码：

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TenantDataModel;
namespace TenantDataContext
{
    public class TenantDataCtx : DbContext, IDbModelCacheKeyProvider
    {
        #region Construction
        public static TenantDataCtx Create(string databaseServer, string databaseName, string databaseUserName, string databasePassword, Guid tenantId)
        {
            var connectionStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
            connectionStringBuilder.DataSource = databaseServer;
            connectionStringBuilder.InitialCatalog = databaseName;
            connectionStringBuilder.UserID = databaseUserName;
            connectionStringBuilder.Password = databasePassword;
            string connectionString = connectionStringBuilder.ToString();
            return new TenantDataCtx(connectionString, tenantId);
        }
        // Used by EF migrations
        public TenantDataCtx()
        {
            Database.SetInitializer<TenantDataCtx>(null);
        }
        internal TenantDataCtx(string connectionString, Guid tenantId)
            : base(connectionString)
        {
            Database.SetInitializer<TenantDataCtx>(null);
            this.SchemaName = tenantId.ToString("D");
        }
        public string SchemaName { get; private set; }
        #endregion
        #region DataSet Properties
        public DbSet<TestEntity> TestEntities { get { return this.Set<TestEntity>(); } }
        #endregion
        #region Overrides
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            if (this.SchemaName != null)
            {
                modelBuilder.HasDefaultSchema(this.SchemaName);
            }
            base.OnModelCreating(modelBuilder);
        }
        #endregion
        #region IDbModelCacheKeyProvider Members
        public string CacheKey
        {
            get { return this.SchemaName; }
        }
        #endregion
    }
}

此外，我还找到了一种使用 EF 迁移的方法。我对我的解决方案不太满意，但似乎现在没有其他解决方案可用。

using System;
using System.Collections.Generic;
using System.Data.Entity.SqlServer;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace TenantDatabaseManager
{
    public class SqlServerSchemaAwareMigrationSqlGenerator : SqlServerMigrationSqlGenerator
    {
        private string _schema;
        public SqlServerSchemaAwareMigrationSqlGenerator(string schema)
        {
            _schema = schema;
        }
        protected override void Generate(System.Data.Entity.Migrations.Model.AddColumnOperation addColumnOperation)
        {
            string newTableName = _GetNameWithReplacedSchema(addColumnOperation.Table);
            var newAddColumnOperation = new System.Data.Entity.Migrations.Model.AddColumnOperation(newTableName, addColumnOperation.Column, addColumnOperation.AnonymousArguments);
            base.Generate(newAddColumnOperation);
        }
        protected override void Generate(System.Data.Entity.Migrations.Model.AddPrimaryKeyOperation addPrimaryKeyOperation)
        {
            addPrimaryKeyOperation.Table = _GetNameWithReplacedSchema(addPrimaryKeyOperation.Table);
            base.Generate(addPrimaryKeyOperation);
        }
        protected override void Generate(System.Data.Entity.Migrations.Model.AlterColumnOperation alterColumnOperation)
        {
            string tableName = _GetNameWithReplacedSchema(alterColumnOperation.Table);
            var newAlterColumnOperation = new System.Data.Entity.Migrations.Model.AlterColumnOperation(tableName, alterColumnOperation.Column, alterColumnOperation.IsDestructiveChange);
            base.Generate(newAlterColumnOperation);
        }
        protected override void Generate(System.Data.Entity.Migrations.Model.DropPrimaryKeyOperation dropPrimaryKeyOperation)
        {
            dropPrimaryKeyOperation.Table = _GetNameWithReplacedSchema(dropPrimaryKeyOperation.Table);
            base.Generate(dropPrimaryKeyOperation);
        }
        protected override void Generate(System.Data.Entity.Migrations.Model.CreateIndexOperation createIndexOperation)
        {
            string name = _GetNameWithReplacedSchema(createIndexOperation.Table);
            createIndexOperation.Table = name;
            base.Generate(createIndexOperation);
        }
        protected override void Generate(System.Data.Entity.Migrations.Model.CreateTableOperation createTableOperation)
        {
            string newTableName = _GetNameWithReplacedSchema(createTableOperation.Name);
            var newCreateTableOperation = new System.Data.Entity.Migrations.Model.CreateTableOperation(newTableName, createTableOperation.AnonymousArguments);
            newCreateTableOperation.PrimaryKey = createTableOperation.PrimaryKey;
            foreach (var column in createTableOperation.Columns)
            {
                newCreateTableOperation.Columns.Add(column);
            }
            base.Generate(newCreateTableOperation);
        }
        protected override void Generate(System.Data.Entity.Migrations.Model.RenameTableOperation renameTableOperation)
        {
            string oldName = _GetNameWithReplacedSchema(renameTableOperation.Name);
            string newName = renameTableOperation.NewName.Split(new char[] { '.' }).Last();
            var newRenameTableOperation = new System.Data.Entity.Migrations.Model.RenameTableOperation(oldName, newName, renameTableOperation.AnonymousArguments);
            base.Generate(newRenameTableOperation);
        }
        protected override void Generate(System.Data.Entity.Migrations.Model.RenameIndexOperation renameIndexOperation)
        {
            string tableName = _GetNameWithReplacedSchema(renameIndexOperation.Table);
            var newRenameIndexOperation = new System.Data.Entity.Migrations.Model.RenameIndexOperation(tableName, renameIndexOperation.Name, renameIndexOperation.NewName);
            base.Generate(newRenameIndexOperation);
        }
        protected override void Generate(System.Data.Entity.Migrations.Model.AddForeignKeyOperation addForeignKeyOperation)
        {
            addForeignKeyOperation.DependentTable = _GetNameWithReplacedSchema(addForeignKeyOperation.DependentTable);
            addForeignKeyOperation.PrincipalTable = _GetNameWithReplacedSchema(addForeignKeyOperation.PrincipalTable);
            base.Generate(addForeignKeyOperation);
        }
        protected override void Generate(System.Data.Entity.Migrations.Model.DropColumnOperation dropColumnOperation)
        {
            string newTableName = _GetNameWithReplacedSchema(dropColumnOperation.Table);
            var newDropColumnOperation = new System.Data.Entity.Migrations.Model.DropColumnOperation(newTableName, dropColumnOperation.Name, dropColumnOperation.AnonymousArguments);
            base.Generate(newDropColumnOperation);
        }
        protected override void Generate(System.Data.Entity.Migrations.Model.RenameColumnOperation renameColumnOperation)
        {
            string newTableName = _GetNameWithReplacedSchema(renameColumnOperation.Table);
            var newRenameColumnOperation = new System.Data.Entity.Migrations.Model.RenameColumnOperation(newTableName, renameColumnOperation.Name, renameColumnOperation.NewName);
            base.Generate(newRenameColumnOperation);
        }
        protected override void Generate(System.Data.Entity.Migrations.Model.DropTableOperation dropTableOperation)
        {
            string newTableName = _GetNameWithReplacedSchema(dropTableOperation.Name);
            var newDropTableOperation = new System.Data.Entity.Migrations.Model.DropTableOperation(newTableName, dropTableOperation.AnonymousArguments);
            base.Generate(newDropTableOperation);
        }
        protected override void Generate(System.Data.Entity.Migrations.Model.DropForeignKeyOperation dropForeignKeyOperation)
        {
            dropForeignKeyOperation.PrincipalTable = _GetNameWithReplacedSchema(dropForeignKeyOperation.PrincipalTable);
            dropForeignKeyOperation.DependentTable = _GetNameWithReplacedSchema(dropForeignKeyOperation.DependentTable);
            base.Generate(dropForeignKeyOperation);
        }
        protected override void Generate(System.Data.Entity.Migrations.Model.DropIndexOperation dropIndexOperation)
        {
            dropIndexOperation.Table = _GetNameWithReplacedSchema(dropIndexOperation.Table);
            base.Generate(dropIndexOperation);
        }
        private string _GetNameWithReplacedSchema(string name)
        {
            string[] nameParts = name.Split('.');
            string newName;
            switch (nameParts.Length)
            {
                case 1:
                    newName = string.Format("{0}.{1}", _schema, nameParts[0]);
                    break;
                case 2:
                    newName = string.Format("{0}.{1}", _schema, nameParts[1]);
                    break;
                case 3:
                    newName = string.Format("{0}.{1}.{2}", _schema, nameParts[1], nameParts[2]);
                    break;
                default:
                    throw new NotSupportedException();
            }
            return newName;
        }
    }
}

这就是我如何使用SqlServerSchemaAwareMigrationSqlGenerator：

// Update TenantDataCtx
var tenantDataMigrationsConfiguration = new DbMigrationsConfiguration<TenantDataContext.TenantDataCtx>();
tenantDataMigrationsConfiguration.AutomaticMigrationsEnabled = false;
tenantDataMigrationsConfiguration.SetSqlGenerator("System.Data.SqlClient", new SqlServerSchemaAwareMigrationSqlGenerator(schemaName));
tenantDataMigrationsConfiguration.SetHistoryContextFactory("System.Data.SqlClient", (existingConnection, defaultSchema) => new HistoryContext(existingConnection, schemaName));
tenantDataMigrationsConfiguration.TargetDatabase = new System.Data.Entity.Infrastructure.DbConnectionInfo(connectionString, "System.Data.SqlClient");
tenantDataMigrationsConfiguration.MigrationsAssembly = typeof(TenantDataContext.TenantDataCtx).Assembly;
tenantDataMigrationsConfiguration.MigrationsNamespace = "TenantDataContext.Migrations.TenantData";
DbMigrator tenantDataCtxMigrator = new DbMigrator(tenantDataMigrationsConfiguration);
tenantDataCtxMigrator.Update();

来自德国的问候，

托比亚斯

好吧，如果不是首先是代码，我将尝试这样做：

在默认架构中生成表，例如 DBO
基于现有数据库生成 EDMX
以 EF 和 POCO 作为起始 TT 模板
编辑 TT 文件并将新属性添加到名为 schema 的上下文中，并将强制生成的类中的查询将其用于数据库对象。

这样，您将能够为不同的架构创建上下文，甚至允许对象在上下文之间飞来飞去。

非常好的方法，它帮助我获得了更直接的解决方案。您只能覆盖名称方法，它在每个编写器中都使用....很抱歉有新的答案，但我不允许发表评论....

public class SqlServerSchemaAwareMigrationSqlGenerator:SqlServerMigrationSqlGenerator
{
    private string _schema;
    public accountMigrationSqlGenerator(string schema)
    {
        _schema = schema;
    }
    protected override string Name(string name)
    {
        int p = name.IndexOf('.');
        if(p>0)
        {
            name = name.Substring(p + 1);
        }
        return $"[{_schema}].[{name}]";
    }
}

感谢：托比亚斯！你救了我几年...

我在EF 6下对Oracle DB的修改：

public class IntegrationDbContext : DbContext, IDbModelCacheKeyProvider
{
    private static readonly ILog __log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
    /// <summary>
    /// Factory method
    /// </summary>
    public static IntegrationDbContext Create(string connectionStringName)
    {
        return new IntegrationDbContext(connectionStringName, GetDBSchema(connectionStringName));
    }
    /// <summary>
    /// Constructor
    /// </summary>
    public IntegrationDbContext()
    {
        Database.SetInitializer<IntegrationDbContext>(null);
    }
    /// <summary>
    /// Constructor
    /// </summary>
    internal IntegrationDbContext(string connectionString, string schemaName)
        : base("name={0}".Fill(connectionString))
    {
        Database.SetInitializer<IntegrationDbContext>(null);
        SchemaName = schemaName;
    }
    /// <summary>
    /// DB schema name
    /// </summary>
    public string SchemaName { get; private set; }
    #region Tables
    /// <summary>
    /// Integration table "SYNC_BONUS_DISTRIBUTION"
    /// </summary>
    public virtual DbSet<SYNC_BONUS_DISTRIBUTION> SYNC_BONUS_DISTRIBUTION { get; set; }
    /// <summary>
    /// Integration table "SYNC_MESSAGE_DISTRIBUTION"
    /// </summary>
    public virtual DbSet<SYNC_MESSAGE_DISTRIBUTION> SYNC_MESSAGE_DISTRIBUTION { get; set; }
    /// <summary>
    /// Integration table "IMPORT_TEMPLATES"
    /// </summary>
    public virtual DbSet<IMPORT_TEMPLATE> IMPORT_TEMPLATES { get; set; }
    #endregion //Tables
    private static Dictionary<string, string> __schemaCache = new Dictionary<string, string>();
    private static object __schCacheLock = new object();
    /// <summary>
    /// Gets DB schema name from connection string, or default from config
    /// </summary>
    private static string GetDBSchema(string connectionStringName)
    {
        string result;
        if (!__schemaCache.TryGetValue(connectionStringName, out result))
        {
            lock (__schCacheLock)
            {
                if (!__schemaCache.TryGetValue(connectionStringName, out result))
                {
                    DbConnectionStringBuilder builder = new DbConnectionStringBuilder();
                    builder.ConnectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
                    result = builder.ContainsKey("User ID") ? builder["User ID"] as string : ConfigurationManager.AppSettings["DefaultIntegrationSchema"];
                    __schemaCache.Add(connectionStringName, result);
                }
            }
        }
        return result;
    }
    /// <summary>
    /// Context initialization
    /// </summary>
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        __log.DebugFormat("OnModelCreating for integration model in schema: {0}", SchemaName);
        if (SchemaName != null)
        {
            modelBuilder.HasDefaultSchema(SchemaName);
        }
        //### CLOB settings
        modelBuilder.Properties().Where(p => p.PropertyType == typeof(string) &&
                                             p.GetCustomAttributes(typeof(MaxLengthAttribute), false).Length == 0)
                                                .Configure(p => p.HasMaxLength(2000));
        base.OnModelCreating(modelBuilder);
    }
    /// <summary>
    /// Implementation of <see cref="IDbModelCacheKeyProvider.CacheKey"/> - thanks by this is 'OnModelCreating' calling for each specific schema.
    /// </summary>
    public string CacheKey
    {
        get { return SchemaName; }
    }
}