Entity Framework and Migration with multiple schemas in a single Context

c# ef-code-first entity-framework entity-framework-6

Question

I am developing a multi-tenant application with Entity Framework Code First. Each tenant will have a different schema in the database, but the application will have a single Context and model for all tenants.

The Entity Framwork 6 is able to use multiple schemas with multiple contexts in the same database, but I didn’t find a way to use multiple schemas with a single Context.

I have generated migrations (by command line) to the default “dbo” schema. I would like to update other schemas using these migrations.

Popular Answer

While I agree that multiple context is absolutely the better way to go (and is how I have my own projects setup), I wanted to answer your original question of how to use multiple schemas in a single context:

Inside of your mapping configuration for each model you can call 'ToTable(myTableName, mySchema)' to modify the schema to which a table belongs:

public class MyEntityMap : EntityTypeConfiguration<MyEntity>
{
    public MyEntityMap ()
    {
        HasKey(t => t.MyId);
        Property(t => t.MyId)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        ToTable("MyEntity", "MySchema");
    }
}

This will allow you to set the schema for each table separately while maintaining a single context.

Since you stated that you want to use the same model in different schemas that makes it a little more difficult without knowing more about your setup. If you are dealing only with a handful of customers and don't mind maintaining their schemas in code then you can simply create a map for each schema (as above) and then add a new DbSet for each customer. If you are trying to make this scalable up to a large number of customers then I would highly suggest looking into a different approach because your dba might scream when he sees 100+ identical tables in different schemas as opposed to using a customerID column on each table.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why