CODEWITHSUNDEEP
linqodatalinqpad
Larry Smithmier
Larry Smithmier

Reputation: 2731

Get a count of a linked collection using OData and LINQ

I set up the OData feed for Stack Overflow as outlined in the wonderful article Using LINQPad to Query Stack Overflow and I want to do something like:

Users.Where(x=>x.Badges.Count==0).Take(5)

to get the users that have no Badges ("Badges? We don't need no stinkin' badges!"). I get a DataServiceQueryException: error snapshot

Upvotes: 3

Views: 3758

Answers (3)

Vitek Karas MSFT
Vitek Karas MSFT

Reputation: 13320

Filtering on count of entities in navigation properties is currently not supported (as already noted by Joe Albahari above). In the latest CTP OData supports any and all functions which would allow you to filter on "empty" navigation properties.

See 

http://blogs.msdn.com/b/astoriateam/archive/2011/10/13/announcing-wcf-data-services-oct-2011-ctp-for-net-4-and-silverlight-4.aspx to get the latest CTP.

Here is a discussion of the any/all feature: 

http://www.odata.org/blog/even-more-any-and-all

Upvotes: 1

Joe Albahari
Joe Albahari

Reputation: 30964

Unfortunately, OData doesn't support aggregate functions - it supports only the limited set of querying functions described here.

Aggregate operators

All aggregate operations are unsupported against a DataServiceQuery, including the following:

Aggregate

Average

Count

LongCount

Max

Min

Sum

Aggregate operations must either be performed on the client or be encapsulated by a service operation.

Hopefully Microsoft will enhance the OData client in the future - it is frustrating to (seemingly) have all the power of LINQ and then not be able to use it.

Upvotes: 8

Turker
Turker

Reputation: 619

Looks like Badges doesn't have a Count property. This is why the exception occurred. 

<EntityType Name="Badge">
    <Key>
    <PropertyRef Name="Id" />
    </Key>
    <Property xmlns:p8="http://schemas.microsoft.com/ado/2009/02/edm/annotation" Name="Id" Type="Edm.Int32" Nullable="false" p8:StoreGeneratedPattern="Identity" />
    <Property Name="UserId" Type="Edm.Int32" Nullable="true" />
    <Property Name="Name" Type="Edm.String" Nullable="true" MaxLength="50" Unicode="true" FixedLength="false" />
    <Property Name="Date" Type="Edm.DateTime" Nullable="true" />
    <NavigationProperty Name="User" Relationship="MetaModel.BadgeUser" FromRole="Badge" ToRole="User" />
</EntityType>

Probably you'd need to process each User to check whether the Badges navigation property resolves to anything.

Upvotes: 1

Related Questions