EF 4.1 Multi-Tenant with Code First

Posted on May 23, 2011. Filed under: Entity Framework | Tags: , , |

After a long break while we got EF 4.1 (Magic Unicorn Edition) polished up and shipped it felt like time to get some content up on here again. We’ve released some good introductory content for EF 4.1 on the MSDN Data Developer Center so I thought I’d dive into something a little more advanced.

The default Code First workflow that is seen in most walkthroughs gently encourages you to write your model configuration code in a way that takes advantage of inbuilt model caching. This is a good thing because the pipeline that processes conventions, data annotations and fluent API calls is pretty expensive. The model for a derived context type is cached at the AppDomain level and is built on the assumption that the context is always used to access the same set of tables. This is fine for most folks but if you have a multi-tenant database then you may have code executing in a single AppDomain that wants to use the same Code First context to access different groups of tables representing multiple instances of the same model.

 

The Model

The model we will be working with is very simple:

public class Person 
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<ContactInfo> ContactInfo { get; set; }
}

public class ContactInfo 
{
    public int Id { get; set; }
    public string Details { get; set; }

    public int PersonId { get; set; }
    public virtual Person Person { get; set; }
}

We are going to use different database schemas to distinguish each tenant. An example database with two tenants looks something like this:

Schema

 

The API

The public surface of our API is shown below, we have a derived context and two static methods that allow us to provision a new tenant and create a context to access a given tenant.

public class ContextContext : DbContext 
{
    public DbSet<Person> People { get; set; }
    public DbSet<ContactInfo> ContactInfo { get; set; }

    /// <summary>
    /// Creates a context that will access the specified tenant
    /// </summary>
    public static ContextContext Create(string tenantSchema, DbConnection connection)
    {
        // TODO: Implement
    }

    /// <summary> 
    /// Creates the database and/or tables for a new tenant 
    /// </summary> 
    public static void ProvisionTenent(string tenantSchema, DbConnection connection)
    {
        // TODO: Implement
}
}

 

The Implementation

Here is a complete code listing of our context, the implementation is explained in the next section.

public class ContactContext : DbContext
{
    private ContactContext(DbConnection connection, DbCompiledModel model)
        : base(connection, model, contextOwnsConnection: false)
    { }

    public DbSet<Person> People { get; set; }
    public DbSet<ContactInfo> ContactInfo { get; set; }

    private static ConcurrentDictionary<Tuple<string, string>, DbCompiledModel> modelCache
        = new ConcurrentDictionary<Tuple<string, string>, DbCompiledModel>();

    /// <summary>
    /// Creates a context that will access the specified tenant
    /// </summary>
    public static ContactContext Create(string tenantSchema, DbConnection connection)
    {
        var compiledModel = modelCache.GetOrAdd(
            Tuple.Create(connection.ConnectionString, tenantSchema),
            t =>
            {
                var builder = new DbModelBuilder();
                builder.Conventions.Remove<IncludeMetadataConvention>();
                builder.Entity<Person>().ToTable("Person", tenantSchema);
                builder.Entity<ContactInfo>().ToTable("ContactInfo", tenantSchema);

                var model = builder.Build(connection);
                return model.Compile();
            });

        return new ContactContext(connection, compiledModel);
    }

    /// <summary>
    /// Creates the database and/or tables for a new tenant
    /// </summary>
    public static void ProvisionTenant(string tenantSchema, DbConnection connection)
    {
        using (var ctx = Create(tenantSchema, connection))
        {
            if (!ctx.Database.Exists())
            {
                ctx.Database.Create();
            }
            else
            {
                var createScript = ((IObjectContextAdapter)ctx).ObjectContext.CreateDatabaseScript();
                ctx.Database.ExecuteSqlCommand(createScript);
            }
        }
    }
}

 

The Explanation

