Webapi: $count=true is considering $top in count result

[JairoMarques]

My problem is when using $top, @odata.count always return the number informed in $top when according with the specification "The returned count MUST NOT be affected by $top, $skip, $orderby, or $expand".

Assemblies affected

Microsoft.AspNetCore.OData 7.2.1

Reproduce steps

My solution was inspired in the solution from this link: https://stackoverflow.com/questions/48662826/automapper-dont-work-with-entity-ef-core/57384700#57384700

There is entities:

public class LessonCatalog {
    public string Name { get; set; }
    public int? ImageId { get; set; }
    public virtual Image Image { get; set; }
    public virtual ICollection<Lesson> Lessons { get; set; }
}

public class Lesson {
    public string Name { get; set; }
    public string Description { get; set; }
    public int? ImageId { get; set; }
    public virtual Image Image { get; set; }
    public int LessonCatalogId { get; set; }
    public virtual LessonCatalog LessonCatalog { get; set; }
}

Views:

public class LessonView {
    public string Name { get; set; }
    public string Description { get; set; }
    public int? ImageId { get; set; }
    public ImageView Image { get; set; }
    public int LessonCatalogId { get; set; }
    public LessonCatalogView LessonCatalog { get; set; }
}

public class LessonCatalogView {
    public string Name { get; set; }
    public int? ImageId { get; set; }
    public ImageView Image { get; set; }
    public IEnumerable<LessonView> Lessons { get; set; }
}

My maps:

CreateMap<LessonCatalog, LessonCatalogView>()
            .ForMember(dest => dest.Image, map => map.ExplicitExpansion())
            .ForMember(dest => dest.Lessons, map => map.ExplicitExpansion());

CreateMap<Lesson, LessonView>()
             .ForMember(dest => dest.LessonCatalog, map => map.ExplicitExpansion());

In my repository:

protected readonly DbContext _context;
protected readonly DbSet<TEntity> _entities;

public Repository(DbContext context) {
        _context = context;
        _entities = context.Set<TEntity>();
     }

public IEnumerable<TView> GetOData<TView>(ODataQueryOptions<TView> query,
        Expression<Func<TEntity, bool>> predicate = null) {

        IQueryable<TEntity> repQuery = _entities.AsQueryable();
        IQueryable res;
        if (predicate != null) repQuery = _entities.Where(predicate);

        if (query != null) {
            string[] expandProperties = GetExpands(query);
            //!!!
            res = repQuery.ProjectTo<TView>(Mapper.Configuration, null, expandProperties);
            //!!!
            var settings = new ODataQuerySettings();
            var ofilter = query.Filter;
            var orderBy = query.OrderBy;
            var skip = query.Skip;
            var top = query.Top;

            if (ofilter != null) res = ofilter.ApplyTo(res, settings);
            if (orderBy != null) res = orderBy.ApplyTo(res, settings);
            if (skip != null) res = skip.ApplyTo(res, settings);
            if (top != null) res = top.ApplyTo(res, settings);
        } else {
            res = repQuery.ProjectTo<TView>(Mapper.Configuration);
        }

        return (res as IQueryable<TView>).AsEnumerable();
    }

Considering my query has 1007 records, if I use
…$count=true&$top=5

Expected result

"@odata.count": 1007

Actual result

"@odata.count": 5

Additional detail

Using SQL Server Profile I can see that the Select for count is including the “top”. So, how to avoid this to happen?

[JairoMarques]

