Introduction

In EF Core (as in most O/RMs) we can map and store multiple things:

Objects that hold a single value, like int, string, bool, float, Guid, DateTime, TimeSpan, Uri, etc. These are (somewhat wrongly) called primitive types and are always included inside some container, like the following
Objects that hold multiple single values and which have an identity, for example, Customer, Product, Order. These are called entity types and they must have an identity that makes them unique amongst all the other objects of the same type
Objects that are structured to hold multiple values, but have no identity, such as Address, Coordinate. These are called value types and are merely a collection of (possibly related) properties
We can also have collections of the previous things

There's more to it, of course, for example, it is even possible to represent inheritance of entities, of which I talked about before,

In this post I am going to cover some modern/advanced mapping techniques for EF Core that may not be so well known. It assumes EF Core 10 and a database that supports JSON columns, such as SQL Server 2025. Some things will not work with older versions, like JSON persistence - a compatibility level of 170 or higher is required. Other databases, such as PostgreSQL, should work too.

I will be using singular table names for all tables, the same as for their related entity (e.g., Customer entity <=> Customer table), which means that each entity is persisted to a table of the same name, and will show only the minimum required code to illustrate a point.

Classic Mappings

Up until recently, EF Core would allow us to map standard (classic) relationships between entities only. The difference between entities and value types is:

Entities and Value Types

Both entities and value types are custom classes (or records) with properties or fields, but the difference between the two is, entities have an identity. This means that one or a combination of properties of an entity are unique in the data store where they live, in relational databases this is called a primary key. It is possible to query the data store for this entity's id and it shall return always the same entity (unless the data store is modified, of course). We cannot query by a value type, as it is a detail of a containing entity, does not exist on it's own and does not have any id. Not all O/RMs support the concept of value types, and until recently, EF Core didn't.

Standard relations in relational databases are:

One to Many

Each source entity can be related to many target entities. For example, one customer can have many orders. This is represented as:

public class Customer
{
    public int Id { get; set; }
    public List<Order> Orders { get; set; } = [];
}

This means that the table holding the Order entity will have a foreign key to the table holding Customer.

Many to One

Many source entities can reference the same target entity. For example. many orders belong to the same customer. We can represent this as:

public class Order
{
    public int Id { get; set; }
    public Customer Customer { get; set; }
}

As you know, this is exactly the opposite of one to many, and it means that the Order table will have a foreign key to the Customer table.

One to One

Each source entity can be related to a single target entity, which doesn't relate to any other source. For example, one customer has an address. An example code:

public class Customer
{
    public int Id { get; set; }
    public Address Address { get; set; }
}

public class Address
{
    public int Id { get; set; }
    public string Street { get; set; }   
    public string City { get; set; }
    public string Country { get; set; }
    public string POBox { get; set; }
}

This relation is very similar to many to one, and some people avoid it. It means that either the Customer table has a foreign key to the Address table or the Customer table has a foreign key to Address, depending on what exists first (can share the same key).

Many to Many

Each source entity can be related to many target entities, and, on their turn, each target entity can be related to many source entities. It's essentially two one to many/many to one put together. For example: one order can contain multiple products; a product can be part of many orders. In code:

public class Order
{
    public int Id { get; set; }
    public List<Product> Products { get; set; } = [];
}

public class Product
{
    public int Id { get; set; }
    public List<Order> Orders { get; set; } = [];
}

For many to many we need a third table to hold foreign keys to both the Order and Product tables. However, there is no need to map it to a class, unless it requires additional properties, in which case, the relations become two many to one.

Modern Mappings

With modern versions of EF Core (and relational databases), we can have more complex situations. One particular case is, when we want to use value types, meaning, classes without identity, which can be reused across the domain model. There are two possible approaches to this, using EF Core: complex properties and owned entities. The same .NET model can be mapped in many different ways to the database.

Complex Properties

Imagine for a second that we wish to use classes that are not entities, meaning, we don't care about their identity, just their values. From EF Core 8 onwards, we have complex properties and complex collections for this.

