Can not work with EF Core 5 AsSplitQuery()

[anranruye]

OData WebApi can not work with EF Core 5 AsSplitQuery() api when expand navigation collections.

Assemblies affected

Microsoft.AspNetCore.OData 7.5.2
Microsoft.EntityFrameworkCore 5.0.1
Microsoft.EntityFrameworkCore.SqlServer 5.0.1

Reproduce steps

Call AsSplitQuery() before transfer the IQuerable object to OData WebApi and run any query which expand one or more navigation collections.

Expected result

The query works well and ef core perform separate sql query for each navigation collection.

Actual result

EF Core 5 can not understand the query produced by OData WebApi and throw an error.

Additional detail

In the error message, ef core suggest that single sql query should be used. However, when single sql query loads multiple collections, it may cause performance issues.

EF Core 5 provides QuerySplittingBehavior and AsSingleQuery()/AsSplitQuery() to let the user decide whether to perform single sql query or separate sql queries. But it can not translate the Linq query produced by OData WebApi to separate sql queries in this moment.

The same Linq query produced by OData WebApi can work with EF Core 2.x which can only perform separate sql queries. But EF Core 2.x can not create single sql query, this may also cause performance issues sometimes. And we don't want to fallback to EF Core 2.x since EF Core 5 is obviously more powerful than 2.x version in many ways.

ps: I use Sql Server database, not sure the situation with other data providers this moment.

[anranruye]

Closing this since this is an problem of ef core. It seems that OData can not do better to improve.