Schemas in Entity Framework

Schemas in Entity Framework Entity Framework 6 Ninja Edition as Julie Lerman terms it boasts several improvements from it’s predecessors but one improvement that gets little press is schema support. The ability to specify default schema for database mapping instead of always defaulting to dbo is an overlooked feature. Entity Framework 5 supports non-default schema but to do so requires more code than I’d prefer. The new default schema feature in Entity Framework 6 is nice, it supports more advanced database schemas for larger applications BUT more importantly reduces code and potential for coding errors. The idea of having to remap each table to the non-default schema and then ensuring all the relationship mappings are also pointing to the proper schema name is cumbersome to say the least. All this effort in EF 5/4 was thankfully reduced to a single line of code in Entity Framework 6.

For each entity definition override the schemaName using ToTable to desired schema then redefine the many-to-many relationships and use the Map method to outline the relationship to a table and schema name.

Override schemaName in DbModelBuilder
1
2
3
4
5
6
7
8
9
10
11
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customer>().ToTable("Customers", schemaName: "IRF");
    modelBuilder.Entity<Address>().ToTable("Addresses", schemaName: "IRF");
    modelBuilder.Entity<Order>().ToTable("SalesOrders", schemaName: "IRF");

    modelBuilder.Entity<Customer>()
    .HasMany(c => c.Addresses)
    .WithMany(a => a.Customers)
    .Map(m => m.ToTable("CustomerAddresses", schemaName: "Ordering"));
}

In EF 6 simply leverage the HasDefaultSchema property on the DbModelBuilder. Obviously this will not help modeling a database with mutliple schemas but at least for the smaller scenarios support comes out of the box.

Set HasDefaultSchema Property on DbModelBuilder
1
2
3
4
5
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema("IRF");
    ....
}

Comments