Table Splitting in code first entity framework

A significant benefit to using Entity Framework is that your entity model does not have to match your database model. One of the ways this is accomplished is by using Table Splitting, which is having two entities in your model map to the same physical database table.

An example of using table splitting would be if you have a Customer entity that contains details about a Customer (first name, last name, e-mail, etc.) and a separate entity to store their address. The database table might store all of this information in a single physical table, but we might not want to pull back the Address every time we want the Customer information.

To accomplish this scenario in Code First you will need the following two entity classes and DbContext configuration:

public class Customer
{
    public int CustomerID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string EmailAddress { get; set; }
    public CustomerAddress Address { get; set; }
}

public class CustomerAddress
{
    public int CustomerID { get; set; }
    public string Line1 { get; set; }
    public string Line2 { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string ZipCode { get; set; }
    public Customer Customer { get; set; }
}

public class TableSplittingContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity()
            .HasKey(pk => pk.CustomerID)
            .ToTable("Customers");

        modelBuilder.Entity()
            .HasKey(pk => pk.CustomerID)
            .ToTable("Customers");

        modelBuilder.Entity()
            .HasRequired(p => p.CustomerAddress)
            .WithRequiredPrincipal(c => c.Customer);
    }
    public IDbSet Customers { get; set; }
}

The database that gets generated from this model will have a single table in it that looks like the following:

Now when you write a query to retrieve a Customer it will only retrieve columns related to the Customer and not the address. If you retrieve a Customer with the Address it will generate a query that selects both sets of columns. Here is an example of both of those queries and the resulting SQL queries that get generated:

using(var db = new TableSplittingContext())
{
    var customers = db.Customers.ToList();
    var customersAndAddress = db.Customers.Include(c => c.Address).ToList();
}

-- Query 1
SELECT
  [Extent1].[CustomerID] AS [CustomerID],
  [Extent1].[FirstName] AS [FirstName],
  [Extent1].[LastName] AS [LastName]
FROM [dbo].[Customers] AS [Extent1]

-- Query 2
SELECT
  [Extent1].[CustomerID] AS [CustomerID],
  [Extent1].[FirstName] AS [FirstName],
  [Extent1].[LastName] AS [LastName],
  [Extent1].[Line1] AS [Line1],
  [Extent1].[Line2] AS [Line2],
  [Extent1].[City] AS [City],
  [Extent1].[State] AS [State],
  [Extent1].[ZipCode] AS [ZipCode]
FROM [dbo].[Customers] AS [Extent1]

As you can see the first query only pulls the relevant Customer details and the second query pulls both the Customer and Address columns, all coming from a single table. This allows the queries that do not need Address information to be more efficient. In a future post I will talk about Entity Splitting, which is the opposite of table splitting in that two tables map to a single Entity in the model.