Let's suppose that we want to store an Address not as an entity, but only its values. If we only want to store one Address per Customer, we could have this:

public class Customer
{
    public int Id { get; set; }
    public Address Address { get; set; }
}

public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
    public string POBox { get; set; }
}

Notice the Id property of Address is gone, we don't need it.

And, if we need multiple Addresses, possibly of different types:

public class Customer
{
    public int Id { get; set; }
    public List<Address> Addresses { get; set; } = [];
}
    
public class Address
{
    public AddressType AddressType { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
    public string POBox { get; set; }
}

public enum AddressType
{
    Personal,
    Work,
    Other
}

Here we introduced an AddressType enumeration, let's assume that we can add multiple addresses to a single Customer, possibly all of different types, but this is not required.

Enter complex types. Complex types are the EF Core implementation of value types, and allow us to map values in our entities explicitly, and also collections of value types. The configuration for a single Address would be defined using ComplexProperty:

modelBuilder.Entity<Customer>()
    .ComplexProperty(x => x.Address);

What happens is, each of the properties of Address will be stored as a separate column in the Customer table.

And, for multiple Addresses, we use ComplexCollection instead:

modelBuilder.Entity<Customer>()
    .ComplexCollection(x => x.Addresses, options =>
    {
        options.ToJson();
    });

You may have noticed ToJson: indeed, complex collections need to be stored in single column, which must contain JSON - remember, here we do not have a foreign key to another table. The actual type is decided by the data store that we are using (e.g., SQL Server uses JSON or NVARCHAR(MAX)).

Owned Entities

Unlike complex types, owned entities can either be stored in a table separate from the owning entity's table or as a JSON column in the owning table. They can be entity types, with id semantics (even when that id is shadowed), if we store them in a different table, or they do not have an id value at all, if we store them as a JSON column.

So, let's pick on the previous example of a Customer with a single Address, we would configure it, using owned entity, with OwnsOne, as this:

modelBuilder.Entity<Customer>()
    .OwnsOne(x => x.Address);

Again, each property of Address will be stored as a separate column in the Customer table.

For having multiple Addresses, on the same table as Customer, we use OwnsMany instead:

modelBuilder.Entity<Customer>()
    .OwnsMany(x => x.Addresses, options =>
    {
        options.ToJson();
    });

Notice again the call to ToJson: we always need this if we are going to store a collection of objects inside the containing entity. In this case a single column will be used.

If we want to allow storing Addresses on a separate table, we call instead OwnsMany and also HasKey:

modelBuilder.Entity<Customer>()
    .OwnsMany(x => x.Addresses, options =>
    {
        options.HasKey("Id");  //required if not using JSON
    });

This way, a new table is created transparently (Address, by default) which is linked to the Customer table: Customer has a foreign key to Address.

The difference between complex properties and owned entities is that, complex properties are always stored on the same table as the containing entity, whereas owned entities may or may not require a separate table. On both cases, you cannot query by the complex/owned type, meaning, this waill fail:

ctx.Set<Address>().ToList(); //error: Address is not an entity

Collections of Primitive Types

In the old days, as was mentioned, it wasn't possible to store collections of primitive types. You could, of course, store them all in a text column and then use some value converter to turn the database value into the .NET property. Now, with primitive collections, EF Core takes care of this for us, so if we have, for example:

public class Product
{
    public int Id { get; set; }
    public List<string> Tags { get; set; } = [];
}

This just works, the Tags property is persisted automatically inside the Product table, plus it works with any primitive type. We do not need to know the details, but it is probably going to be stored as JSON, if the database supports it. And it can be queried too:

ctx.Products
    .Where(x => x.Tags.Contains("blue"))
    .ToList();

Entities from Views or SQL

It is also possible to map .NET classes from database views or raw SQL. The resulting entities, of course, must be read-only, and any attempt to persist them will result in an exception being throw.

For views, we use ToView:

modelBuilder.Entity<OrderCustomer>()
    .ToView("OrderCustomer")
    .HasNoKey();

HasNoKey is also required, it tells EF Core that there is no logical key on the returned entities and so the entities must be read-only: they are keyless entities. A simple view that joins Orders, Customers, and Products, could be defined as (for SQL Server):

CREATE VIEW dbo.OrderCustomer AS
SELECT 
    o.Timestamp AS OrderTimestamp, 
    c.Name AS CustomerName, 
    COUNT(op.OrderId) AS ProductCount
FROM dbo.Order o
INNER JOIN dbo.Customer c ON o.CustomerId = c.Id
LEFT JOIN dbo.OrderProduct op ON op.OrderId = o.Id
GROUP BY o.Timestamp, c.Name, o.Id

For using SQL, we call ToSqlQuery, using the same query previously defined:

modelBuilder.Entity<OrderCustomer>()
    .ToSqlQuery("SELECT o.CreationDate AS OrderCreationDate, c.Name AS CustomerName, COUNT(op.OrderId) AS ProductCount
                 FROM dbo.Order o
                 INNER JOIN dbo.Customer c ON o.CustomerId = c.Id
                 LEFT JOIN dbo.OrderProduct op ON op.OrderId = o.Id
                 GROUP BY o.CreationDate, c.Name, o.Id")
    .HasNoKey();

Both are keyless entities, same restrictions apply: we can query but not make modifications.

Final option is using some SQL function that returns the columns and records we need, we configure it with ToFunction:

modelBuilder.Entity<OrderCustomer>()
    .ToFunction("GetOrderCustomers");

Where the function could be (SQL Server):

CREATE FUNCTION dbo.GetOrderCustomers()
RETURNS TABLE
AS
RETURN
(
    SELECT 
        o.Timestamp AS OrderTimestamp, 
        c.Name AS CustomerName, 
        COUNT(op.OrderId) AS ProductCount
    FROM dbo.Order o
    INNER JOIN dbo.Customer c ON o.CustomerId = c.Id
    LEFT JOIN dbo.OrderProduct op ON op.OrderId = o.Id
    GROUP BY o.Timestamp, c.Name, o.Id
)

Table Splitting

Now I'm going to cover two opposite techniques related to entity persistence: first, the possibility of splitting (or mapping) a table into many entities. It's called table splitting. Why is this useful? Imagine that you want to have a smaller entity with just the essential information about an order, and another entity with the rest of the details. This way, you only access the second table if you absolutely need to.

Imagine we have an Order class:

public class Order
{
    public int Id { get; set; }
    public State State { get; set; }
    public DateTime CreationDate { get; set; }
    public OrderDetail Detail { get; set; }
}

And also an OrderDetail class:

public class OrderDetail
{
    public int Id { get; set; }
    public DateTime? DispatchDate { get; set; }
    public Order Order { get; set; }
    public Customer Customer { get; set; }
    public List<Product> Products { get; set; } = [];
}

Let's consider that Order contains the more important properties and OrderDetail all the rest. You only load the details when and if you need to. This could be mapped before using a one to one relation (with different tables), but now we have table splitting, which allows to map to the same table. We configure it like this:

modelBuilder.Entity<OrderDetail>(x =>
{
    x.ToTable("Order");
});

modelBuilder.Entity<Order>(x =>
{
    x.ToTable("Order");
    x.HasOne(o => o.Detail)
        .WithOne(o => o.Order)
        .HasForeignKey(o => o.Id);
});

Entity Splitting

Entity splitting is the exact opposite of the previous technique: an entity is spread into multiple tables. Each table must be joined by the same primary key. Let's imagine that we want to separate the Order entity:

public class Order
{
    public int Id { get; set; }
    public State State { get; set; }
    public DateTime CreationDate { get; set; }
    public DateTime? DispatchDate { get; set; }
    public Customer Customer { get; set; }
    public List<Product> Products { get; set; } = [];
}

into two tables, for better organisation: one with the more important data, and the other with the rest. Here is how we set it up:

modelBuilder.Entity<Order>(x =>
{
    x.ToTable("Order")
        .SplitToTable("OrderDetail", y =>
        {
            y.Property(o => o.DispatchDate);
            y.HasOne(o => o.Customer).WithMany();
            y.HasMany(o => o.Products).WithMany();
        });
});

So, entity Order will be mapped to Order and OrderDetail tables. The Order table will get:

Id
State
CreationDate

And OrderDetail will have:

DispatchDate
Customer (foreign key)
Products (foreign key from Product will point to OrderDetail)

Shadow and Indexer Properties

Shadow properties are properties that exist in the database but do not have a physical property in the data model (.NET class). One common usage is for things that we do not want users to change, such as soft-deleted or last updated columns; EF Core uses this behind the scenes in many to many relations, and for other relations for which there is no collection or navigation property.

To configure, we use Property:

builder.Property<DateTime?>("LastUpdated")
    .HasDefaultValueSql("GETUTCDATE()")
    .ValueGeneratedOnAddOrUpdate();

It is possible to access the current (and the original value) too, from the entity's Entry:

var lastUpdated = ctx.Entry(entity)
    .Property<DateTime?>("LastUpdated")
    .CurrentValue;

And they can even be used in queries using EF.Property:

var query = ctx.Products
    .Where(x => EF.Property<DateTime>(x, "LastUpdated").Year == 2025);

Indexer properties are similar to shadow properties, in the sense that they are virtual and do not have a backing field or property. They can be configured using IndexerProperty:

builder.IndexerProperty<string>("Colour");
builder.IndexerProperty<string>("Make");

We map then using classic .NET indexers with string keys, but we persist them is up to us:

public class Product
{
    private readonly Dictionary<string, object> _data = new();

    public object this[string key]
    {
        get => _data[key];
        set => _data[key] = value;
    }
}

As you can see, indexer properties rely on an indexer in our entity and then we can control how we are going to persist it. They can be used together with other regular properties.

To store a value:

product["Colour"] = "red";
product["Make"] = "cotton";

And to query:

ctx.Products.Single(x => x["Colour"] == "red");

Indexer properties are easier to access than shadow properties because of the indexer, which does not require the context.

Property Bag Entity Types

Now this is something totally new: the possibility to represent entities as key-value dictionaries (Dictionary<string, object> in .NET) instead of POCOs! It is called property bag entity types or shared type entities and it means that you can have this definition:

public class Context : DbContext
{
    public DbSet<Dictionary<string, object>> KeyValuePairs => Set<Dictionary<string, object>>("KeyValuePairs");
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.SharedTypeEntity<Dictionary<string, object>>("KeyValuePairs", options =>
        {
            options.Property<int>("Id");
            options.Property<string>("A");
            options.Property<int>("B");
            options.Property<DateTime>("C");
            options.HasKey("Id");
        });
    }
}

The parameter to SharedTypeEntity is the mapped table name (same as the one passed on the call to Set), and on it we must define all our properties (name, type, other constraints), using Property.

To store, it's the usual process - don't set a value for the Id property, as you wouldn't do with POCOs:

var keyValue = new Dictionary<string, object>
{
    ["A"] = "this is a phrase",
    ["B"] = 100,
    ["C"] = DateTime.UtcNow
};

ctx.KeyValuePairs.Add(keyValue);

And to query too:

ctx.KeyValuePairs.Single(x => x["Id"].Equals(1));
//or
ctx.KeyValuePairs.Find(1);

Each Dictionary<string, object> entry corresponds to a single record in the database. These are entities fully made of indexer properties.

Conclusion

As we can see, modern EF Core supports quite a lot of new functionality, including functionality that previously only existed on other O/RMs such as NHibernate. There are a few options missing, though, such as sets, maps, idbags, and indexed collections (with extra lazy loading), but what is available is already pretty impressive. Let me know what you think of this and stay tuned for more!

Search This Blog

Development with a Dot

Modern Mapping with EF Core