EF Core Identity Value Return Cost Explained

This article explains the real performance and usability cost of returning the identity value after an insert in EF Core, and what you can do to minimize it. The short answer: the cost is one extra database round-trip per inserted row (unless you use a client-generated key), plus a small amount of CPU and memory for materializing the result. In most applications this overhead is negligible, but in bulk-insert scenarios it can multiply your database calls and become the dominant bottleneck.

Background: Why EF Core Returns Identity Values

When you insert a row into a table with an auto-incrementing primary key (IDENTITY in SQL Server, AUTO_INCREMENT in MySQL, SERIAL in PostgreSQL), the database generates the key value. EF Core needs that value back so the tracked entity in your context has the correct key. Without it, the entity would have a temporary key (often 0 or a negative number) and subsequent operations like updating that entity or using it as a navigation property would fail.

EF Core’s default behavior is to fetch the generated identity value immediately after the INSERT statement. It does this by appending a SELECT clause to the INSERT (in SQL Server it uses OUTPUT INSERTED.Id; in PostgreSQL it uses RETURNING). This is not optional—if you want the entity to be usable after SaveChanges, the value must be retrieved.

How EF Core Retrieves the Identity Value

The exact mechanism depends on the database provider:

• SQL Server: EF Core appends OUTPUT INSERTED.[Id] to the INSERT statement. This returns the value in the same round-trip, so there is no extra network call.
• PostgreSQL: Uses RETURNING "Id", again in the same round-trip.
• MySQL: Does not support OUTPUT or RETURNING, so EF Core issues a separate SELECT LAST_INSERT_ID() (or SELECT @@IDENTITY) after the INSERT. This is a second round-trip.
• SQLite: Uses last_insert_rowid() in a separate query.

So the “cost� is not always a separate round-trip—it depends on the database. For SQL Server and PostgreSQL, the value comes back in the same network packet. For MySQL and SQLite, it is an extra round-trip per insert.

Real Cost: Round-Trips vs. CPU

The dominant cost is the number of database round-trips. If you insert 1,000 rows in a single SaveChanges call, EF Core will batch them (if batching is enabled) but it still needs to retrieve the identity for each row. For SQL Server, the batch INSERT can include multiple OUTPUT clauses, so one round-trip can return 1,000 identity values. For MySQL, EF Core must execute 1,000 separate SELECT LAST_INSERT_ID() calls—one per row—because the value is per-connection, not per-batch. That is 1,000 round-trips instead of 1.

In terms of raw numbers, a single round-trip on a local network takes about 0.1–0.5 ms. On a remote cloud database, it can be 1–10 ms. So inserting 1,000 rows with MySQL could add 1–10 seconds of latency. That is the real cost: it turns a bulk insert into a slow loop.

How to Choose: When to Accept the Cost

For most CRUD applications, the cost is irrelevant. You insert one or a few entities per request, and the extra round-trip (or the OUTPUT clause) is a tiny fraction of the total request time. You should always accept the default behavior unless you have a measured performance problem.

When you do have a bulk-insert scenario (importing thousands of rows, seeding a test database), you have three options:

1. Keep the default and accept the overhead. For SQL Server/PostgreSQL, the overhead is minimal even for large batches. For MySQL, it will be slow.
2. Use a client-generated key (e.g., GUID) so EF Core does not need to retrieve anything. This eliminates the round-trip entirely, but changes your key type.
3. Bypass EF Core for the bulk insert using a bulk library or raw SQL, and then re-query the inserted rows if you need the IDs.

Common Problems and Misconceptions

A frequent misconception is that EF Core always does an extra round-trip. As shown above, that is only true for MySQL and SQLite. Another misconception is that you can disable identity retrieval by setting ValueGeneratedNever. That will stop EF Core from requesting the value, but then your entity will have the temporary key, and you will not be able to update it or use it in relationships until you manually set the real key.