What exactly is that code doing?

  • Constructor
    • We’ve made the constructor private to force consumers to use the Create factory method.
    • We are using the base constructor that accepts a compiled model and connection, this bypasses the usual OnModelCreating workflow and forces the context to run with the specified model and connection.
    • The contextOwnsConnection flag indicates whether or not the connection should be disposed when the context is disposed. You’ll see in the sample usage that I used the same connection instance to access multiple tenants so I don’t want this behavior.
  • Static modelCache Field
    • We already know that model creation is expensive so don’t want to recreate the model for every context instance. This field will be used to cache models based on the connection string and tenant schema.
    • Obviously the connection string based caching is a little flaky, feel free to substitute Server/Database name or whatever works in your scenario.
  • Static Create Method
    • This method houses the primary logic that allows us to target multiple tenants.
    • We check to see if there is a model already created to access the tenant on this connection string. If not we use the DbModelBuilder –> DbModel –> DbCompiledModel workflow to create a model. This workflow is usually handled internally by DbContext.
      • If you look at the API on DbModel you may be wondering what you can achieve by us having separated DbModel and DbCompiledModel… the answer in EF 4.1 is ‘absolutely nothing’! However, the longer term plan is to have DbModel be a mutable object model giving you much finer grain control once you are done with DbModelBuilder.
    • You’ll notice I am removing ‘IncludeMetadataConvention’ from the conventions, this removes the EdmMetadata table that Code First includes by default.
    • Provider information is required to build the final model because the database part will vary depending on the database provider being used. There is an overload of DbModel.Build that will accept raw provider information but we are using a handy overload that will work out these things based on the DbConnection.
    • 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.
  • Static ProvisionTenant Method
    • This is pretty self explanatory, if the database doesn’t exist then we create it along with the schema for the new tenant. If the database does exist then we pull out the create script and run it against the existing database.

 

The Usage

Does it work? Of course…

using (var connection = new SqlConnection(@"Server=.\SQLEXPRESS;Database=Contacts;Trusted_Connection=True;"))
{
    ContactContext.ProvisionTenant("personal", connection);
    ContactContext.ProvisionTenant("work", connection);

    using (var ctx = ContactContext.Create("personal", connection))
    {
        ctx.People.Add(new Person { Name = "Rowan Miller" });
        ctx.SaveChanges();
    }
}

 

The Problem

Database schemas work fine as a way to differentiate each tenant. Another common practice is to use table prefixes and this hits a bit of an issue. Code First generates names for things such as foreign key constraints and there is no way to change these names. The generated names are often based on the entity type names and you end up with conflicts between two tenants. Unfortunately the only solution at this stage is to manipulate the generated sql script before executing it in the ProvisionTenant method… admittedly that is a pretty ugly solution.

 

The Summary

You can use Code First to access a multi-tenant database that uses database schemas to differentiate between each tenant. Table prefixes are possible but requires some ugly code.



About these ads

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

27 Responses to “EF 4.1 Multi-Tenant with Code First”

RSS Feed for RoMiller.com Comments RSS Feed

Hi,
This sounds very interesting.
I have a quick question.. How would you implement custom tenant defined fields for each entity using EF 4.1?

Hello, I am a passionate c# developer i would like to talk to you on the direction ef-codefirst is going to. Where can i email you directly?

Would it be easy to move a row from one schema to another? if so, I think this may be a fairly nice way to provide softdelete functionality.

great but two questions:
Why is the “caching by connection” considered a bit flakey – you mention using domain or server, can you elaborate on the “whys and hows” of makign these decisions? ie what would be a practical example of why you would use something over connection string, I’m guessing distributed n-teir something but like to have a solid example.

Why does this force to specify a connection rather than the convention type stuff (eg: “name=MyDBConnection”) provided in most CF example stuff… is this a necessary evil of this “workaround” or will this still work “base(connection, model, contextOwnsConnection: false)”? (My guess is that its something to do with the model construction “getting” the provider (eg: SqlClient) so it knows how to build the specific underlying functionality, and maybe a the factory time, it can’t “see” the ConfigurationManager connection string stuff, but I haven’t time to play with it)

Relatively new to it but loving EF4.1 keep up the good (great) work!

@Nic – I think the answer is because two connection strings can be semantically equivalent without being lexically equivalent…i.e. reorder the parameters, add a space or a double-delimiter and you still have the same connection to the same database but the comparison in this case would fail (hence the ‘flaky’ comment). Clearly your suggestion about using the name instead of the value would solve the problem if it were possible (I don’t have an answer to that one).

@Emile – We’re all passionate about this stuff…I’d vote to keep the conversations out in the open so we can all benefit from them. No offense intended but “If I could just get him alone I’m sure I could talk some sense into him” is not likely to be a successful strategy.

First off bare with I’m new to EF. I tried this example with EF 4.3 and I’m getting the following error when the second tenant is created.
The model backing the ‘ContactContext’ context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).
Any idea what the problem is?

@Steve
I had same problem and solved including the following in the dbContext constructor:

