Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I filter with OData based on an inner array?

So I created a controller using the mvc4 web api where the url (Get) “/api/things” return the following data:

 <ArrayOfThing>
   <Thing>
   <Id>1</Id>
   <Description>The Thing Desc</Description>
   <Categories>
           <Category><Id>1</Id></Category>
          <Category><Id>2</Id></Category>
   </Categories>
  </Thing>
  <Thing>
   <Id>2</Id>
   <Description>The Other Thing Desc</Description>
   <Categories>
           <Category><Id>1</Id></Category>
           <Category><Id>3</Id></Category>
   </Categories>
  </Thing>
</ArrayOfThing>

*note that thing and categories has a many to many relationship

I know that if a need one “Thing” resource I should use a controller that matches the following route url (Get) “/api/things/{id}”.

But what if I want to get a subset of the data returned by the url (Get) “/api/things”. I tested the OData protocol modifying the controller to return an IQueryable and it work fine if I wanted to $filter on the properties of “Thing” like the Id or the Description. Unfortunately, I didn’t work out when I wanted to filter base on the Category, I believe is because Categories is an inner array.

So, what should I do to filter based on categories?

like image 755
user1261620 Avatar asked Sep 05 '12 14:09

user1261620


People also ask

How do I filter OData query?

You can use filter expressions in OData requests to filter and return only those results that match the expressions specified. You do this by adding the $filter system query option to the end of the OData request.

What is $select in OData?

The $select option specifies a subset of properties to include in the response body. For example, to get only the name and price of each product, use the following query: Console Copy. GET http://localhost/odata/Products?$select=Price,Name.


1 Answers

OData V3 supports Any/All operations, which you can filter by a collection property. For example, to filter things which contains Category(1), using following syntax:

/api/things/?$filter=Categories/any(category: category/Id eq 1)

If you are using ASP.net Web Api RTM bits, you can use odata package to support any/all. For more examples, you can check the queryable sample code: http://aspnet.codeplex.com/SourceControl/changeset/view/61dfed023e50#Samples%2fNet4%2fCS%2fWebApi%2fODataQueryableSample%2fProgram.cs

like image 56
Hongye Sun Avatar answered Oct 01 '22 15:10

Hongye Sun