What's New in EF Core 8: Complex Types, Primitive Collections and SqlQuery<T>

Version note: This post covers what EF Core 8 introduced; the code targets EF Core 8.

Guess First

You want to add free-form keywords to books. A separate table feels like overkill, so you turn the property straight into a list:

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = null!;
    public int PublishedYear { get; set; }
    public List<string> Keywords { get; set; } = new();
    public int AuthorId { get; set; }
    public virtual Author Author { get; set; } = null!;
}

What does EF Core 6 do with this model: ignore the list, turn it into a comma-separated string, or throw? And how does EF Core 8 store the same model? Both answers are in the first section.

Released on November 14, 2023, EF Core 8 is an LTS release, and it's the modern EF version you'll run into most often in the field today. I ran the examples in this post with EF Core 8.0.33 in a project targeting net8.0.

Primitive Collections

The error I got when I called EnsureCreated on EF Core 6.0.36:

InvalidOperationException: The entity type 'List<string>' requires a primary key to be defined. If you intended to use a keyless entity type, call 'HasNoKey' in 'OnModelCreating'. For more information on keyless entity types, see https://go.microsoft.com/fwlink/?linkid=2141943.

EF Core 6 thinks List<string> is an entity and goes looking for its key. In the core posts there were two ways around that: a separate table or a hand-written value converter. EF Core 8 writes the same model to a JSON column without any configuration. In the GenerateCreateScript() output:

CREATE TABLE "Books" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Books" PRIMARY KEY AUTOINCREMENT,
    "Title" TEXT NOT NULL,
    "PublishedYear" INTEGER NOT NULL,
    "Keywords" TEXT NOT NULL,
    "AuthorId" INTEGER NOT NULL,
    CONSTRAINT "FK_Books_Authors_AuthorId" FOREIGN KEY ("AuthorId") REFERENCES "Authors" ("Id") ON DELETE CASCADE
);

The really nice part is that this column can be queried. You couldn't reach inside a string stored through a value converter with LINQ; here you can:

var dotnetBooks = db.Books
    .Where(b => b.Keywords.Contains("dotnet"))
    .ToList();

SELECT "b"."Id", "b"."AuthorId", "b"."Keywords", "b"."PublishedYear", "b"."Title"
FROM "Books" AS "b"
WHERE 'dotnet' IN (
    SELECT "k"."value"
    FROM json_each("b"."Keywords") AS "k"
)

SQLite's json_each function expands the JSON array into rows, and EF then writes a normal IN. The same mechanism kicks in for collections you pass as parameters, and this is the part that changes the queries from the core posts. The ids.Contains(b.Id) query was generated with constants in EF Core 6:

var ids = new[] { 1, 2, 3 };
var books = db.Books.Where(b => ids.Contains(b.Id)).ToList();

SELECT "b"."Id", "b"."AuthorId", "b"."Title"
FROM "Books" AS "b"
WHERE "b"."Id" IN (1, 2, 3)

In EF Core 8 the same line becomes a single JSON parameter:

.param set @__ids_0 '[1,2,3]'

SELECT "b"."Id", "b"."AuthorId", "b"."Keywords", "b"."PublishedYear", "b"."Title"
FROM "Books" AS "b"
WHERE "b"."Id" IN (
    SELECT "i"."value"
    FROM json_each(@__ids_0) AS "i"
)

Why? In EF Core 6, a list with 3 elements produced one SQL statement and a list with 4 elements produced another. The database's plan cache kept a separate plan for every length. With a single parameter, the SQL text always stays the same. We'll see a side effect of this change on SQL Server in the migration section.

Complex Types: The Right Fit for Value Objects

In the core posts we modeled values like addresses as owned types. Owned types work, but they are entities with a hidden key inside; EF tracks them by identity. An address, though, is a value without identity: if two addresses have the same street, city and postcode, they're the same address. EF Core 8's complex types are exactly that idea:

public class Address
{
    public required string Street { get; set; }
    public required string City { get; set; }
    public required string PostCode { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public DateTime CreatedAt { get; set; }
    public required Address ShippingAddress { get; set; }
    public required Address BillingAddress { get; set; }
}

modelBuilder.Entity<Order>().ComplexProperty(o => o.ShippingAddress);
modelBuilder.Entity<Order>().ComplexProperty(o => o.BillingAddress);

On the table side there's no difference from an owned type; the columns spread across the Orders table:

CREATE TABLE "Orders" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Orders" PRIMARY KEY AUTOINCREMENT,
    "CreatedAt" TEXT NOT NULL,
    "BillingAddress_City" TEXT NOT NULL,
    "BillingAddress_PostCode" TEXT NOT NULL,
    "BillingAddress_Street" TEXT NOT NULL,
    "ShippingAddress_City" TEXT NOT NULL,
    "ShippingAddress_PostCode" TEXT NOT NULL,
    "ShippingAddress_Street" TEXT NOT NULL
);

