Query: Prevent client evaluation and N+1 queries when calling result operators before other operators

[felipepessoto]

Steps to reproduce

Full example: EfCoreMultipleQueriesIssue.zip

IQueryable queryBug = db.Posts.OrderBy(x => x.Id).Include(x => x.PostTags).Where(x => x.PostTags.Any());
//Hundreds of
//SELECT[p].[PostId]
//FROM[PostTag] AS[p]

IQueryable queryBug2 = db.Posts.OrderBy(x => x.Id).Include(x => x.PostTags).Where(x => x.PostCategories.Any());
//Hundreds of
//SELECT[p].[PostId]
//FROM[PostTag] AS[p]

IQueryable queryOk = db.Posts.Where(x => x.PostTags.Any()).OrderBy(x => x.Id).Include(x => x.PostTags);

IQueryable queryOk2 = db.Posts.Include(x => x.PostTags).Where(x => x.PostTags.Any()).OrderBy(x => x.Id);

IQueryable queryOk3 = db.Posts.Include(x => x.PostTags).OrderBy(x => x.Id).Where(x => x.PostTags.Any());

The issue

Looks like part of the query is being evaluated locally, generating all those queries. It's not clear why.

[maumar]

Initially when the query is parsed, each Include call is treated as result operator, effectively producing a subquery, unless it's the last operation, e.g.:

db.Posts.Include(x => x.PostTags).Where(x => x.PostTags.Any()).OrderBy(x => x.Id)

translates to:

from Post x in 
    (from Post x2 in DbSet<Post>
    select x2)
    .Include(x.PostTags)
where 
    (from PostTag <generated>_1 in x.PostTags
    select <generated>_1)
    .Any()
order by x.Id asc
select x

in the next step we extract the include operators out of the query:

from Post x in 
    from Post x2 in DbSet<Post>
    select x2
where 
    (from PostTag <generated>_1 in x.PostTags
    select <generated>_1)
    .Any()
order by x.Id asc
select x

and then we remove redundant subqueries:

from Post x in DbSet<Post>
where 
    (from PostTag <generated>_1 in x.PostTags
    select <generated>_1)
    .Any()
order by x.Id asc
select x

However, if there is orderby before the include

db.Posts.OrderBy(x => x.Id).Include(x => x.PostTags).Where(x => x.PostTags.Any())

we get a more complicated query:

from Post x in 
    (from Post x2 in DbSet<Post>
    order by x2.Id asc
    select x2)
    .Include(x.PostTags)
where 
    (from PostTag <generated>_1 in x.PostTags
    select <generated>_1)
    .Any()
select x

We extract include operators, but we can't remove the additional subquery it generated because of the orderby:

from Post x in 
    from Post x2 in DbSet<Post>
    order by x2.Id asc
    select x2
where 
    (from PostTag <generated>_1 in x.PostTags
    select <generated>_1)
    .Any()
select x

[maumar]

Perhaps, this could be improved for some cases by lifting orderby out of the subquery into the main query:

from Post x in 
    from Post x2 in DbSet<Post>
    order by x2.Id asc
    select x2
select x

could be translated into:

from Post x in 
    from Post x2 in DbSet<Post>
    select x2
order by x.Id asc
select x

and then subquery could be optimized out:

from Post x in DbSet<Post>
order by x.Id asc
select x

If the outer query has an orderby:

from Post x in 
    from Post x2 in DbSet<Post>
    order by x2.Id asc
    select x2
order by x.Name desc
select x

we could do something like this (append inner ordeby to the end of the outer one):

from Post x in DbSet<Post>
order by x.Name desc, x.Id asc
select x

Question is what should we do when there are multiple subqueries due to joins or few levels of subqueries

[maumar]

reassigning to Smit as he is working on a similar issue

[maumar]

poaching this

[maumar]

Fixed in f022d66

[felipepessoto]

Thanks! @maumar