dotnet / dotnet/efcore

Allow fetching values back with ExecuteUpdate/Delete

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

Description

Our current ExecuteUpdate/Delete APIs do not support returning database-generate values, although all databases support this (RETURNING or OUTPUT clause); this functionality was scoped out of 7.0 to gauge user interest.

Some initial design thoughts [copied from the original issue](https://github.com/dotnet/efcore/issues/795#issuecomment-997450180) (see Operator return values):

The basic versions of the Update/Delete operators should return the number of rows affected, as returned to us from DbCommand.ExecuteNonQuery.

However, PostgreSQL, SQL Server, SQLite and MySQL/MariaDB all support returning the deleted/updated data (RETURNING clause everywhere except for SQL Server, which has the OUTPUT clause).

* We can have separate operators alongside the ones returning the bare count (DeleteAndProject? DeleteReturning?).
* You can specify which columns you want in the RETURNING/OUTPUT clause, so we need to support projection. If no projection is given, we do `RETURNING *`.
* Other than that projection, DELETE and UPDATE can't be used as a general purpose table-returning expression - they're limited. PostgreSQL supports using them in a common table expression (WITH), but not in a subquery. It's mostly for sending results back to the user.
* So these operators would need to return IQueryable:
* If the user composes anything other than Select, they get a translation failure.
* This allows specifying ToArray, AsEnumerable, etc.
* Where the database supports embedding (e.g. PostgreSQL WITH), this would allow that too.
* If no Select is composed, we can return tracked entities just like any normal query
* Ideally, entities returned from Delete should be tracked as Added, while those returned from Update should be Modified.
* AsNoTracking could be used to not track, as usual.
* Note that we previously discussed using AsNoTracking/AsTracking to distinguish between the database-only/change tracker versions (see #24176); but as above, we already need these operators to indicate the tracking of the database-only output...

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.