The difference is in behavior. Think of an order whose billing and shipping addresses are the same, and give both properties the same Address instance. When I built the same model with OwnsOne, SaveChanges said:

InvalidOperationException: Cannot save instance of 'Order.ShippingAddress#Address (Address)' because it is an owned entity without any reference to its owner. Owned entities can only be saved as part of an aggregate also including the owner entity.

An owned type has an identity and a single owner, so the same instance can't live in two places. With a complex type the save goes through. But now you have to watch out for something else. I changed the street of the shipping address:

order.ShippingAddress.Street = "2 Side St";
db.SaveChanges();

UPDATE "Orders" SET "BillingAddress_Street" = @p0, "ShippingAddress_Street" = @p1
WHERE "Id" = @p2
RETURNING 1;

The billing address changed too, because both are the same object. The fix is to make the value object immutable. A record is a perfect fit:

public record Address(string Street, string City, string PostCode);

order.ShippingAddress = order.ShippingAddress with { Street = "2 Side St" };
db.SaveChanges();

UPDATE "Orders" SET "ShippingAddress_Street" = @p0
WHERE "Id" = @p1
RETURNING 1;

Now only the shipping address changes. Complex types have limits in EF Core 8 too: they can't be collections, can't be null, and can't be mapped to JSON. All three go away in EF Core 10; we'll meet them again in that post.

Non-Entity Results with SqlQuery<T>

In the raw SQL post, reading the result of a report query meant defining a keyless entity and adding it to the model. The SqlQuery<T> we saw in EF Core 7 only accepted scalar types. With EF Core 8, T can be an ordinary class that isn't in the model:

public class AuthorBookCount
{
    public string Name { get; set; } = null!;
    public int BookCount { get; set; }
}

var counts = db.Database
    .SqlQuery<AuthorBookCount>($"""
        SELECT a.Name, COUNT(b.Id) AS BookCount
        FROM Authors a
        LEFT JOIN Books b ON b.AuthorId = a.Id
        GROUP BY a.Name
        """)
    .Where(x => x.BookCount > 1)
    .ToList();

SELECT "a"."BookCount", "a"."Name"
FROM (
    SELECT a.Name, COUNT(b.Id) AS BookCount
    FROM Authors a
    LEFT JOIN Books b ON b.AuthorId = a.Id
    GROUP BY a.Name
) AS "a"
WHERE "a"."BookCount" > 1

AuthorBookCount isn't in any DbSet and doesn't end up in a migration. Column names match property names, and the Where you add on top gets translated to SQL as well. The "write the SQL, fill a DTO" job we did in the Dapper series can now be done without leaving EF; thanks to the interpolated string, the parameters are still safe.

Lazy Loading and No-Tracking

In the core posts we set up lazy-loading proxies and talked about their risks. They had one more limit. In a model with UseLazyLoadingProxies() and virtual navigations, when I accessed the books of an author loaded with AsNoTracking() on EF Core 6:

InvalidOperationException: An error was generated for warning 'Microsoft.EntityFrameworkCore.Infrastructure.DetachedLazyLoadingWarning': An attempt was made to lazy-load navigation 'Books' on a detached entity of type 'AuthorProxy'. Lazy loading is not supported for detached entities or entities that are loaded with 'AsNoTracking'. This exception can be suppressed or logged by passing event ID 'CoreEventId.DetachedLazyLoadingWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.

On EF Core 8 the same code works:

var authors = db.Authors.AsNoTracking().ToList();
Console.WriteLine($"books: {authors[0].Books.Count}");

SELECT "a"."Id", "a"."Name"
FROM "Authors" AS "a"

SELECT "b"."Id", "b"."AuthorId", "b"."Keywords", "b"."PublishedYear", "b"."Title"
FROM "Books" AS "b"
WHERE "b"."AuthorId" = @__p_0
books: 2

The second SELECT went out the moment we touched Books. There's a price: according to the docs, no-tracking entities keep holding a reference to the context for lazy loading. Once the context is disposed loading stops working, and with long-lived entities you need to watch out for memory leaks. If you want to turn off lazy loading for a specific navigation, Navigation(a => a.Books).EnableLazyLoading(false) arrived in this release too.

JSON on SQLite, HierarchyId on SQL Server

In the EF Core 7 post we saw ToJson() blow up on SQLite while the model was being built. In EF Core 8 the same OwnsOne(a => a.Contact, b => b.ToJson()) model works on SQLite, and the query a.Contact.City == "London" is translated to SQLite's JSON operator:

SELECT "a"."Id", "a"."Name", "a"."Contact"
FROM "Authors" AS "a"
WHERE "a"."Contact" ->> 'City' = 'London'

