Suspect suboptimal query logic when combining primary filter, paging and included resource

[bjornharrtell]

SUMMARY

I've been looking into performance issues when making a filtered request that also includes some related resource collection and pagination is enabled and I suspect the query strategy is suboptimal.

DETAILS

It seems the query that is being built will use row_number window function on the full set of related resources, filtering applied afterwards.

STEPS TO REPRODUCE

Not available yet. Working on a full reproduction as standalone repo, or possibly as fork of JsonApiDotNetCore.

VERSIONS USED

• JsonApiDotNetCore version: 5.7.1
• ASP.NET Core version: Latest on .NET 9.0
• Entity Framework Core version: 9.0.5
• Database provider: Npgsql.EntityFrameworkCore.PostgreSQL 9.0.4

[bjornharrtell]

In the case I'm investigating, the related entites are of bounded amounts that we do not need to paginate. So we can actually workaround this by setting page size to 0 for the related included entities. Would be fun go dig deeper though... I'm suspecting the generated query could be optimized by a lateral join or forced filter order by using ctes.

[bkoelman]

Thanks for sharing. I'm curious what you'll come up with.

Please use the master branch for exploration. It contains #1735, which eliminates subqueries when pagination is turned off in includes.