Non-tracking ExecuteInsert
- 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
Assessment
This issue has not been assessed yet.