Make AsSubQuery public

[InspiringCode]

Hi @virzak,

is there any chance you could make DbFunctionsExtensions.AsSubQuery public?

Unfortunately, EF Core has some limitations (and there will probably always be some limitations) in regard to the way it generates SQL. I am writing a query in my current project, where I need to filter on quite a few subselect projections, which generates horribly inefficient SQL at the time (see this EF core issue).

I was trying to reimplement the AsSubQuery-functionality as a separate extension, but then I can't use your efcore-extensions anymore because we both would replace the NpgsqlQueryableMethodTranslatingExpressionVisitorFactory which doesn't work.

Since I really need this functionality (other attempts to optimize the query have failed), I am a bit out of look. What do you think?

[virzak]

This was explicitly recommended against by the ef core team.

Can you provide minimum repro of where the latest prerelease fails?

[InspiringCode]

You can take the simple example from the first post of the linked issue.

What the EF Core team meant was, that it would not be good to include AsSubQuery in the official EF Core package because the described issue should ideally be solved by the query processor.

But since there is not even a milestone yet when the query generator will be redesigned, we need a solution until then (which might be EF11, EF12 or even later).

And AsSubQuery is the only solution (workaround) I can think of.

[virzak]

Ok, I'll look into it on the weekend

[InspiringCode]

var q1 = ctx
    .Blogs
    .Select(p => new
    {
        p.Name,
        PostCount = p.Posts.Count
    })
    .AsSubQuery()
    .Where(p => p.PostCount > 5)
    .ToList();

Adding AsSubQuery to the LINQ statement above fixes the issue.

[InspiringCode]

@virzak Any news on this? Is there still a good argument left to not make this method public?

[virzak]

@InspiringCode apologies, haven't had the chance to look into this. This weekend. Promise.

In the meantime, if you can't wait, you can easily make it public yourself and throw the packages on myget or something. That's what I do while waiting for other people to resolve issues.

[InspiringCode]

@virzak Any updates on this?

[virzak]

Really sorry, been extremely busy to do anything. Broke my promise too.

Have you made a temp myget package just for yourself?

[InspiringCode]

@virzak I worked on a different project in the meantime. Is there any update on this? I mean it's just a single keyword that would have to be changed (namely internal to public). Would be really helpful if you could do this.

[virzak]

@InspiringCode, that single keyword could mean breaking change if I wanted to remove it later.

This project is set up to upload to myget. You can change from internal to public, pack the package and upload to myget.

efcore-extensions/.github/workflows/build.yml

Lines 139 to 143 in 799e9db

	- name: Push to MyGet
	if: ${{ env.MYGET_PUSH_KEY != '' }}
	run: dotnet nuget push packages\*.nupkg -s https://www.myget.org/F/zomp-efcore-extensions/api/v3/index.json -k ${{ secrets.MYGET_PUSH_KEY }}
	env:
	MYGET_PUSH_KEY: ${{ secrets.MYGET_PUSH_KEY }}

That's the way to go about it. I'll look at this issue in November at the latest.

[sobiemir]

@InspiringCode
If you still need this, you can add this piece of code to your project:

public static class DbFunctionsExtensions
{
    public static IQueryable<TEntity> AsSubQuery<TEntity>(this IQueryable<TEntity> source)
    {
        ArgumentNullException.ThrowIfNull(source);
        return source.Provider.CreateQuery<TEntity>(
            Expression.Call(
                null,
                DbFunctionsExtensions.AsSubQueryMethod.MakeGenericMethod(typeof(TEntity)),
                source.Expression));
    }
    
    internal static readonly MethodInfo AsSubQueryMethod = typeof(DbFunctionsExtensions)
        .GetMethod(nameof(DbFunctionsExtensions.AsSubQuery))
            ?? throw new NullReferenceException();
}

Now you can use AsSubQuery on IQueryable.

The answer to the question "how it works?" is here:
https://github.com/zompinc/efcore-extensions/blob/master/src/Zomp.EFCore.WindowFunctions/Extensions/SubQueryProcessor.cs

To be more specific, here:

if (methodCallExpression.Method.Name == nameof(DbFunctionsExtensions.AsSubQuery))