Exploding Kitten's Blog

How to map a table to two entities in EF Core

May 08, 20244 min read

Hello there.

EF Core supports the mapping of a single table to multiple entities and vice-versa. In the current article, I will explain how to map a table to multiple entities and what problems this could cause.

The configuration for such kind of mapping is pretty straightforward and even the official documentation has an example of how to do it and it is called table splitting.

The table splitting is useful when you need to use two entities (mapped to the same entity) but one entity is a subset of another one.

In our use case, we used table splitting to split our entities between different domains in a legacy application. We have an existing database schema and we can’t change it because there is a legacy application. So, any change is a breaking change. But to start introducing “modules”/“domains” in a new application, we decided to use the existing schema and map it to a different model. Basically, one entity is a “read-write” model and a source of truth and any other model mapped to the same table is a read-only model.

Whereas in the new application, we would design it as a set of completely different entities. For example (code from Microsoft example below), the Orders domain has a primary write model with all necessary properties, nested entities, etc. This domain is “responsible” for this entity, it is a source of truth. But at the same time we need to use it in a different domain, let’s assume it’s a reporting domain. The reporting domain could read the information about orders from the Orders domain directly, each time when he needs it. But it creates a direct dependency between two domains, if Orders is down, then Reporting is also down. Also, this entity is from an external data source, we can’t control or even rely on it. Performance is also a consideration. To solve these “problems”, we can introduce a read-only Order model in our Reporting domain with a reduced set of properties and sync them between two domains by using messages (eventually).

Let’s take a look at Microsoft’s example:

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; }
}
 
// ...
 
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);
    });

It maps two entities: Order and DetailedOrder to the same Orders table and has an “unusual” mappings in the configuration:

ob.HasOne(o => o.DetailedOrder)
    .WithOne()
    .HasForeignKey<DetailedOrder>(o => o.Id);

It is required, otherwise EF Core will throw an exception because it doesn’t allow mapping several entities to a single table with one DbContext. With such configuration, EF Core will be able to query two entities but it will generate inefficient SQL code. It will have two problems. The first one is unnecessary CASE/WHEN statements, the following code is not exact SQL generated by EF Core, but you get the idea:

SELECT ...
    CASE 
        WHEN t1.Status IS NOT NULL t1.Status
        ELSE NULL
    END
FROM Orders t1

To fix it, you may need to include fk.IsRequiredDependent = true to your configuration, like this:

var fk = ob.HasOne(o => o.DetailedOrder)
    .WithOne()
    .HasForeignKey<DetailedOrder>(o => o.Id);
 
fk.IsRequiredDependent = true;

The table splitting has another performance problem. When you are trying to query the database, EF Core will generate additional join like so:

SELECT ...
FROM Orders t1
    INNER JOIN Orders t2 ON t1.ID = t2.ID

The official GitHub repository has a ticket to fix this problem but there are no estimates of when this might happen.

Recent Notes