How to count the number of query results

[dmitryst]

I need to execute query that should contain pagination data.

The request model is the following:

public class ItemsSearchRequest
{
    // several properties that should be used in Where clause omitted for brevity
    
    // requested page number
    public int PageNumber { get; set; }

    // number of found results on one page
    public int PageCountLines { get; set; }
}

The response model is the following:

public class ItemsSearchResponse
{
    // found items
    public IList<Item> Items { get; set; }

    // requested page number
    public int PageNumber { get; set; }

    // total count of found results
    public int Count { get; set; }
}

To return such a response, I use two specifications and then combine the result into response model.

public sealed class ItemsBySearchRequestSpec : Specification<Item, ItemContentDto>
{
    public ItemsBySearchRequestSpec(RpoSearchRequest request) : base()
    {          
        if (!string.IsNullOrWhiteSpace(request.RecipientName))
        {
            Query.Where(e => e.RecipientName.Contains(request.RecipientName));
        }
        // and so on for each property of request model (omitted for brevity)

        Query.Skip(PaginationHelper.CalculateSkip(request.PageCountLines, request.PageNumber))
             .Take(PaginationHelper.CalculateTake(request.PageCountLines));

        Query.Select(e => new ItemContentDto { Content = e.Content });
    }
}

public sealed class ItemsCountBySearchRequestSpec : Specification<Item, ItemIdDto>
{
    public ItemsCountBySearchRequestSpec(RpoSearchRequest request) : base()
    {          
        // That's what I don't like
        // I'm repeating the same where clause as I did in the first specification
        // so, if a developer change it in the first specification and forget to change it here, the results would be wrong
        if (!string.IsNullOrWhiteSpace(request.RecipientName))
        {
            Query.Where(e => e.RecipientName.Contains(request.RecipientName));
        }
        // and so on for each property of request model (omitted for brevity)

        // here I select `Id` just to calculate the count of ids on result of executed query
        Query.Select(e => new ItemIdDto { Id = e.Id });
    }
}

// usage

var itemsSearchSpec = new ItemsBySearchRequestSpec(request);
var itemsCountSearchSpec = new ItemsCountBySearchRequestSpec(request);

var items = await _repository.ListAsync(rpoSearchSpec, cancellationToken);

var result = new ItemsSearchResponse
{
    Count = (await _repository.ListAsync(itemsCountSearchSpec, cancellationToken)).Count(),
    PageNumber = request.PageNumber,
    Items = items
};

I would like to know:

1. Is there any way to get a total count in the first specification to not to use two specifications for it?
2. How can I refactor the code to define Where clause in one place?

[fiseni]

Hi @dmitryst,

We implemented a feature where evaluators are aware if they should be processed for Count operation or not. We have this evaluateCriteriaOnly flag for that. And if you're using Count method of the repository, this flag is set to true, and all pagination expressions are omitted from the evaluation. This was done exactly for this reason, to be able to reuse the same spec. Here is the code in the repository

public virtual async Task<int> CountAsync(ISpecification<T> specification, CancellationToken cancellationToken = default)
{
	return await ApplySpecification(specification, true).CountAsync(cancellationToken);
}

protected virtual IQueryable<T> ApplySpecification(ISpecification<T> specification, bool evaluateCriteriaOnly = false)
{
	return specificationEvaluator.GetQuery(dbContext.Set<T>().AsQueryable(), specification, evaluateCriteriaOnly);
}

The issue is that we have only for specifications that don't contain projections, the Select feature. I don't remember what was the issue, we can analyze that part. You can try and implement CountAsync(ISpecification<T, TResult> specification) and see how it goes.

[dmitryst]

Hi @fiseni ,

I tried to implement CountAsync(ISpecification<T, TResult> specification) like this:

private IQueryable<TResult> ApplySpecification<TResult>(ISpecification<T, TResult> specification, bool evaluateCriteriaOnly = false)
{
    return _specificationEvaluator.GetQuery(_dbContext.Set<T>(), specification, evaluateCriteriaOnly);
}

But I get compile-time error: "CS0266: Cannot implicitly convert type System.Linq.IQueryable{T} to System.Linq.IQueryable{TResult}".

I guess that was the issue, you've mentioned.

[fiseni]

Hm, that's because the method which evaluates specs with the selector in SpecificationEvaluator does not contain that feature, the evaluateCriteriaOnly flag. You will have to override the SpecificationEvaluator too. I can provide you the required changes in the following days if you can wait.

public virtual IQueryable<TResult> GetQuery<T, TResult>(IQueryable<T> query, ISpecification<T, TResult> specification) where T : class
{
	query = GetQuery(query, (ISpecification<T>)specification);

	return query.Select(specification.Selector);
}

[dmitryst]

Yeah, I see that there is no flag evaluateCriteriaOnly in IQueryable<TResult> GetQuery<T, TResult>(IQueryable<T> query, ISpecification<T, TResult> specification).

I would appreciate if you add this feature in the following days. I can wait.

[fiseni]

I just checked the code. Actually, you don't have to do anything. Since ISpecification<T, TResult> inherits from ISpecification<T>, this will just work out of the box. You'll just need to use the CountAsync method of the repository. In your case, the code will be something like:

var itemsSearchSpec = new ItemsBySearchRequestSpec(request);

var items = await _repository.ListAsync(itemsSearchSpec, cancellationToken);
var count = await _repository.CountAsync(itemsSearchSpec, cancellationToken);

var result = new ItemsSearchResponse
{
    Count = count,
    PageNumber = request.PageNumber,
    Items = items
};

The CountAsync method won't evaluate Take, Skip, Ordering, and Include expressions in the specification. Try it out, and let me know if it works.

[dmitryst]

Yes, it works. Thank you for your help and awesome library.

[dylinmaust]

Sorry to bump an old thread. I'm happy to create a new issue if it's appropriate. It's not clear to me how to get the Count using an In Memory collection.

I'd like to keep my API endpoints consistent regardless of if the resource is in memory or remote (supporting ordering, pagination, etc.). Do I have to create a custom Repository implementation which applies the same logic as the provided Repository<T> classes?