{"id":4563,"date":"2021-08-26T12:19:16","date_gmt":"2021-08-26T19:19:16","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/odata\/?p=4563"},"modified":"2021-08-26T12:19:16","modified_gmt":"2021-08-26T19:19:16","slug":"adding-support-for-count-segment-in-filter-collections-in-odata-webapi","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/odata\/adding-support-for-count-segment-in-filter-collections-in-odata-webapi\/","title":{"rendered":"Adding support for $count segment in $filter collections in OData WebAPI"},"content":{"rendered":"<h2>Introduction<\/h2>\n<p>In OData core\u00a0 v7.9.0 we added improved support for $count segment in $filter collection properties.<\/p>\n<pre class=\"prettyprint\">$filter=navProp\/$count($filter=prop gt 1) gt 2\r\n$filter=collectionProp\/$count($filter=prop gt 1) gt 2<\/pre>\n<p>Previously, versions of OData core had support for:<\/p>\n<pre class=\"prettyprint\">$filter=navProp\/$count gt 2\r\n$filter=collectionProp\/$count gt 2<\/pre>\n<p>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<\/p>\n<pre class=\"prettyprint\">$filter=navProp\/$count($filter=prop gt 1) gt 2\r\n$filter=collectionProp\/$count($filter=prop gt 1) gt 2\r\n$filter=navProp\/$count gt 2\r\n$filter=collectionProp\/$count gt 2<\/pre>\n<p>According to the <a href=\"http:\/\/docs.oasis-open.org\/odata\/odata\/v4.01\/cs01\/part1-protocol\/odata-v4.01-cs01-part1-protocol.html#sec_RequestingtheNumberofItemsinaCollect\" target=\"_blank\" rel=\"noopener\">spec<\/a> only <code>$filter<\/code> or <code>$search<\/code> query options can be applied to a <code>$count<\/code> segment.<\/p>\n<p>Note:<\/p>\n<p>For scalar primitive collections, only <code>$filter=collectionProp\/$count gt 2<\/code> is applicable.<\/p>\n<p>For scalar complex collection, both <code>$filter=collectionProp\/$count gt 2<\/code> and <code>$filter=collectionProp\/$count($filter=prop gt 1) gt 2<\/code> are supported.<\/p>\n<h2><strong>Prerequisites<\/strong><\/h2>\n<p>Let us create an ASP.NET Core Application using Visual Studio 2019.<\/p>\n<p>Note: The instructions in this blog post will focus on setting up OData WebAPI v7.x. If interested in using it in the <strong>ASP.NET Core OData 8.0 Preview for .NET 5<\/strong>, you can set up using the instructions in this <a href=\"https:\/\/devblogs.microsoft.com\/odata\/asp-net-odata-8-0-preview-for-net-5\/\" target=\"_blank\" rel=\"noopener\">link<\/a>.<\/p>\n<p>We install the following nuget packages:<\/p>\n<ul>\n<li>Microsoft.AspNetCore.OData -version 7.5.9<\/li>\n<\/ul>\n<h2><strong>CLR Model<\/strong><\/h2>\n<pre class=\"prettyprint\">public class Book\r\n{\r\n    public int Id { get; set; }\r\n    public string Isbn { get; set; }\r\n    public string Title { get; set; }\r\n    public int Year { get; set; }\r\n    public ICollection&lt;Author&gt; Authors { get; set; }\r\n}\r\n\r\npublic class Author\r\n{\r\n    public int Id { get; set; }\r\n    public string Name { get; set; }\r\n    public ICollection&lt;Book&gt; Books { get; set; }\r\n}<\/pre>\n<h2>Controllers<\/h2>\n<pre class=\"prettyprint\">public class BooksController : ODataController\r\n{\r\n    BookLibDbContext db;\r\n    public BooksController(BookLibDbContext db)\r\n    {\r\n        this.db = db;\r\n    }\r\n\r\n    [EnableQuery]\r\n    public IQueryable&lt;Book&gt; Get()\r\n    {\r\n        return db.Books.AsQueryable&lt;Book&gt;();\r\n    }\r\n\r\n    [EnableQuery]\r\n    public Book Get(int key)\r\n    {\r\n        return db.Books.Where(b =&gt; b.Id == key).Single();\r\n    }\r\n\r\n    \/\/.... Other controller methods\r\n    \/\/....\r\n}<\/pre>\n<pre class=\"prettyprint\">public class AuthorsController : ODataController\r\n{\r\n    BookLibDbContext db;\r\n\r\n    public AuthorsController(BookLibDbContext db)\r\n    {\r\n        this.db = db;\r\n    }\r\n\r\n    [EnableQuery]\r\n    public IQueryable&lt;Author&gt; Get()\r\n    {\r\n        return db.Authors.AsQueryable&lt;Author&gt;();\r\n    }\r\n\r\n    [EnableQuery]\r\n    public Author Get(int key)\r\n    {\r\n        return db.Authors.Where(a =&gt; a.Id == key).FirstOrDefault();\r\n    }\r\n\r\n    \/\/.... Other controller methods\r\n    \/\/....\r\n}<\/pre>\n<p class=\"x-hidden-focus\">The above will allow having two OData entity sets that we are going to query: <strong>http:\/\/localhost:5000\/odata\/Books <\/strong>and\u00a0<strong>http:\/\/localhost:5000\/odata\/Authors<\/strong><\/p>\n<p class=\"x-hidden-focus\">Now we are ready to try a few filter queries.<\/p>\n<h4>Example: 1<\/h4>\n<p>Return all <strong>books<\/strong> with more than one <strong>author<\/strong>.<\/p>\n<pre class=\"prettyprint\">GET http:\/\/localhost:5000\/odata\/Books?$filter=Authors\/$count gt 1<\/pre>\n<p>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.<\/p>\n<pre class=\"prettyprint\">{\r\n    \"@odata.context\": \"http:\/\/localhost:5000\/odata\/$metadata#Books\",\r\n    \"value\": [\r\n        {\r\n            \"Id\": 1,\r\n            \"Isbn\": null,\r\n            \"Title\": \"B1\",\r\n            \"Year\": 1990\r\n        },\r\n        ....\r\n    ]\r\n}<\/pre>\n<p>The expression generated will resemble as follows:<\/p>\n<pre class=\"prettyprint\">$it.Books.Where($it =&gt; ($it.Authors.LongCount() &gt; 1)<\/pre>\n<h4>Example 2<\/h4>\n<p>Return all books with more than two authors, and the Author Id greater must be greater than 1.<\/p>\n<pre class=\"prettyprint\">GET http:\/\/localhost:5000\/odata\/Books?$filter=Authors\/$count($filter=Id gt 1) gt 2<\/pre>\n<p>In the above query, please note that the inner $filter will be evaluated first, followed by the $count.<\/p>\n<p>Below is the response:<\/p>\n<pre class=\"prettyprint\">{\r\n    \"@odata.context\": \"http:\/\/localhost:5000\/odata\/$metadata#Books\",\r\n    \"value\": [\r\n        {\r\n            \"Id\": 2,\r\n            \"Isbn\": null,\r\n            \"Title\": \"B2\",\r\n            \"Year\": 1995\r\n        }\r\n    ]\r\n}<\/pre>\n<p>The expression generated will resemble as follows:<\/p>\n<pre class=\"prettyprint\">$it.Books.Where($it =&gt; ($it.Authors.Where($it =&gt; ($it.Id &gt; 1).LongCount() &gt; 2)<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction In OData core\u00a0 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 [&hellip;]<\/p>\n","protected":false},"author":20598,"featured_media":3253,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1472,1,117],"tags":[],"class_list":["post-4563","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-asp-net-core","category-odata","category-webapi"],"acf":[],"blog_post_summary":"<p>Introduction In OData core\u00a0 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 [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/posts\/4563","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/users\/20598"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/comments?post=4563"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/posts\/4563\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/media\/3253"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/media?parent=4563"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/categories?post=4563"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/tags?post=4563"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}