Relational: Provide query execution escape hatch APIs that operates a the whole query level

[divega]

The SQL you pass to the FromSql*() (e.g. FromSqlRaw) method is used as a substitute for whatever the query root represented by the current IQueryable is mapped to in the database. As a consequence:

• If FromSql() is called on a query that doesn't have a single query root (e.g. because multiple query roots have been combined in a join), we throw at runtime
• When Include() is called on a query with FromSql(), we expect the SQL to only supply columns for the query root, so we still need to turn the user provided SQL into a subquery and generate additional JOINs
• If the source of the data is not composable (e.g. a stored procedure) it is not possible to materialize a graph of objects (see Allow keyless types with owned types #14497)

This issue is about providing alternative APIs that allow supplying a substitute SQL statement (or store procedure call) or DbDataReader for the whole query. The user provided SQL or DbDataReader will be assumed to contain all the necessary columns used by the materializer, regardless of whether Include() or implicit eager loading (e.g. aggregates), or multiple query roots are present.

[ajcvickers]

Consider also the case where the shape of the query is pre-determined and hence will not match what EF is expecting, as described in #16863. (We may choose not to support this.)

[tola]

@ajcvickers Thanks for choosing to add #16863 to this related issue. I hope the team will consider this as many of the .NET developers are dealing with Stored Procedures of SQL Server almost always. This is much needed for those who work with SQL script or Stored Procedure in EF Core.