Table Splitting

EF Core allows to map two or more entities to a single row. This is called table splitting or table sharing.

Configuration

To use table splitting the entity types need to be mapped to the same table, have the primary keys mapped to the same columns and at least one relationship configured between the primary key of one entity type and another in the same table.

A common scenario for table splitting is using only a subset of the columns in the table for greater performance or encapsulation.

In this example Order represents a subset of DetailedOrder.

public class Order
{
    public int Id { get; set; }
    public OrderStatus? Status { get; set; }
    public DetailedOrder DetailedOrder { get; set; }
}
public class DetailedOrder
{
    public int Id { get; set; }
    public OrderStatus? Status { get; set; }
    public string BillingAddress { get; set; }
    public string ShippingAddress { get; set; }
    public byte[] Version { get; set; }
}

In addition to the required configuration we call Property(o => o.Status).HasColumnName("Status") to map DetailedOrder.Status to the same column as Order.Status.

modelBuilder.Entity<DetailedOrder>(dob =>
{
    dob.ToTable("Orders");
    dob.Property(o => o.Status).HasColumnName("Status");
});

modelBuilder.Entity<Order>(ob =>
{
    ob.ToTable("Orders");
    ob.Property(o => o.Status).HasColumnName("Status");
    ob.HasOne(o => o.DetailedOrder).WithOne()
        .HasForeignKey<DetailedOrder>(o => o.Id);
});

Tip

See the full sample project for more context.

Usage

Saving and querying entities using table splitting is done in the same way as other entities:

using (var context = new TableSplittingContext())
{
    context.Database.EnsureDeleted();
    context.Database.EnsureCreated();

    context.Add(new Order
    {
        Status = OrderStatus.Pending,
        DetailedOrder = new DetailedOrder
        {
            Status = OrderStatus.Pending,
            ShippingAddress = "221 B Baker St, London",
            BillingAddress = "11 Wall Street, New York"
        }
    });

    context.SaveChanges();
}

using (var context = new TableSplittingContext())
{
    var pendingCount = context.Orders.Count(o => o.Status == OrderStatus.Pending);
    Console.WriteLine($"Current number of pending orders: {pendingCount}");
}

using (var context = new TableSplittingContext())
{
    var order = context.DetailedOrders.First(o => o.Status == OrderStatus.Pending);
    Console.WriteLine($"First pending order will ship to: {order.ShippingAddress}");
}

Optional dependent entity

Note

This feature was introduced in EF Core 3.0.

If all of the columns used by a dependent entity are NULL in the database, then no instance for it will be created when queried. This allows modeling an optional dependent entity, where the relationship property on the principal would be null. Note that this would also happen if all of the dependent's properties are optional and set to null, which might not be expected.

Concurrency tokens

If any of the entity types sharing a table has a concurrency token then it must be included in all other entity types as well. This is necessary in order to avoid a stale concurrency token value when only one of the entities mapped to the same table is updated.

To avoid exposing the concurrency token to the consuming code, it's possible the create one as a shadow property:

modelBuilder.Entity<Order>()
    .Property<byte[]>("Version").IsRowVersion().HasColumnName("Version");

modelBuilder.Entity<DetailedOrder>()
    .Property(o => o.Version).IsRowVersion().HasColumnName("Version");