How to use "SelectMany" with DataServiceQuery<>

Question

I have the following DataServiceQuery running agaist an ADO Data Service (with the update installed to make it run like .net 4):

 DataServiceQuery<Account> q = (_gsc.Users
            .Where(c => c.UserId == myId)
            .SelectMany(c => c.ConsumerXref)
            .Select(x => x.Account)
            .Where(a => a.AccountName == "My Account" && a.IsActive)
            .Select(a => a)) as DataServiceQuery<Account>;

When I run it, I get an exception: Cannot specify query options (orderby, where, take, skip) on single resource

As far as I can tell, I need to use a version of "SelectMany" that includes an additonal lambda expression (http://msdn.microsoft.com/en-us/library/bb549040.aspx), but I am not able to get this to work correctly.

Could someone show me how to properly structure the "SelectMany" call?

Thank you for any help.

Answer 1

Data Services doesn't support composing SelectMany with a subsequent Select unless you include a key selector to filter the 'Many' back to just one item.

If you think about queries in terms of URIs you will understand why.

In OData URIs you have to have just one Entity before you navigate (i.e. /NavigationProperty).

So this:

~/Users(123)/ConsumerXRef

is okay because you have one User (123) before you retrieve the many related ConsumerXRef(s).

However this is no good:

~/Users(123)/ConsumerXRef/Account

because you don't identify a single ConsumerXRef before navigating to accounts.

When you take this thinking into LINQ land, something like this:

from u in ctx.Users
where u.ID == 123
from c in u.ConsumerXRef
select c;

is okay because it roughly translates to:

~/Users(123)/ConsumerXRef

But this:

from u in _gsc.Users
where u.UserId == myId
from c in u.ConsumerXref
where c.AccountName == "MyAccount" && c.IsActive
select x.Account;

is no good because - I'm guessing here - AccountName isn't the key? so this translates to something like this URL

~/Users(123)/ConsumerXRef/Account/?$filter=AccountName eq 'MyAccount' ...

which is invalid because you've navigated (from ConsumerXRefs to their Accounts) without first selecting a specific ConsumerXRef.

Does this make sense?

Hope so

Alex