dotnet / dotnet/efcore

Non-tracking ExecuteInsert

Open
#29,897 9 comments 130 reactions 0 assignees View on GitHub
area-bulkupdate area-perf customer-reported needs-design
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

EF Core 7.0 introduced ExecuteUpdate and ExecuteDelete, which are ways of updating and deleting database rows without passing through change tracking. For inserts, it's still necessary to go through the change tracker (Add and then SaveChanges).

There are two main advantages to this:

1. Ease-of-use. For the common task of adding a single entity, this would be a single line instead of two.
2. Performance. Not passing through the change tracker would remove that overhead.

Notes:

* We should probably allow a single call can insert multiple rows, since SQL INSERT allows this.
* If so, it should still be kept distinct from [#27333](https://github.com/dotnet/efcore/issues/27333), which would be a bulk import mechanism suitable for a large number of rows (different mechanism).
* As alternatives, note that it's still possible to use the change tracker to insert multiple rows. [#10879](https://github.com/dotnet/efcore/issues/10879) would also allow bundling an arbitrary set of query or ExecuteXXX operations in a single batch.
* Note that unlike ExecuteUpdate/Delete, ExecuteInsert doesn't need to return the number of rows affected (inserts either succeed or error), so the simple ExecuteInsert would return void/Task.
* Similar to ExecuteUpdate and ExecuteDelete, since ExecuteInsert bypasses change tracking, it would also not (by default) fetch back database-generated values.
* [#29898](https://github.com/dotnet/efcore/issues/29898) tracks adding RETURNING/OUTPUT support to ExecuteUpdate/Delete, which would be relevant here too. So some version of ExecuteInsert would return an IQueryable:

```c#
var dbGeneratedStuff = ctx.Blogs.ExecuteInsertReturning(blogs).Select(b => new { b.Id, b.Name}).ToArray();
```

At least in some DBs, returning an IQueryable allow embedding the insert in a CTE (WITH).

* Consider whether we want to allow arbitrary expressions (e.g. `INSERT INTO foo (bar) VALUES (current_timestamp)`). This can also be useful to call some database function over a user-provided parameter (e.g. PG full-text search insertion, https://github.com/npgsql/efcore.pg/issues/2317#issuecomment-1443402048). Note that there wouldn't be any query root here; only totally static functions make sense in this context. This also may intersect with server-side value converters (#10861), where a database function needs to be called on user-supplied data before inserting.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.