What's New in EF Core 7: ExecuteUpdate, ExecuteDelete, JSON Columns and TPC

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

Guess First

The bookstore decided to give a 10 percent discount on every book published before 2000. Using what we learned in the core posts, the code looks like this:

var oldBooks = db.Books
    .Where(b => b.PublishedYear < 2000)
    .ToList();

foreach (var book in oldBooks)
{
    book.Price = book.Price * 0.9m;
}

db.SaveChanges();

If 1000 books in the table match that condition, how many SQL commands does this code send to the database? One, two, or a thousand and one? I give the answer with the log in the first section.

Released on November 7, 2022, EF Core 7 is an STS release, and it brings the three features we kept postponing to "the version posts" in the core series: bulk update and delete, JSON columns and TPC. One nice detail: EF Core 7 targets net6.0. So you can move the EF side to 7 without upgrading your .NET 6 project. I ran every example in this post with EF Core 7.0.20 in a project targeting net6.0.

How Bulk Updates Worked in EF Core 6

I ran the code above with two matching books and captured the commands with LogTo:

SELECT "b"."Id", "b"."AuthorId", "b"."Price", "b"."PublishedYear", "b"."Stock", "b"."Title"
FROM "Books" AS "b"
WHERE "b"."PublishedYear" < 2000

UPDATE "Books" SET "Price" = @p0
WHERE "Id" = @p1
RETURNING 1;

UPDATE "Books" SET "Price" = @p0
WHERE "Id" = @p1
RETURNING 1;

First a SELECT, then one UPDATE per row. A thousand books means a thousand and one commands. The SQLite provider sent each UPDATE as a separate command here; on SQL Server they get batched and travel in fewer round trips, but there's still one UPDATE statement per row. On top of that, every row comes into memory first and the change tracker watches each one.

It's like changing the price tags on a thousand books in a library by taking each book off the shelf, bringing it to your desk, changing the tag and putting it back. When you could just tell the clerk "lower every tag on the pre-2000 shelf by 10 percent". In EF Core 6 the only built-in way to say that was raw SQL: writing an UPDATE by hand with ExecuteSqlInterpolated, which means giving up LINQ's type safety.

ExecuteUpdate and ExecuteDelete

With EF Core 7 the same job stays inside LINQ:

var tracked = db.Books.Single(b => b.Title == "Old Tales");

var updated = db.Books
    .Where(b => b.PublishedYear < 2000)
    .ExecuteUpdate(s => s
        .SetProperty(b => b.Price, b => b.Price * 0.9m)
        .SetProperty(b => b.Stock, 0));

var deleted = db.Books
    .Where(b => b.Stock == 0 && b.PublishedYear < 1950)
    .ExecuteDelete();

Console.WriteLine($"updated: {updated}, deleted: {deleted}");
Console.WriteLine($"tracked: {tracked.Price} {tracked.Stock} {db.Entry(tracked).State}");

The two commands that hit the log:

UPDATE "Books" AS "b"
SET "Stock" = 0,
    "Price" = ef_multiply("b"."Price", '0.9')
WHERE "b"."PublishedYear" < 2000

DELETE FROM "Books" AS "b"
WHERE "b"."Stock" = 0 AND "b"."PublishedYear" < 1950

One UPDATE and one DELETE, no matter how many rows. Not a single row comes into memory. The first lambda of SetProperty says which column changes, the second gives the new value, and the second lambda can use the current row's value. ef_multiply is a SQLite-specific detail: SQLite has no real decimal type, so EF does the multiplication with a function it registers itself. Both methods return the number of affected rows.

The line you really need to watch is at the end of the output:

updated: 2, deleted: 1
tracked: 20.0 3 Unchanged

"Old Tales" was first updated and then deleted in the database. But the instance tracked in the same context still shows the old price and stock, and its state is Unchanged. ExecuteUpdate and ExecuteDelete bypass the change tracker completely. So don't mix them with entities you track in the same context; if you need to, call ChangeTracker.Clear() afterwards or open a new context. Also, the SaveChanges interceptors and audit fields from the core posts don't run for these commands, because there is no SaveChanges involved.

JSON Columns: ToJson

We saw owned types in the core posts: we kept an author's contact info in a separate class and spread it over columns of the same table with OwnsOne. EF Core 7 can write the same owned type into a single JSON column:

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public ContactInfo Contact { get; set; } = null!;
    public List<Book> Books { get; set; } = new();
}

public class ContactInfo
{
    public string Email { get; set; } = null!;
    public string City { get; set; } = null!;
}

modelBuilder.Entity<Author>()
    .OwnsOne(a => a.Contact, b => b.ToJson());

Watch where it goes: ToJson() isn't chained directly after OwnsOne, it's called inside the lambda that configures the owned type. It works the same way for collections with OwnsMany.

