Introduction

In OData core  v7.9.0 we added improved support for $count segment in $filter collection properties.

$filter=navProp/$count($filter=prop gt 1) gt 2
$filter=collectionProp/$count($filter=prop gt 1) gt 2

Previously, versions of OData core had support for:

$filter=navProp/$count gt 2
$filter=collectionProp/$count gt 2

We are constantly improving filtering capabilities in OData WebAPI. In OData WebApi v7.5.9 and OData WebApi v8.0.2, we have added support for the queries below

$filter=navProp/$count($filter=prop gt 1) gt 2
$filter=collectionProp/$count($filter=prop gt 1) gt 2
$filter=navProp/$count gt 2
$filter=collectionProp/$count gt 2

According to the spec only $filter or $search query options can be applied to a $count segment.

Note:

For scalar primitive collections, only $filter=collectionProp/$count gt 2 is applicable.

For scalar complex collection, both $filter=collectionProp/$count gt 2 and $filter=collectionProp/$count($filter=prop gt 1) gt 2 are supported.

Prerequisites

Let us create an ASP.NET Core Application using Visual Studio 2019.

Note: The instructions in this blog post will focus on setting up OData WebAPI v7.x. If interested in using it in the ASP.NET Core OData 8.0 Preview for .NET 5, you can set up using the instructions in this link.

We install the following nuget packages:

• Microsoft.AspNetCore.OData -version 7.5.9

CLR Model

public class Book
{
    public int Id { get; set; }
    public string Isbn { get; set; }
    public string Title { get; set; }
    public int Year { get; set; }
    public ICollection<Author> Authors { get; set; }
}

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Book> Books { get; set; }
}

Controllers

public class BooksController : ODataController
{
    BookLibDbContext db;
    public BooksController(BookLibDbContext db)
    {
        this.db = db;
    }

    [EnableQuery]
    public IQueryable<Book> Get()
    {
        return db.Books.AsQueryable<Book>();
    }

    [EnableQuery]
    public Book Get(int key)
    {
        return db.Books.Where(b => b.Id == key).Single();
    }

    //.... Other controller methods
    //....
}

public class AuthorsController : ODataController
{
    BookLibDbContext db;

    public AuthorsController(BookLibDbContext db)
    {
        this.db = db;
    }

    [EnableQuery]
    public IQueryable<Author> Get()
    {
        return db.Authors.AsQueryable<Author>();
    }

    [EnableQuery]
    public Author Get(int key)
    {
        return db.Authors.Where(a => a.Id == key).FirstOrDefault();
    }

    //.... Other controller methods
    //....
}

The above will allow having two OData entity sets that we are going to query: http://localhost:5000/odata/Books and http://localhost:5000/odata/Authors

Now we are ready to try a few filter queries.

Example: 1

Return all books with more than one author.

GET http://localhost:5000/odata/Books?$filter=Authors/$count gt 1

The query will only return books whose count of Authors is greater than 1. Note since we did not expand Authors, we will not have Authors in the response.

{
    "@odata.context": "http://localhost:5000/odata/$metadata#Books",
    "value": [
        {
            "Id": 1,
            "Isbn": null,
            "Title": "B1",
            "Year": 1990
        },
        ....
    ]
}

The expression generated will resemble as follows:

$it.Books.Where($it => ($it.Authors.LongCount() > 1)

Example 2

Return all books with more than two authors, and the Author Id greater must be greater than 1.

GET http://localhost:5000/odata/Books?$filter=Authors/$count($filter=Id gt 1) gt 2

In the above query, please note that the inner $filter will be evaluated first, followed by the $count.

Below is the response:

{
    "@odata.context": "http://localhost:5000/odata/$metadata#Books",
    "value": [
        {
            "Id": 2,
            "Isbn": null,
            "Title": "B2",
            "Year": 1995
        }
    ]
}

The expression generated will resemble as follows:

$it.Books.Where($it => ($it.Authors.Where($it => ($it.Id > 1).LongCount() > 2)