Entity Modeling

Your e-commerce system has different types of products: physical products, digital downloads, and subscriptions. Each type has common properties (name, price) and specific properties (shipping weight for physical, download URL for digital, billing frequency for subscriptions).

How you map this inheritance hierarchy to SQL Server tables dramatically affects query performance, storage efficiency, and maintainability.

The Three Inheritance Strategies

EF Core provides three approaches to map inheritance hierarchies to relational databases:

Table-per-Hierarchy (TPH)

All types stored in a single table with a discriminator column

Table-per-Type (TPT)

Shared properties in base table, specific properties in derived tables

Table-per-Concrete-Class (TPC)

Each concrete type gets its own table with all properties

Let’s examine each approach with a practical example.

Domain Model Setup

public abstract class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string Description { get; set; }
    public DateTime CreatedAt { get; set; }
    public bool IsActive { get; set; }
}

public class PhysicalProduct : Product
{
    public double Weight { get; set; }
    public string Dimensions { get; set; }
    public decimal ShippingCost { get; set; }
    public int StockQuantity { get; set; }
}

public class DigitalProduct : Product
{
    public string DownloadUrl { get; set; }
    public long FileSizeBytes { get; set …

Your Customer entity has 47 properties. Most queries only need the basic information: name, email, and contact details. But every time you load a customer, EF Core pulls all 47 columns from the database, including the large ProfileData JSON blob that’s rarely used.

This is where table splitting can help: split your large entity into multiple classes that map to the same table, loading only what you need when you need it.

But table splitting is a double-edged sword. Use it wrong, and you’ll make performance worse, not better.

Understanding Table Splitting

Table splitting maps multiple entity classes to the same database table:

// Single table with many columns
CREATE TABLE Customers (
    Id int PRIMARY KEY,
    Name nvarchar(100),
    Email nvarchar(200),
    Phone nvarchar(20),
    -- Frequently accessed columns above
    
    ProfileData nvarchar(max),
    Preferences nvarchar(max), 
    MarketingMetadata nvarchar(max),
    LastLoginDetails nvarchar(max),
    -- Rarely accessed columns below
    CreatedAt datetime2,
    ModifiedAt datetime2
);

Instead of one large entity, you create multiple focused entities:

// Frequently accessed data
public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    
    // Navigation to extended data
    public CustomerExtended Extended { get; set; }
}

// Infrequently accessed data
public class CustomerExtended
{ …

A few years back, I spent a solid week chasing a weird performance bug. Queries that should have taken milliseconds were sometimes taking seconds to run. The culprit? A simple string property on a core entity. Nobody had set a max length, so EF Core defaulted it to nvarchar(max). SQL Server handles those columns differently, and it was wrecking our query plans.

This one burned me in production, and it taught me a hard lesson: your entity model is the foundation of your application’s performance. Get it wrong, and you’ll pay for it with slow queries and painful migrations.

This checklist is the result of modeling dozens of schemas for EF Core on SQL Server. I’m focusing on SQL Server here because its defaults and indexing behaviors are unique.

Getting the Basics Right: The Entity Itself

Let’s start with the entity class. If you mess this up, everything else gets more complicated.

• Primary Keys: Just use Id or [EntityName]Id. Don’t get clever. I avoid composite keys unless the domain model is impossible without them. They make joins and repository logic a pain to write and maintain.

  // Good: Simple, clean, effective.
  public int Id { get; private set; }
  
  // Avoid: This complicates everything.
  public class OrderItemKey
  {
      public int OrderId { get; set; }
      public int ProductId { get; set; }
  }
  

• Value Objects: For data that has no identity on its own, use Owned Types. An Address or a Money value doesn’t need its own Id. It belongs to its parent. This …