c# - 使用 Code First EF6 的 Multi-Tenancy

Question

我们的组织需要有一个单一的数据库， Multi-Tenancy
(按表架构，而不是按租户 ID)架构。

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

文章中间，是这样写的:

You’ll notice (probably with some dismay) we need to write code to configure the table schema for each entity. Admittedly there aren’t many magic unicorns grazing around this code… in future versions of EF we will be able to replace this with a much cleaner custom convention.

我们的目标是尽可能以最简洁的方式拥有一个上下文类，我们可以使用它来连接到具有相同模型的多个模式。
(请注意，modelBuilder.HasDefaultSchema 似乎还不够，因为它仅适用于第一次 EF 初始化上下文并运行 OnModelCreating)

EF5 或 EF6 中是否存在上述更清洁的自定义约定？
还是有更简洁的方式来处理这个问题？

注意:我也在开发论坛上问过这个问题，因为它似乎与 EF 的方向有关，但想看看这里是否有人有其他选择。

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

Answer 1

OnModelCreating 中的属性 modelBuilder.HasDefaultSchema 就足够了 如果 您在 DbContext 上实现了 IDbModelCacheKeyProvider 。模型创建一次，然后由 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();

来自德国的问候，

托比亚斯