There's a limit here: in EF Core 7, JSON columns are only supported on SQL Server; SQLite support arrived in EF Core 8. For SQL Server you need an instance in Docker. But you don't need a connection to see the SQL: GenerateCreateScript() and ToQueryString() run without touching the database. The output with the SQL Server provider:

CREATE TABLE [Authors] (
    [Id] int NOT NULL IDENTITY,
    [Name] nvarchar(max) NOT NULL,
    [Contact] nvarchar(max) NOT NULL,
    CONSTRAINT [PK_Authors] PRIMARY KEY ([Id])
);

SELECT [a].[Id], [a].[Name], JSON_QUERY([a].[Contact],'$')
FROM [Authors] AS [a]
WHERE CAST(JSON_VALUE([a].[Contact],'$.City') AS nvarchar(max)) = N'London'

The first is the table: there are no Email and City columns, just a single Contact column in their place. The second is the query db.Authors.Where(a => a.Contact.City == "London"): what you write in LINQ like a normal property turns into an expression that reaches inside the JSON with JSON_VALUE. According to the docs, when you change a single field inside the JSON and call SaveChanges, EF doesn't rewrite the whole document; it updates only the changed path with JSON_MODIFY.

TPC: Table per Concrete Type

We saw TPH and TPT in the core posts. EF Core 7 adds the third strategy: TPC, a full table for each concrete type in the hierarchy. In a separate context, let's add e-books and audiobooks to the bookstore:

public class Book
{
    public Guid Id { get; set; }
    public string Title { get; set; } = null!;
}

public class EBook : Book
{
    public int FileSizeMb { get; set; }
}

public class AudioBook : Book
{
    public string Narrator { get; set; } = null!;
}

modelBuilder.Entity<Book>().UseTpcMappingStrategy();
modelBuilder.Entity<EBook>();
modelBuilder.Entity<AudioBook>();

The resulting schema has three tables, and each one carries all the columns of its type:

CREATE TABLE "AudioBook" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_AudioBook" PRIMARY KEY,
    "Title" TEXT NOT NULL,
    "Narrator" TEXT NOT NULL
);

CREATE TABLE "Books" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Books" PRIMARY KEY,
    "Title" TEXT NOT NULL
);

CREATE TABLE "EBook" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_EBook" PRIMARY KEY,
    "Title" TEXT NOT NULL,
    "FileSizeMb" INTEGER NOT NULL
);

No JOINs like in TPT, no columns full of NULLs like in TPH. You pay the price when you query the whole hierarchy. The ToQueryString() output for db.Books and db.Books.OfType<EBook>():

SELECT "b"."Id", "b"."Title", NULL AS "Narrator", NULL AS "FileSizeMb", 'Book' AS "Discriminator"
FROM "Books" AS "b"
UNION ALL
SELECT "a"."Id", "a"."Title", "a"."Narrator", NULL AS "FileSizeMb", 'AudioBook' AS "Discriminator"
FROM "AudioBook" AS "a"
UNION ALL
SELECT "e"."Id", "e"."Title", NULL AS "Narrator", "e"."FileSizeMb", 'EBook' AS "Discriminator"
FROM "EBook" AS "e"

SELECT "e"."Id", "e"."Title", "e"."FileSizeMb", 'EBook' AS "Discriminator"
FROM "EBook" AS "e"

The rule is simple: if most of your queries go to a single subtype, TPC shines, because that query goes to a single table. If you keep listing the whole hierarchy, UNION ALL gets expensive; TPH is usually better there. We'll see why the key is a Guid in the pitfalls section. By the way, TPT now has an explicit API too: UseTptMappingStrategy().

What's New in Interceptors and Raw SQL

We wrote a SaveChangesInterceptor and a DbCommandInterceptor in the core posts. EF Core 7 adds new ones to the list; the most useful is IMaterializationInterceptor. You can step in while each entity instance coming from the database is being created:

public interface IHasRetrieved
{
    DateTime Retrieved { get; set; }
}

public class SetRetrievedInterceptor : IMaterializationInterceptor
{
    public object InitializedInstance(MaterializationInterceptionData materializationData, object instance)
    {
        if (instance is IHasRetrieved hasRetrieved)
        {
            hasRetrieved.Retrieved = DateTime.UtcNow;
        }

        return instance;
    }
}

// Book : IHasRetrieved, its Retrieved property is [NotMapped]
options
    .UseSqlite("Data Source=bookstore7b.db")
    .AddInterceptors(RetrievedInterceptor);

In this example we write when the entity was loaded into an unmapped property; at the same point you could also inject a service into the entity. The list also includes IQueryExpressionInterceptor (changing the LINQ expression tree before it's compiled), the ThrowingConcurrencyException hook that runs before a concurrency exception is thrown, and connection interception that lets you supply the connection string at the moment of the first connection.

There are two additions on the raw SQL side. The FromSql name is back, this time only in its safe form that takes an interpolated string. Database.SqlQuery<T> lets you write queries that return scalar results, and you can compose LINQ on top:

var minYear = 2000;
var recent = db.Books
    .FromSql($"SELECT * FROM Books WHERE PublishedYear >= {minYear}")
    .OrderBy(b => b.Title)
    .ToList();

var years = db.Database
    .SqlQuery<int>($"SELECT PublishedYear AS Value FROM Books")
    .Where(y => y > 1900)
    .ToList();

The SQL produced by the second query wraps yours as a subquery:

SELECT "t"."Value"
FROM (
    SELECT PublishedYear AS Value FROM Books
) AS "t"
WHERE "t"."Value" > 1900

The column has to be named Value; EF looks for that name in the wrapping query. In EF Core 7, T can only be a scalar type; we'll see filling a non-entity class with results in EF Core 8.

Moving to EF Core 7

The package side of an EF Core 6 project is one line; net6.0 can stay as it is:

<TargetFramework>net6.0</TargetFramework>

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

Bump the dotnet-ef tool to 7.0.20 too. Two of the breaking changes affect everyone using SQL Server. The first is the Encrypt=True default that came with Microsoft.Data.SqlClient 4.0: when connecting to a SQL Server without a valid certificate on your dev machine, you'll get "The certificate chain was issued by an authority that is not trusted". For development only, adding this to the connection string is enough:

Server=localhost;Database=Bookstore;User Id=sa;Password=...;TrustServerCertificate=True

The second is triggers. To speed up saving, EF Core 7 uses the OUTPUT clause on SQL Server, and SQL Server doesn't allow that on a table with triggers. You have to tell EF about every table that has a trigger:

modelBuilder.Entity<Book>()
    .ToTable(tb => tb.HasTrigger("TRG_Books_Audit"));

EF then falls back to the EF Core 6 SQL for that table. On SQLite a similar change came with the RETURNING clause: according to the docs, tables with AFTER triggers or virtual tables need special configuration. There are also a few warnings the docs say "throw again"; because of a bug in EF Core 6 they were only logged, and in 7 they become exceptions by default again.

On support: EF Core 7 was STS and went out of support on May 14, 2024, so nobody should stay on it today. But every API in this post is still there in EF Core 8 and 10; 7 is just where they first appeared.

Common Pitfalls

First, trusting tracked entities after ExecuteUpdate. We saw it above: a row deleted in the database can sit in the change tracker as Unchanged. If you mix the two in the same context, do the bulk operation first and load entities afterwards.

Second, assuming HasTrigger changes anything on SQLite. The code compiles on SQLite too, but when I checked the log, EF Core 7.0.20 was still sending the INSERT with RETURNING "Id". In EF Core 7 the fix for SQLite is to switch the whole context to the old SQL generator: ReplaceService<IUpdateSqlGenerator, SqliteLegacyUpdateSqlGenerator>(). When I did that, the compiler gave an EF1001 warning, because that class is an internal API. EF Core 8 replaced this with the per-table UseSqlReturningClause(false).

Third, trying a JSON column on SQLite in EF Core 7. You don't even get a meaningful error: when I called EnsureCreated on SQLite with OwnsOne(..., b => b.ToJson()), a NullReferenceException came from RelationalModel.CreateTableMapping while the model was being built.

Fourth, using an int key with TPC. Since there's no shared table, an identity column can't act as a shared counter, and SQLite has no sequences. When I built the same hierarchy with an int key and added one Book and one EBook, both keys stayed 0 and Add gave me this error:

InvalidOperationException: The instance of entity type 'EBook' cannot be tracked because another instance with the same key value for {'Id'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the conflicting key values.

On SQL Server, EF uses a sequence for this by default. On SQLite a Guid key is the most trouble-free option.

A Small Experiment

★☆☆ Set the price of every out-of-stock book to zero with ExecuteUpdate, then call ExecuteDelete with the same condition. Use Count() to confirm that the returned numbers match the row counts in the database.

★★☆ Build the TPC hierarchy above with TPH as well. Put the ToQueryString() outputs of db.Books.ToList() and db.Books.OfType<AudioBook>().ToList() side by side for both models and see which one is simpler under which strategy.

★★★ With IMaterializationInterceptor, generate an unmapped DisplayTitle property for every loaded Book from its Title and publication year. Then run the same query with AsNoTracking(); does the interceptor run in both cases?

EF Core 7's JSON columns were built on owned types and didn't support SQLite. In the next post we'll look at how both changed in EF Core 8 LTS, along with complex types and primitive collections.