多租户用code首先EF6 [英] Multi-Tenant With Code First EF6

问题描述

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

Our organization has need to have a single database, multi-tenant
(by table schema, not by tenant id) architecture.

这里有一个伟大的文章上开始使用这种事情在这里：
<一href=\"http://romiller.com/2011/05/23/ef-4-1-multi-tenant-with-$c$c-first/\">http://romiller.com/2011/05/23/ef-4-1-multi-tenant-with-$c$c-first/

There is a great article here on getting started with this kind of thing here: http://romiller.com/2011/05/23/ef-4-1-multi-tenant-with-code-first/

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

In the middle of the article, this is written:

您会注意到（可能与有些不舍），我们需要写code到
  配置每个实体的表模式。诚然，也有不
  许多神奇的独角兽解决此code放牧......在英孚的未来版本
  我们将能够以更清洁的定制公约来取代这一点。

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）

Our goal is to have the cleanest way possible of having a single context class that we can use to connect to multiple schemas that have the same model.
(Note that modelBuilder.HasDefaultSchema doesn't seem sufficient, because it only applies that the first time EF initializes the context and runs OnModelCreating)

在是否EF5或EF6上述清洁定制的约定存在吗？结果
还是有以某种方式处理这一个更清洁的方式？

Does the aforementioned cleaner custom convention exist in EF5 or EF6?
Or is there a cleaner way to handle this somehow?

注：我也问上发展论坛这个问题，因为它似乎更多地涉及到EF的方向，但想看看是否有人在这里有替代品

Note: I also asked this question on the development forum, as it seems to relate more to the direction of EF, but wanted to see if anybody here had alternatives.

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

Note2: I'm not worried about migrations, we'll handle that seperately.

推荐答案

在 OnModelCreating 属性 modelBuilder.HasDefaultSchema 足够如果您实施 IDbModelCacheKeyProvider 在的DbContext 。一个模型创建一次，比 EntityFramwork 内部缓存，你可以定义自己的高速缓存键。就拿架构名称为模型缓存键和EF将每个不同的缓存键（在我们的案例模式）创建一个模型。这是我的理念code证明：

The property modelBuilder.HasDefaultSchema in OnModelCreating is sufficient if you implement IDbModelCacheKeyProvider on your DbContext. A model is created once and than cached internally by EntityFramwork and you can define your own key for the cache. Take the schema name as model cache key and EF will create a model by every different cache key (schema in our case). Here is my proof of concept code:

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迁移。我不是用我的解决方案，真的很高兴，但似乎没有可用的其它解决方案现在。

Furthermore I have found a way to use EF migrations. I am not really happy with my solution but it seems that there are no other solutions available right now.

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

来自德国的问候，

Regards from Germany,

托比亚斯

这篇关于多租户用code首先EF6的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！