Allow fetching values back with ExecuteUpdate/Delete
- 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
Assessment
This issue has not been assessed yet.