public ContactContext(DbConnection connection, DbCompiledModel model)
: base(connection, model, false)
{
Database.SetInitializer(null);
}

That solved the problem, though not sure if that’s the best solution.

Yep, same problem here.
EdmModelDiffer now used over IncludeMetadataConvention probably has something to do with this… Hard to understand how/where its considering that the model is different, because my model hasn’t changed since upgrade.
I suspect its got a lot to do with the fact that the __MigrationHistory table does have a Model – it’s not clear if this model is “A” or “B”, so for instance, there is probably some way to make this work with ONE model and multiple instances of that model (as Alvaro has managed to get working, but hasn’t work for me) plus there is no clear way to have two models housed in the one database… unless the Model field is somehow able to “unhash” the context name in its cleverness…

Ahh… Alvaro’s solution DOES work, to enable this solution (multi-tennants) but not if you have another (core non-tennanted) models hitting the same database that also has migrations enabled…
It seems that when you set the initalizer to null it does stop the requirement for the model to “reinitlize” (thus allowing multiple tennants to share the same model), but if there is a _MigrationHistory table present then migrations seems to still check the model validity.
Not sure that makes much sense, but the long and the short of it is that you probably can’t have two models, one using migrations and one not, as it doesn’t seem to have an “Off” switch.

This is awesome – exactly what I was looking for! Would you happen to have time to create a very simple sample application using ASP.NET MV4 & EF5 using this technique and share with us? Thank you !!

Cant we use same for Model first approach?

Do you know how you would go about this for multiple databases?

Jim, did you get this working with multiple databases?

Assuming “The Usage” subsection could be some internal method exposed via service, for example. How would one unit test it than? with static method Create?

Really interesting article.
What’s the best practice to create new tenant when we have a model with tables shared between different tenant?
Suggested code try to create also the existing table and foreign keys. Actually i use migration to generate two migration one first shared objects and one for tenant objects. It there a best practis to do this or the best way is to lunch a full creation script ignoring the sql error?

I need to implement multi-tenancy using Entity Framework 5.0 databasefirst and single database multiple schemas.
How can i do this ?

In my scenario, each tenant is in a separate database (same SQL Server instance) rather than schema and I need transactions to span tenants.

I originally tried to do it with multiple DbContext instances created with connection injection and separated with ChangeDatabase calls. I found that I couldn’t span the tenants because each DbContext _requires_ that the connection not be opened before use (so the first DbContext succeeds but the second fails).

I assume I’d hit the same limitation with your approach. Any ideas?

Any good way to create indexes for the new tenant ?

I’m guessing the new HasDefaultSchema property in EF 6 changes the recommended way to deal with multiple instances of the same model? If that’s the case, any chance of an update to this article?

Thanks,
Clint

Hey Clint,

The general approach would still be the same but you could just simplify the configuration code by using HasDefaultSchema (rather than individually configuring each entity).

~Rowan

Hi,
I would also welcome a quick statement if what is described in this great article is somehow “replaced” by functionality in EF6 (or maybe 7). I searched quite a while now, and also found some articles about the new features of EF6 regarding mutli-tenant, but they do not cover exactly this case (which I have to implement in my SAAS web app).
Thanks!

Hey Volker,

This approach is still applicable to the more recent releases of EF – including EF6.

~Rowan

Was it ever determined if you can have the same application and database but a different connection string per user?

Hey Ken,

Yes absolutely. Probably the easiest way is to determine the connection string externally and then use the DbContext constructor that accepts a connection string.

~Rowan


Where's The Comment Form?

    About

    Rowan works as a Program Manager for the ADO.NET Entity Framework team at Microsoft. He speaks at technical conferences and blogs at romiller.com. Rowan lives in Seattle, Washington with his wife Athalie. Prior to moving to the US he resided in the small state of Tasmania in Australia. Outside of technology Rowan's passions include snowboarding, mountain biking, horse riding, rock climbing and pretty much anything else that involves being active. The primary focus of his life, however, is to follow Jesus.

    RSS

    Subscribe Via RSS

    • Subscribe with Bloglines
    • Add your feed to Newsburst from CNET News.com
    • Subscribe in Google Reader
    • Add to My Yahoo!
    • Subscribe in NewsGator Online
    • The latest comments to all posts in RSS

    Meta

Liked it here?
Why not try sites on the blogroll...

Follow

Get every new post delivered to your Inbox.

Join 162 other followers

%d bloggers like this: