The Real Cost of Returning the Identity Value in EF Core

When you insert thousands of rows into SQL Server with Entity Framework Core, performance drops quickly.

Most developers blame change tracking.
But there is another hidden cost that slows down every SaveChanges call: returning database-generated identity values back to your entities.

If your table has an auto-increment primary key (like most SQL Server tables), EF Core must ask the database for each generated identity after the insert.
This round-trip limits batch size, forces extra SQL logic, and is the reason SaveChanges struggles at scale.

In this post, you will learn:

• Why SaveChanges is slow when inserting 10,000 rows into a table with an identity column
• How EF Core handles generated value properties under the hood
• How Entity Framework Extensions solves this problem with BulkInsert
• How to make BulkInsert faster with the AutoMapOutputDirection option
• Why BulkInsertOptimized is the fastest bulk insert method and how it compares with the other approaches
• When you should (and shouldn't) return identity values after a bulk insert

All examples in this post were tested against a SQL Server database running in Docker, with benchmarks written in BenchmarkDotNet on .NET 10.

Let's dive in.

---

👉 Read original article on my newsletter: https://antondevtips.com/blog/the-real-cost-of-returning-the-identity-value-in-ef-core

The Problem: Inserting 10,000 Rows with EF Core SaveChanges

I built a small benchmark project to measure how long EF Core takes to insert 10,000 Product rows into SQL Server.

Here is the Product entity:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public string Description { get; set; } = string.Empty;
    public string Sku { get; set; } = string.Empty;
    public string Barcode { get; set; } = string.Empty;
    public string Category { get; set; } = string.Empty;
    public string Brand { get; set; } = string.Empty;
    public string Manufacturer { get; set; } = string.Empty;
    public int StockQuantity { get; set; }
    public decimal Weight { get; set; }
    public bool IsActive { get; set; } = true;
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public DateTime? UpdatedAt { get; set; }
}

And the Fluent API mapping that configures the identity column:

public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.HasKey(p => p.Id);

        builder.Property(p => p.Id).ValueGeneratedOnAdd();

        builder.Property(p => p.Name).IsRequired().HasMaxLength(250);
        builder.Property(p => p.Price).IsRequired().HasColumnType("decimal(18,2)");
        builder.Property(p => p.Description).HasMaxLength(1000);

        builder.Property(p => p.Sku).IsRequired().HasMaxLength(50);
        builder.Property(p => p.Barcode).IsRequired().HasMaxLength(20);
        builder.Property(p => p.Category).IsRequired().HasMaxLength(100);
        builder.Property(p => p.Brand).IsRequired().HasMaxLength(100);
        builder.Property(p => p.Manufacturer).IsRequired().HasMaxLength(100);
        builder.Property(p => p.StockQuantity).IsRequired();
        builder.Property(p => p.Weight).IsRequired().HasColumnType("decimal(10,3)");
        builder.Property(p => p.IsActive).IsRequired().HasDefaultValue(true);
        builder.Property(p => p.CreatedAt).IsRequired();
        builder.Property(p => p.UpdatedAt);

        builder.HasIndex(p => p.Sku).IsUnique();
    }
}

The ValueGeneratedOnAdd() call tells EF Core that the database will generate Id on insert.
On SQL Server, this maps to an IDENTITY(1,1) column.

Here is the baseline benchmark that uses the standard SaveChangesAsync approach:

[Benchmark]
public async Task SaveChangesAsync()
{
    var products = GenerateProducts(10_000);
    _dbContext.Products.AddRange(products);
    await _dbContext.SaveChangesAsync();
}

I use Bogus to generate fake products:

public static List<Product> GenerateProducts(int count)
{
    return new Faker<Product>()
        .RuleFor(p => p.Name, f => f.Commerce.ProductName())
        .RuleFor(p => p.Description, f => f.Lorem.Paragraph())
        .RuleFor(p => p.Price, f => decimal.Parse(f.Commerce.Price()))
        .RuleFor(p => p.Sku, f => $"SKU-{Guid.NewGuid():N}")
        .RuleFor(p => p.Barcode, f => f.Commerce.Ean8())
        .RuleFor(p => p.Category, f => f.Commerce.Categories(1)[0])
        .RuleFor(p => p.Brand, f => f.Company.CompanyName())
        .RuleFor(p => p.Manufacturer, f => f.Company.CompanyName())
        .RuleFor(p => p.StockQuantity, f => f.Random.Int(0, 10_000))
        .RuleFor(p => p.Weight, f => Math.Round(f.Random.Decimal(0.01m, 50m), 3))
        .RuleFor(p => p.IsActive, f => f.Random.Bool(0.9f))
        .RuleFor(p => p.CreatedAt, f => f.Date.Past(2).ToUniversalTime())
        .RuleFor(p => p.UpdatedAt, f => f.Date.Recent(30).OrNull(f, 0.3f)?.ToUniversalTime())
        .Generate(count);
}

On my SQL Server instance running in Docker, this benchmark takes around 7,000 ms for 10,000 rows.
For production APIs with a strict p99 SLA, that is way too slow.

But here is the interesting part: the slowdown is not only change tracking.
A large part of the cost comes from EF Core's need to return the database-generated Id back to every entity.

Let's see why.

Why EF Core Is Slow When Returning Identity Values

When Product.Id is marked as ValueGeneratedOnAdd, EF Core must perform two jobs during SaveChanges:

1. Insert the row into the database
2. Retrieve the generated identity value and assign it back to the in-memory Product.Id

On SQL Server, EF Core uses a MERGE statement with an OUTPUT clause to batch multiple inserts into one command and still return each new identity.

The generated SQL looks roughly like this:

MERGE [products].[products] USING (
    VALUES (@p0, @p1, @p2),
    (@p3, @p4, @p5),
    (@p6, @p7, @p8)
    ) AS i ([name], [price], [description])
    ON 1 = 0
    WHEN NOT MATCHED THEN
    INSERT ([name], [price], [description]) VALUES (i.[name], i.[price], i.[description])
    OUTPUT INSERTED.[id];

This approach is clever, but it has hard limits:

• The 2,100 parameter cap - SQL Server allows a maximum of 2,100 parameters per command. With 14 mapped columns, that is roughly 100-200 rows per batch. For 10,000 Product rows, EF Core needs many round-trips.
• Parameter binding cost - Every parameter has to be created, bound, and sent over the network. For 10,000 rows with a few columns, that is tens of thousands of parameters.
• Change tracker overhead - After each batch, EF Core updates the change tracker and assigns the returned identity values to each tracked entity.
• Log writes and index updates - The database writes to the transaction log and updates indexes for every batch.

You might think: "What if I disable the identity column?"
That removes the OUTPUT round-trip, but now you are responsible for generating unique IDs, and you lose the simplicity of auto-increment keys.

For a deeper explanation of how EF Core handles generated values, see this article on LearnEntityFrameworkCore.

There is a better solution.

---

👉 Read original article on my newsletter: https://antondevtips.com/blog/the-real-cost-of-returning-the-identity-value-in-ef-core