Entity Splitting in Code First Entity Framework

In my last post I talked about a mapping option in Entity Framework called table splitting. Table splitting allows you to take a single physical table and split it into multiple entities in your Entity Framework model. Today I will talk about another modeling option called entity splitting.

Entity splitting gives you the ability to take a single entity in your model and split it into multiple physical database tables. It is essentially the opposite of table splitting. When you query this entity in Entity Framework, it will create a query that automatically joins to the tables for you. Consider the following entity in Code First:

public class User {
    // these fields come from the Users table
    public int UserId { get; set; }
    public string Username { get; set; }

    // these fields come from the UserProfiles table
    public string FavoriteColor { get; set; }
    public string EmailAddress { get; set; }
}

The above model stores information about a User in our system. The UserId and Username properties will be stored in the Users table, and the EmailAddress and FavoriteColor properties will be stored in the UserProfiles table. Without adding the configuration, Code First will use the default conventions to create a single table (dbo.Users) where all 4 properties would be stored. Let's change that by overriding the default conventions:

public class EntitySplittingContext : DbContext
{
    public IDbSet Users { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity()
            // Map to the Users table
            .Map(map =>
            {
                map.Properties(p => new { p.UserId, p.Username });
               map.ToTable("Users");
            })
            // Map to the Users table
            .Map(map =>
            {
                map.Properties(p => new { p.FavoriteColor, p.EmailAddress });
                map.ToTable("UserProfiles");
            });
    }
}

We use two Map statements to select which properties go to the Users table and which properties go to the UserProfiles table. Entity Framework will create two tables in the database with a one-to-one relationship. Since we chose to have UserId belong to the Users table, it will treat that table as the Parent table. The UserProfiles table will also contain a UserId primary key column but it will not be an identity and will contain a foreign key reference back to the Users table.

As I mentioned before, when we query the Users entity the query that is generated for us will automatically perform a join between the two tables. Here is a query that demonstrates this behavior:

using(var db = new EntitySplittingContext())
{
    var users = db.Users.Where(u => u.Username == "Travis.Ellis").ToList();
}

-- SQL Query
SELECT
  [Extent1].[UserId] AS [UserId],
  [Extent1].[Username] AS [Username],
  [Extent2].[FavoriteColor] AS [FavoriteColor],
  [Extent2].[EmailAddress] AS [EmailAddress]
FROM [dbo].[Users] AS [Extent1] INNER JOIN [dbo].[UserProfiles] AS [Extent2]
  ON [Extent1].[UserId] = [Extent2].[UserId]
WHERE N'Travis.Ellis' = [Extent1].[Username]