Working with Schema Names in Entity Framework Code-First Design

In my last column about Entity Framework's code-first design feature, I explained to readers about my session at PASS Summit 2011. One of the questions I didn't have time to explore in my session is how you can work with database schema names other than being the database owner (DBO). On one hand, the options aren't very obvious, but on the other it's a capability that would be hard to imagine the Entity Framework developers overlooking. Fortunately this capability wasn't overlooked, and after you see how the basic feature works, schema names become pretty easy to work with.

I'll use the same sample application from last month's column, Ordering.sln, which is a simple ordering system with Customer, Address, and Order entities. Customer and Address are wired to have a many-to-many relationship, and Customer and Order are wired for a one-to-many relationship. The code in Figure 1 defines those three classes in the OrderingModel.cs code file.

The OrderingContext class inherits from DbContext, and defines the object that code-first will use to implement all the rich Entity Framework features for these classes. Here's the code for the OrderingContext class that can be found in OrderingModel.cs:

public class OrderingContext : DbContext{    public DbSet Customers { get; set; }    public DbSet Addresses { get; set; }    public DbSet Orders { get; set; }} 

When you run the sample ASP.NET MVC application for the first time, code-first creates an Ordering.Models.OrderingContext database in a local instance of SQL Server Express (the sample application uses a lot of code-first defaults, but you can easily change the database location and name). Figure 2 shows the database schema that's created and displayed in SQL Server Management Studio. I've expanded the AddressCustomers table to show that it's a many-to-many relationship that links Customer and Address entities, with respective foreign keys as its only contents. Notice that the table created for the Order entity's data is named SalesOrders because the Order class has a Table attribute that changes the name.

Related:AppGen Is an Existential Threat to the Enterprise App Business

The sample application also creates an OrderingInitializer class that inherits from the DropCreateDatabaseIfModelChanges class to drop and recreate the database when the model changes. That class overrides the Seed method to populate the database with a few sample records.

At last we get to the question at hand: How can you control the schema names that code-first uses when it creates database objects? As with any code-first schema customization, you can do this by using the entity classes' attributes or through the DbModelBuilder API. With data annotations, you can use the optional second parameter of the Table attribute to specify the schema name. The code in Figure 3 implements this change in the model. Unfortunately the table name is a required parameter, so you have to specify that name even if it's the same as the entity name. When you run the application again, code-first updates the database schema to use the Ordering schema name, as Figure 4 shows.

Related:Accelerating Software Delivery: How AI Is Shaping the Future of Development

Updated Database Schema 

You can accomplish the same thing using the DbModelBuilder API by overriding the OnModelCreating method in the class that inherits from DbContext, which is OrderingContext in the sample application. The following code does the same thing as the attributes on the classes in the previous code:

protected override void OnModelCreating(DbModelBuilder modelBuilder){    modelBuilder.Entity().ToTable("Customers", schemaName: "Ordering");    modelBuilder.Entity().ToTable("Addresses", schemaName: "Ordering");    modelBuilder.Entity().ToTable("SalesOrders", schemaName: "Ordering");}

When you explicitly override the default table name, Entity Framework pluralization doesn't kick in. As a result, the table names used in the code are pluralized.

So far, so great. We now have the three model entities in the Ordering schema instead of the DBO. But notice that there's a problem in the resulting database schema. The AddressCustomers table that Entity Framework created to manage the many-to-many relationship between Customer and Address entities is still in the DBO schema. There doesn't seem to be a solution to this problem using attributes, probably because the problem involves more than one entity.

Related:Choosing the Right Tech Stack: The Key to Successful App Development

The solution is straightforward using the DbModelBuilder API, but the code is a bit convoluted. You essentially have to redefine the many-to-many relationship and use the Map method to outline the relationship to a table and schema name. The following code does the trick and should be placed in the overridden OnModelCreating method.

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

Voilà! Now, as Figure 5 shows, all the new objects are in the desired schema.

Final Schema 

Sometimes Entity Framework seems big, bloated, and imposing, but you have to admit that it has plenty of options for accomplishing what you need to do. There are usually multiple ways to accomplish a particular task, so it's worth exploring the different available options.

Figure 1: Defining Customer, Address, and Order Classes

public class Customer{    public int CustomerID { get; set; }    [MaxLength(20), Required]    public string FirstName { get; set; }    [MaxLength(30), Required]    public string LastName { get; set; }    [MaxLength(120), Required]    public string Company { get; set; }    [MaxLength(225)]    public string Email { get; set; }    [MinLength(10), MaxLength(15)]    public string Phone { get; set; }    [Column("FirstContact")]    public DateTime Created { get; set; }public virtual ICollection Addresses { get; set; }    public virtual ICollection Orders { get; set; }}public class Address{    public int AddressID { get; set; }    [MaxLength(50), Required]    public string Street { get; set; }    [MaxLength(30), Required]    public string City { get; set; }    [MaxLength(20), Required]    public string StateProvince { get; set; }    [MaxLength(10), Required]    public string PostalCode { get; set; }    public string Country { get; set; }public virtual ICollection Customers { get; set; }}[Table("SalesOrders")]public class Order{    public int OrderID { get; set; }    public DateTime OrderDate { get; set; }    public DateTime ShipDate { get; set; }    public decimal TotalOrder { get; set; }    public int ProductCount { get; set; }      public virtual Customer Customer { get; set; }    public virtual Address ShipToAddress { get; set; }    public virtual Address BillToAddress { get; set; }}

 Figure 3: Schema Customization Using Entity Class Attributes

[Table("Customers", Schema = "Ordering")]public class Customer{    ...}[Table("Addresses", Schema = "Ordering")]public class Address{    ...}[Table("SalesOrders", Schema = "Ordering")]public class Order{    ...}