Adjust EF behavior based on query buffering vs. streaming

Start by reading the query execution path around DbDataReader and the existing BufferedDataReader behavior for retrying strategies. Compare the proposed EF-specific buffering operator with the IQueryable ToList and IIListProvider approaches, including eager-loading ORDER BY and performance implications. Done requires a decided design with compatibility and performance conclusions, rather than a single localized edit.

Queries currently execute in streaming mode (see exception below), i.e. they process rows one-by-one from DbDataReader. If eager loading is being performed, this requires EF to add an ORDER BY clause grouping together all rows of an entity, so that related entities can be loaded and returned to the user. There is at least some evidence that these ORDER BY clauses can have negative perf impact, especially when they are over a more complex subquery.

We can allow the user to specify buffering behavior on a given query (i.e. AsBuffering/WithBuffering operator). For tracking queries projecting out entities with eager loading, this would allow us to remove the ORDER BY clause; the entire resultset is consumed before returning the first result (buffering), and related entities can simply be fixed up with identity resolution.

Another advantage: we currently buffer internally (buffered reader) when using a retrying strategy, to make sure that if the user is streaming results and and an exception occurs, data consistency is guaranteed (2nd retry could return different results). If we know that the user is buffering, we don't need to do that any more.

Finally, as an alternative to EF-specific operators (e.g. AsBuffering), we'd ideally get this information automatically when the user executes ToList or similar. This could be done in two ways:

• Introduce an IQueryable overload of ToList (see https://github.com/dotnet/efcore/issues/16730 for an IQueryable overload of ToDictionary). This would inject a ToList node into the query tree, just like IQueryable Single does. The problem is obviously backwards-compat - all older LINQ providers would immediately break, as the node isn't recognized. We're working elsewhere on expression tree versioning, in order to allow newer C# constructs to be used in expression trees - this could be related.
• ToList internally checks whether the source is an IIListProvider (internal interface), and if so, calls ToList on it. This allows specific collection/enumerable types to implement the (optimized) behavior they want when ToList is called. The IQueryable returned by EF Core could implement IIListProvider, and the implementation would inject the new node side-stepping all backwards-compat issues with older LINQ providers.
  • IIListProvider is currently internal and there doesn't seem to be a desire to make it public (https://github.com/dotnet/runtime/issues/48805#issuecomment-786608888)
  • We could also introduce IIDictionaryProvider in a similar way. But that approach doesn't solve the problem of translating the ToDictionary lambdas - only an IQueryable overload of ToDictionary can do that (#16730). So if we want to solve that, we probably just need an IQueryable ToList as well.
  • Thanks to @Emill for suggesting this approach.

Some notes (discussed in design meeting):

• If we go with EF-specific operators, then this behavior needs to be opt-in via an operator and not the default, because it implies a long wait before the first result is returned (so change in latency, perf profile of the query).
• This is an optimization that's orthogonal to query splitting (#18022). However, it's possible that once a good query splitting shortcut is provided, the perf impact of the ORDER BY clause would no longer be significant enough to do this. On the other hand, query splitting isn't always desirable (data consistency issues, multiple roundtrips), so this ORDER BY elimination might still be relevant. Requires a perf investigation.
• Note that we already implement a type of buffering internally when retrying execution strategies are used, although that is at the reader level (BufferedDataReader). The idea here is to use the change tracker as the "buffer".