Multi-Tenant With Code First EF6

Question

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

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:

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.

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)

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

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.

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

Answer 1

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

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

And this is how I use the 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,

Tobias

Answer 2

Well, if it would not be code first, I will try to do it this way:

• generate tables in default schema, say dbo

• generate edmx based on existing database

• take EF with POCO as starting TT template

• edit TT file and add new property to context called schema and will force the queries in generated classes to use it for database objects.

This way you will be able to create contexts for different schemas and even allow objects to fly around between contexts.

Answer 3

very nice approach and it helped me to get a more straight forward solution. You may override only the Name-Method, it is used in every writer .... Sorry for new answer, but i'm not allowed to comment ....

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}]";

    }

}

Answer 4

Thanks to: Tobias! You save me a years...

My modification for Oracle DB under EF 6:

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