DbContext.Query for arbitrary rootless queries

[ajcvickers]

No description provided.

[ajcvickers]

@smitpatel to fill in detail.

[smitpatel]

public T Query<TContext, T>(Expression<Func<TContext, T>> expression)
{
    return DbContextDependencies.QueryProvider.Execute<T>(expression);
}

public T QueryAsync<TContext, T>(Expression<Func<TContext, T>> expression, CancellationToken cancellationToken = default)
{
    return DbContextDependencies.QueryProvider.ExecuteAsync<T>(expression, cancellationToken);
}

Usage:

context.Query(ctx => new 
    {
        userCount = ctx.Users.Count(),
        animalCount = ctx.Animals.Count()
    });

context.Query(ctx => EF.Functions.StandardDeviationSample(ctx.Products.Select(u => u.UnitPrice)));
context.Query(ctx => string.Join(", ", ctx.Products.Select(p => p.Name)));

// Can also combine above 2
context.Query(ctx => new {
    userCount = ctx.Users.Count(),
    Names = string.Join(", ", ctx.Users.Select(e => e.Name))
});

Or something like this on derived DbContext

[DbFunction]
public int PostReadCount(int postId)
{
  return Query(ctx => ctx.PostReadCount(postId));
}

Translations

SELECT (SELECT COUNT(*) FROM [Users] AS [u0]) AS [userCount], 
    (SELECT COUNT(*) FROM [Animals] AS [a]) AS [animalCount];

SELECT STDEVP([p].[UnitPrice])
FROM [Products] AS [p];

SELECT dbo.PostReadCount(@postId)

For databases which doesn't support SELECT without FROM would use FROM DUAL or equivalent.

Translation of first is very straightforward, we just translate individual subquery and put on projection.
Translation of second would be little more interesting because we are using it only to capture lambda but we printed out subquery without using first SELECT. Combining multiple aggregate operators over the table (Products in this case), would still require GroupBy 1, There is no way to represent that in LINQ easily.
Translation of 3rd is very similar to first one by integrating information of UDFs.

[smitpatel]

Covers issue #27728 by allowing users to use API to run multiple different scalar returning queries in 1 round-trip.
Covers issue #28264 in some way to allow us not having to add queryable versions.
Covers issue #9810 by allowing users to call into API which would automatically translate the function at the same time also capture it inside lambda in other cases for them to compose over.

[roji]

Possibly covers also #30159, which is about allowing arbitrary expressions as arguments of top-level TVF-mapped functions.