Webapi: $top query option does not work properly on DTO based controller in .NET Core

[JafarAmiri]

I'm migrating my application to .Net Core. I have some DTO based OData controllers that worked correctly on previous version of .Net. But in .Net Core the performance of these controllers are not good. I tried to find the cause of the problem and I realized that there is no "top" statement in queries sent to SQL server. It seems that when we have a OData controller on an entity, every thing is OK, but when we have a OData controller on a DTO, some query options like $top are applied in application side not database side. So, The controller performance is very low.
Is there a solution in .NET Core to make this work properly?

Assemblies affected

Microsoft.AspNetCore.All, Version="2.2.0"
Microsoft.AspNetCore.OData Version="7.1.0"
Microsoft.OData.Core Version="7.5.3"

Reproduce steps
Create two models for Product and ProductDTO and a Get method on ProductDtoController:
public class ProductDtoController : ODataController
{
...
public IActionResult Get()
{
var query = _productRepository.GetAll()
.Select(x => new ProductDto
{
Id = x.Id,
Name = x.Name,
CategoryName = x.Category.Name
}
return Ok(query);
}
...
}

Call an address like this with $top:
/odata/ProductDto?$count=true&$top=10

Check the SQL Server profiler and you will see this query without "top" statement:

SELECT [e].[Id], [e].[Name], [e.Category].[Name] AS [CategoryName]
FROM [dbo].[Products] as [e]
INNER JOIN [dbo].[Categories] AS [e.Category] ON [e].[CategoryId] = [e.Category].[Id]

Expected result
SELECT TOP(10) [e].[Id], [e].[Name], [e.Category].[Name] AS [CategoryName]
FROM [dbo].[Products] as [e]
INNER JOIN [dbo].[Categories] AS [e.Category] ON [e].[CategoryId] = [e.Category].[Id]

[kosinsky]

You need to decorate Get() method with [EnableQuery] attribute. For example
https://github.com/OData/WebApi/blob/3b1ecc572088ff1c1abc5147b29191cfe3afbe58/samples/AspNetCoreODataSample.Web/Controllers/MoviesController.cs#L80

[JafarAmiri]

Hi @kosinsky,
My Get method has [EnableQuery] attribute in real project and current issue comment does not contain entire implementation of my controller. But the issue is that the EF Core does not understand the expression generated by OData properly and executes a part of query on database side and loads related data to application and executes the rest of query on application side. So, this issue leads to low performance with .Net Core + OData + EF Core on some queries.

[kosinsky]

Looks like an edge case for EF Core. AFAIK, WebAPI will execute two queries to implement '$count=true&$top=10' one to do $count and one to do $top.

Could you check what queries are executed when you do just $top=10?

EF Core could do "client side evaluation". Could you add into EF Context OnConfiguring() method following code:
optionsBuilder.ConfigureWarnings(error => error.Throw(RelationalEventId.QueryClientEvaluationWarning));
and share detailed exception.

[JafarAmiri]

There is no difference between queries are executed with $count and without it. I added your code into EF Context OnConfiguring() method and this is detailed exception:

InvalidOperationException: Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression 'orderby new ProductDto() {Id = [e].Id, Name = [e].Name, CategoryName = [e.Category].Name}.Id asc' could not be translated and will be evaluated locally.'. This exception can be suppressed or logged by passing event ID 'RelationalEventId.QueryClientEvaluationWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.

Why the query tries to execute order by on { new ProductDto() ...}.Id whereas my query doesn't contain any order by expression?

[kosinsky]

$top=10 requires Stable sorting to get the same 10 elements (no matter how DB decides to execute the query). You could try to disable stable sorting with:
[EnableQuery(EnsureStableOrdering = false)]

[JafarAmiri]

I Added EnsureStableOrdering = false to EnableQuery attribute and the issue has been fixed. but when I tries to call the following query with $filter:

/odata/ProductDto?$filter=Id eq 5&$count=true&$top=10

I get new error:
_An unhandled exception occurred while processing the request.
InvalidOperationException: Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression 'where (new ProductDto() {Id = [e].Id, Name = [e].Name, CategoryName = [e.Category].Name}.Id == __TypedProperty_0)' could not be translated and will be evaluated locally.'. This exception can be suppressed or logged by passing event ID 'RelationalEventId.QueryClientEvaluationWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'._