On the SQL Server side, the hierarchyid type came to EF. For that you need the Microsoft.EntityFrameworkCore.SqlServer.HierarchyId package and a SQL Server in Docker. Think of tags as a tree: /1/ programming, /1/1/ .NET, /1/1/1/ EF Core:

public class Tag
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public HierarchyId Path { get; set; } = null!;
}

options.UseSqlServer(connectionString, x => x.UseHierarchyId());

var parent = HierarchyId.Parse("/1/");
var children = db.Tags
    .Where(t => t.Path.GetAncestor(1) == parent)
    .ToList();

DECLARE @__parent_0 hierarchyid = hierarchyid::Parse('/1/');

SELECT [t].[Id], [t].[Name], [t].[Path]
FROM [Tags] AS [t]
WHERE [t].[Path].GetAncestor(1) = @__parent_0

Again I got the SQL with ToQueryString(), without opening a connection. Methods like GetAncestor, IsDescendantOf and GetLevel go straight to SQL Server's own functions.

Moving to EF Core 8: Contains and OPENJSON

EF Core 8 requires net8.0, so in a .NET 6 or 7 project you have to upgrade the TFM first:

<TargetFramework>net8.0</TargetFramework>

<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="8.0.33" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.33" />

The two changes the docs mark as "High" are about the Contains we just saw. On SQL Server, the parameter collection is expanded with OPENJSON, and OPENJSON arrived in SQL Server 2016 with compatibility level 130. On an older server, or on a database whose compatibility level is below 130, a query that worked on EF Core 6 fails on 8. A case the docs specifically warn about: databases moved from an on-premises SQL Server to Azure SQL can carry their old compatibility level with them. Plans can also get worse for some queries. I got both SQL statements with the SQL Server provider, without a connection:

// Default setting
options.UseSqlServer(connectionString);

// For an old server or a low compatibility level
options.UseSqlServer(connectionString, o => o.UseCompatibilityLevel(120));

DECLARE @__ids_0 nvarchar(4000) = N'[1,2,3]';

SELECT [b].[Id], [b].[AuthorId], [b].[Keywords], [b].[PublishedYear], [b].[Title]
FROM [Books] AS [b]
WHERE [b].[Id] IN (
    SELECT [i].[value]
    FROM OPENJSON(@__ids_0) WITH ([value] int '$') AS [i]
)

SELECT [b].[Id], [b].[AuthorId], [b].[Keywords], [b].[PublishedYear], [b].[Title]
FROM [Books] AS [b]
WHERE [b].[Id] IN (1, 2, 3)

The first is the default, the second is with UseCompatibilityLevel(120). In the second case EF goes back to the constant list from EF Core 6. One more change matters to anyone who used JSON columns in EF Core 7: enums inside JSON are now stored as ints instead of strings. If existing data contains string enums, reading it fails; the fix the docs suggest is to give that property HasConversion<string>().

A nice tool arrived too: the dotnet ef migrations has-pending-model-changes command and Database.HasPendingModelChanges() tell you whether the model has a change that hasn't been turned into a migration. It's perfect for catching commits in CI that forgot to add a migration. On support, even though EF Core 8 is LTS, it goes out of support on November 10, 2026 together with EF Core 9. If you're on 8, your target should be 10.

Common Pitfalls

First, using a primitive collection instead of a small table. A JSON column makes it hard to update the list item by item, to enforce integrity with foreign keys, and to index. If the list really is just a list of values belonging to that row, it's great; if it relates to other tables, the many-to-many from the core posts is still the right answer. That's also why we keep tags as a separate entity in this series.

Second, leaving a complex type mutable and sharing instances. We saw it above: a one-line assignment changed two addresses at once, and EF did it silently. Write value objects as a record or as a class that is only set through its constructor.

Third, not checking the SQL Server version during the upgrade. If your tests run on LocalDB or a fresh Docker image, the OPENJSON problem won't show up there; it blows up on the old production server. Find out production's compatibility level and set UseCompatibilityLevel if needed.

Fourth, treating no-tracking lazy loading as a performance win. Not tracking doesn't change the fact that every navigation access is a separate SQL statement; the N+1 problem is still there. If you're going to touch a navigation while looping over a list, Include or a projection is still the right tool.

A Small Experiment

★☆☆ Add List<string> Keywords to Book, save a few books, open the SQLite file with a database tool and look at the column's content. Then inspect the SQL of a b.Keywords.Count > 1 filter with ToQueryString().

★★☆ Map Address on Order as a complex type, first as a mutable class and then as a record. In both cases give the same instance to both properties, change one, and compare the UPDATEs that hit the log.

★★★ Write a SqlQuery<T> for a report that returns order totals: the total number of books sold per author. Add OrderByDescending and Take(3) on top of the result and see where they end up in the generated SQL. What would you do differently if you wrote the same report with Dapper?

As an LTS release, EF Core 8 will stay in the field for a long time. In the next post we look at an STS release instead: EF Core 9, which makes seeding and migrations safer.