How to change $count query on server side?

[ycymio]

Hello

I am trying to implement <EntitySet>/$count API in server side, which means $count is calculated in server side, not return a collection as getting the whole collection is a waste of resource.

I did some test to return an int value directly but [EnableQuery] attribute will call ValidateSelectExpandOnly() and throw exceptions. Is there any way I can directly implement $count, not creating an API with another name?

[julealgon]

/$count API in server side, which means $count is calculated in server side, not return a collection as getting the whole collection is a waste of resource.

I think you have a misunderstanding there. Count doesn't "get the whole collection" usually, provided you properly exposed IQueryable from your controller. All $count does is do a .Count() on top of the queryable, which will generate a select count in the database assuming you exposed the queryable. It will not get the whole collection.

Now, assuming you still want to do this, I believe there are 2 options:

1. Use attribute routing to model the $count route explicitly. Don't use [EnableQuery] on that one.
2. Pass a ODataQueryOptions<T> instance to your endpoint and inspect it to see whether $count was requested, and perform the logic based on that. Don't use [EnableQuery] here either.

...not creating an API with another name?

Not exactly sure what you mean over here, but I assume you mean that you want to implement the logic without creating a custom bound action, and instead keep with the OData standard protocol? If so, both of the above will get you that.

[xuzhg]

@ycymio As mentioned from @julealgon, OData generates the correct 'Count(*)' expression and runs the expression at Database side. So, there's no whole collection returning from database.

at OData, there are two 'count' related:

1. $count segment, for example: ~/entityset/$count . This will only return the 'raw count value'.
2. $count query, for example: /entityset?$count=true. This will include '@odata.count' into the OData payload.

I noticed there's a OData v8 related video at: https://www.youtube.com/watch?v=dWKpDSTpFQk&t=504s. Near the end of the video, the author demos the "$count segment" and "$count query". Please take a look and let us know further questions.

of course, thanks @julealgon 's inputs.

[AntaraG19]

I've been checking the video and the partner's code. But when we talk about receiving the $count segment (/entity/$count) The code does not work, it does not provide the solution no matter how much it shows in the video that it works. All its logic and functionalities do not give that result. In the YouTube video itself there is a comment that says so. Likewise reviewing your blog documentation. It gives case 2 as an example, $count query (/entity?$count=true), it does not put anything about case 1.

Curious isn't it? No matter how many times I think about my code, I have this same problem with a NetCore 8 project. No problem in extracting the oData.count, but when what we want is the numerical value there is no way. Options?

[julealgon]

@AntaraG19 I'm sorry, maybe this is just me, but I really don't understand what you are asking there. Could you perhaps rephrase your question and/or provide a bit more detail, or even a sample?

[AntaraG19]

I rephrase the question. There are two options to implement the count:

1. $count segment, for example /entity/$count, should return the longCount. Simply the numerical value.
2. $count query, for example /entity?$count=true, which returns the collection.

@xuzhg shows us a video to review and test the solution he tells us. Which uses at that time the latest version of NetCore 7 and the implementation of all this. Well, my question is that following the code, everything, when we want to extract option 1, that is, the numerical value, continues to return the collection, not the value. Following the documentation attached to the video, we verify that the functionality for option 2 is indeed correct but there is nothing about the implementation for option 1, since we assume that it is direct from oData, but it is not, oData returns the collection. My question is how do we return the numerical value? Because currently for versions on NetCore 8, it does not work even though the code

public async Task<ActionResult<IEnumerable<TDto>>> GetAsync(ODataQueryOptions<TDto> options)
{
    return Ok(await Service.GetAsync(UnitOfWork, options));
}

 /// <summary>
 /// Gets all Entities asynchronously
 /// </summary>
 public virtual Task<IQueryable<TDto>> GetAsync(IUnitOfWork uow, ODataQueryOptions<TDto> options)
 {
     return GetAsync(uow, options, null);
 }

public static async Task<IQueryable<TModel>> GetQueryAsync<TModel, TData>(this IQueryable<TData> query, IMapper mapper, ODataQueryOptions<TModel> options, QuerySettings querySettings = null)
    where TModel : class
{
    Expression<Func<TModel, bool>> filter = options.ToFilterExpression<TModel>(
             querySettings?.ODataSettings?.HandleNullPropagation ?? HandleNullPropagationOption.False,
             querySettings?.ODataSettings?.TimeZone);
        
    await query.ApplyOptionsAsync(mapper, filter, options, querySettings);
    return query.GetQueryable(mapper, options, querySettings, filter);
}

public static async Task ApplyOptionsAsync<TModel, TData>(this IQueryable<TData> query, IMapper mapper, Expression<Func<TModel, bool>> filter, ODataQueryOptions<TModel> options, QuerySettings querySettings)
{
    ApplyOptions(options, querySettings);
    if (options.Count?.Value == true)
        options.AddCountOptionsResult(await query.QueryLongCountAsync(mapper, filter, querySettings?.AsyncSettings?.CancellationToken ?? default));
}

/// <summary>
/// Adds the count options to the result.
/// </summary>
/// <param name="options"></param>
/// <param name="longCount"></param>
public static void AddCountOptionsResult(this ODataQueryOptions options, long longCount)
{
    if (options.Count?.Value != true)
        return;

    options.Request.ODataFeature().TotalCount = longCount;
}

This has a response 200. And since the two requests are of type count in the oData, there is no way to differentiate them in any optimal way, without having to create specific calls to the controller that involve redoing the routing in the Controller. The only viable option, of all the ones I have tried and reviewed (including the video mentioned), that I have seen viable is to add a dictionary so that the response when it is type 1 (entity/$count) does not give a response 200 . But of course this returns the collection not the value.

[julealgon]

@AntaraG19 did you try just creating a separate endpoint with this as its route template? /entity/$count?