After a very deep research in Microsoft.AspNetCore.OData code, I detected that the problem is in ODataResourceSetSerializer.cs

        public virtual ODataResourceSet CreateResourceSet(IEnumerable resourceSetInstance, IEdmCollectionTypeReference resourceSetType,
            ODataSerializerContext writeContext)
        {
...

long? countValue = writeContext.InternalRequest.Context.TotalCount;

At this point the Select Count(*) is executed but using then entire query, including TOP

I tried to change the QueryOptions to remove the TOP, but the things is more complex. Any help?

[genusP]

@JairoMarques, for this scenario you should manually calculate total records count.
For this, you should setup TotalCount or TotalCountFunc property of ODataFeature on action your controller.
```c#
using Microsoft.AspNet.OData;
using Microsoft.AspNet.OData.Extensions;

…
public IActionResult Get() {
//exec your repo logic
var data = repository.GetOData(queryOptions);

if(queryOptions.Count?.Value == true)
{
Request.ODataFeature().TotalCount = 10; //your calculated value
//or
Request.ODataFeature().TotalCountFunc = CalcTotal; //your function for calculate records count
}
}
```

[JairoMarques]

@genusP,

it's a nice workaround and I'm use it and it works brilliant! Thanks a lot!

But, in your opinion, and inspecting the Microsoft.AspNet.OData Library, this TotalCount couldn't be easily calculated based in the query and should be automatic?

[genusP]

Library waits in actionresult query without apply query options. If you have applied them, then for correct calculate counts of records you need implement its self.

[JairoMarques]

I agree with @genusP and could solve my problem. Thanks a lot!

[JairoMarques]

I apply the following solution:

                if (ofilter != null) res = ofilter.ApplyTo(res, settings);
                if (query.Count?.Value == true)
                {
                    // We should calculate TotalCount only with filter
                    // http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part2-url-conventions.html#_Toc371341773
                    // 4.8 Addressing the Count of a Collection
                    // "The returned count MUST NOT be affected by $top, $skip, $orderby, or $expand.
                    query.Request.ODataFeature().TotalCount = ((IQueryable<TView>)res).LongCount();
                }

                if (top != null) res = top.ApplyTo(res, settings);
                if (orderBy != null) res = orderBy.ApplyTo(res, settings);
                if (skip != null) res = skip.ApplyTo(res, settings);

[JairoMarques]

Hi @genusP,

using the solution above, $count works great, but if I use $skip the result is not serialized.

I'm using:
http://localhost:5000/odata/users?$select=name,usercode&$orderby=usercode&$top=15&$count=true&$skip=99

The query in Profiler is:

exec sp_executesql N'SELECT [t].[Name], [t].[UserCode]
FROM (
    SELECT [i].[Name], [i].[UserCode]
    FROM [Users] AS [i]
    ORDER BY (SELECT 1)
    OFFSET @__TypedProperty_0 ROWS FETCH NEXT @__TypedProperty_1 ROWS ONLY
) AS [t]
ORDER BY [t].[UserCode]',N'@__TypedProperty_0 int,@__TypedProperty_1 int',@__TypedProperty_0=99,@__TypedProperty_1=15

And is resulting 15 records:

Emmett Grimes   usr202
Clyde Jenkins   usr266
Alfredo Kuvalis usr280
Laurence Howell usr302
Oliver Turner   usr335
Floyd Mohr  usr354
Maurice Walsh   usr373
April Grady usr595
Jermaine Osinski    usr633
Edna McCullough usr665
Sue VonRueden   usr683
Carrie Jacobson usr696
Frances Parisian    usr862
Brendan Stamm   usr881
Marion Lubowitz usr914

But the result is not serialized:

{
    "@odata.context": "http://localhost:5000/odata/$metadata#Users(name,userCode)",
    "@odata.count": 1008,
    "value": []
}

I'm using Microsoft.AspNetCore.OData 7.2.1

Looks like something in ODataOutputFormatter.cs

                ODataOutputFormatterHelper.WriteToStream(
                    type,
                    context.Object,
                    request.GetModel(),
                    ResultHelpers.GetODataResponseVersion(request),
                    baseAddress,
                    contentType,
                    new WebApiUrlHelper(request.GetUrlHelper()),
                    new WebApiRequestMessage(request),
                    new WebApiRequestHeaders(request.Headers),
                    (services) => ODataMessageWrapperHelper.Create(response.Body, response.Headers, services),
                    (edmType) => serializerProvider.GetEdmTypeSerializer(edmType),
                    (objectType) => serializerProvider.GetODataPayloadSerializer(objectType, request),
                    getODataSerializerContext);

in (objectType) => serializerProvider.GetODataPayloadSerializer(objectType, request), is not serializing the payload when using $skip.

Deepling investigating I arrived to

private void WriteResourceSet(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext)

in ODataResourceSetSerializer

The enumerable variable when using $skip has expression with

{value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Direction.Domain.Entities.User]).Where(i => True).Select(dtoUser => new UserView() {Name = dtoUser.Name, UserCode = dtoUser.UserCode}).Skip(value(Microsoft.AspNet.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]).TypedProperty).Take(value(Microsoft.AspNet.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]).TypedProperty).OrderBy($it => $it.UserCode).OrderBy($it => $it.UserCode).ThenBy($it => $it.Id).Select(Param_0 => new SelectSome`1() {ModelID = value(Microsoft.AspNet.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[System.String]).TypedProperty, Container = new NamedPropertyWithNext1`1() {Name = "name", Value = Param_0.Name, Next0 = new NamedProperty`1() {Name = "userCode", Value = Param_0.UserCode}, Next1 = new AutoSelectedNamedProperty`1() {Name = "id", Value = Convert(Param_0.Id, Nullable`1)}}}).Skip(value(Microsoft.AspNet.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]).TypedProperty).Take(value(Microsoft.AspNet.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]).TypedProperty)}

This way, "Enumeration yielded no results"

Any idea how to solve this problem?

[genusP]

I see your expression double applied skip and take. If you use EnableQuery on controller method, remove it.

[JairoMarques]

I already did it. But when I remove EnableQuery, all Fields in the query are returned.
But instead I want only the select or expand fields.

This only happen when I apply skip

[genusP]

EnableQueryAttribute implements apply query options. If you customize this logic, you need remove this attribute and manual implement apply all query options.

[JairoMarques]

Thanks @genusP.

I override ApplyQuery from EnableQueryAttribute and remove the $skip from QueryString. So, the skip was not doubling executing.

Problem solved!