Include count of linked records in OData result

Question

I've got a table "Events" with a linked table "Registrations", and I want to create an OData service that returns records from the Events table, plus the number of registrations for each event. The data will be consumed client-side in JavaScript, so I want to keep the size of the returned data down and not include all linked registration records completely.

For example:

ID  Title      Date            Regs
1   Breakfast  01.01.01 12:00  4
2   Party      01.01.01 20:00  20

I'm building the service with ASP.NET MVC4. The tables are in an MSSQL database. I am really just getting started with OData and LINQ.

I tried using the WebAPI OData system first (using classes of EntitySetController) but was getting cryptic server errors as soon as I included the Registrations table in the entity set. ("The complex type 'Models.Registration' refers to the entity type 'Models.Event' through the property 'Event'.")

I had more success building a WCF OData system, and can request event information and information on related registrations.

However, I have no clue how to include the aggregate count information in the event result set. Do I need to create a custom entity set that will be the source for the OData service? I probably included too litte information here for finding a solution, but I don't really know where to look. Can somebody help me?

Answer 1

If you're willing to make an extra request per Event, you could query http://.../YourService.svc/Events(<key>)/Registrations/$count (or http://.../YourService.svc/Events(<key>)/$links/Registrations?$inlinecount=allpages if you're also using the links to the Registration entities).

Examples of both of these approaches on a public service:

http://services.odata.org/V3/OData/OData.svc/Suppliers(0)/Products/$count

http://services.odata.org/V3/OData/OData.svc/Suppliers(0)/$links/Products?$inlinecount=allpages&$format=json

I'm guessing that you'd prefer this information to come bundled together with the rest of the Events response though. It's not ideal, but you could issue a query along these lines:

http://services.odata.org/V3/OData/OData.svc/Suppliers?$format=json&$expand=Products&$select=Products/ID,*

I'm expanding Products (analogous to your Registrations) and selecting Products/ID in order to force the response to include an array that is the same size as the nested Products collection. I don't care about ID -- I just chose a piece of data that would be small. With this JSON response, your javascript client can get the length of the Products array and use that as the number of Products that are linked to the given Supplier.

(Note: to have your service support $select queries using WCF Data Services, you'll need to include this line when you initialize the service: config.DataServiceBehavior.AcceptProjectionRequests = true;)

Edit to add: The approach using $expand and $select won't be guaranteed to give you the correct count if your server does server-driving paging. In general, there isn't a simple single-response way to do what you're asking for in OData v3, but in OData v4, this will be possible with the new expand/select syntax.

Answer 2

i'm using oData v4 and i used this syntax :

var url = '.../odata/clients?$expand=Orders($count=true)';
// ...

a field called [email protected] has been added to the response entity which contains the correct count. and now to access the JSON property containing a dash you have to do it like this :

var ordersCount = response.value['[email protected]'];

hope this helps.

Answer 3

Can you edit your Event model and add a RegistrationCount property? That'd be the simplest way I think

Answer 4

I used this to get the child count without returning the entities:

/Parents$expand=Children($count=true;$top=0)