Another real problem: when you use SaveChanges in a loop (e.g., inserting 100 entities one by one), you are already paying 100 round-trips for the INSERTs. Adding the identity retrieval doubles that. The fix is to use a single SaveChanges call for all inserts, or use a bulk insert library.

Also note that EF Core’s batching (introduced in EF Core 1.0) can combine multiple INSERT statements into one round-trip, but the identity retrieval still happens per row. For SQL Server, the OUTPUT clause is included in the batch, so it is still one round-trip. For MySQL, it is not.

Comparing Providers: SQL Server vs. MySQL vs. PostgreSQL

Here is a concrete comparison of the overhead for inserting 1,000 rows:

• SQL Server: 1 round-trip for the batch INSERT with OUTPUT. Overhead: negligible.
• PostgreSQL: 1 round-trip for the batch INSERT with RETURNING. Overhead: negligible.
• MySQL: 1 round-trip for the batch INSERT (if batching works), plus 1,000 round-trips for SELECT LAST_INSERT_ID(). Overhead: 1,000 extra round-trips.
• SQLite: Similar to MySQL—1,000 extra round-trips (but SQLite is local, so each round-trip is very fast).

If you are on MySQL and need to bulk insert, consider using a GUID key or a bulk insert library that can retrieve the IDs in a more efficient way (some libraries use a temporary table or a single SELECT after the insert).

Alternatives to Returning the Identity Value

If you want to avoid the cost entirely, you have a few options:

• Use a GUID (or other client-generated) key. This is the simplest way to avoid the round-trip, but GUIDs have their own costs (larger index, fragmentation).
• Use a sequence or hi/lo algorithm (e.g., Npgsql’s hi/lo for PostgreSQL). This pre-fetches a range of IDs on the client, so no retrieval is needed. EF Core supports this for PostgreSQL and SQL Server (with a custom value generator).
• Do not track the inserted entities. If you do not need the entity after insert, you can use ExecuteSqlRaw or a bulk insert library that does not return IDs.
• Re-query the rows after a bulk insert if you need the IDs, but that adds a different cost.

Frequently Asked Questions

Does EF Core always make an extra round-trip to get the identity?

No. For SQL Server and PostgreSQL, the identity value is returned in the same round-trip as the INSERT (using OUTPUT or RETURNING). Only MySQL and SQLite require a separate query (e.g., SELECT LAST_INSERT_ID()).

Can I disable identity retrieval to make inserts faster?

You can set ValueGeneratedNever() on the key property, but then EF Core will not know the real key. You must set it yourself before saving, or you will get temporary keys that break relationships and updates. It is not a recommended optimization.

Why is my bulk insert so slow with MySQL and EF Core?

Because EF Core issues a separate SELECT LAST_INSERT_ID() for every inserted row. With 1,000 rows, that is 1,000 extra round-trips. Use a client-generated key or a bulk insert library to avoid this.

Is there a way to get multiple identity values in one round-trip?

For SQL Server and PostgreSQL, yes—the OUTPUT/RETURNING clause returns all values in the batch. For MySQL, no, because LAST_INSERT_ID() returns only the first generated value for the connection. You would need to use a different strategy.

Does using SaveChanges in a loop make the cost worse?

Yes. Each SaveChanges is a separate transaction and round-trip. If you insert 100 entities in a loop, you get 100 INSERT round-trips plus 100 identity retrievals (for MySQL). Always batch inserts into a single SaveChanges call.

What is the best key type for avoiding identity retrieval cost?

A client-generated GUID or a hi/lo sequence. GUIDs avoid the round-trip entirely but can hurt index performance. Hi/lo (available for PostgreSQL) pre-fetches a range of IDs, so no retrieval is needed and the keys remain integer.

Related

• Build vs Buy TCO: How to Calculate Total Cost of Ownership
• Quiet Quitting Rules: Why People Do It
• Recover Deleted Pictures on Nikon Camera