Allow fetching values back with ExecuteUpdate/Delete/Insert

[roji]

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 (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 Change-tracking bulk update/delete operations #24176); but as above, we already need these operators to indicate the tracking of the database-only output...

[AgentFire]

Why would we need the entities returned from Delete to be tracked as Added instead of NotTracked?

[roji]

@AgentFire "not tracked" means you're... not tracking; if that's what you want, AsNoTracking is there for that. If you are tracking, then Added seems to be the only state that makes sense, since the entities are no longer in the database (you just deleted them).

But this all still needs to be thought about and designed, we may decide to do something different.