locked
Ado.Net Data Services query sub collections with DataServiceQuery object RRS feed

  • Question

  • I'm using Ado.Net Data Services 1.0
    Is it possible to query sub collections with the DataServiceQuery object (client side!)? This means, if I want to select all the people, which have in their favorite colors the color red, is it possible to write a linq expression to select them?
    DataServiceQuery<Person> query;
    //Any doesn't work (is not supported)
    query.Where(p=>p.FavoriteColors.Any(fc=>fc.Color.Name=="Red"));
    //First doesn't work (is not supported)
    query.Where(p=>p.FavoriteColors.FirstOrDefault(fc=>fc.Color.Name=="Red")!=null);
    //Single doesn't work (is not supported)
    query.Where(p=>p.FavoriteColors.SingleOrDefault(fc=>fc.Color.Name=="Red")!=null);
    //Count doesn't work (is not supported)
    query.Where(p=>p.FavoriteColors.Count(fc=>fc.Color.Name=="Red")!=0);
    //Contains doesn't work (is not supported)
    query.Where(p=>p.FavoriteColors.Select(fc=>fc.Color).Contains(redColor));
    Tuesday, January 12, 2010 12:54 PM

Answers

  • Hi,

    Usually data service doesn't expose the mapping table, it rather hides it underneath a many-many relationship. In which case the query is easier to write and will work. Your sample above might not work properly in all cases.

    Anyway as you noted, the best solution for complex queries which are not directly supported is to write your own service operation. You're also right that the client code gen doesn't have a support for that yet.

    All in all, I'm just wondering what was your original question about if you know the solution :-)

    Thanks,
    Vitek Karas [MSFT]
    Wednesday, January 13, 2010 9:51 AM
    Moderator

All replies

  • I'm sorry but query projections are suported since v1.5ctp1

    regards.
    Tuesday, January 12, 2010 3:10 PM
  • Hi,

    You're right we don't support the queries above. Even though we do support projections in the latest CTP, we still don't support Contains anyway.
    On the other hand usually you can write such query in a different way which we do support.
    In this case I will assume that you have two entity sets: Persons and FavoriteColors.
    I also assume you have a navigation property on Person called FavoriteColors (resource set reference property - 1 to many mapping).
    And then you probably also have navigation property on FavoriteColor to Persons (basically the back link for the first navigation).
    I also assume that you know the key for the FavoriteColor with name "Red". So for example let's say that "Red" has a key value 1

    In that case you can write your above query like this:
    ctx.FavoriteColors.Where(fc => fc.ID == 1).Select(fc => fc.Persons);
    The URL query should then look like:
    /FavoriteColors(1)/Persons

    Thanks,
    Vitek Karas [MSFT]
    Tuesday, January 12, 2010 3:37 PM
    Moderator
  • Thanks Vitek for your solution, also if it has a little bug, FavoriteColors is a mapping table between Person and Color, so the linq expression should be:
    ctx.FavoriteColors.Where(fc=>fc.Color.ColorId==1).Select(fc=>fc.Person)
    This is a work around in case that you have to query only one mapping collection, but if you have to query more than one mapping collection there isn't a solution, at least with linq on the client side.

    There is a server side solution which consist in a custom web method:
    [WebGet]
    public IQueryable<Person> FilterPerson(int? colorId)
    {
      ObjectQuery<Person> query = this.CurrentDataSource.PersonSet;
      if (colorId.HasValue) query = (ObjectQuery<Person>)query.Where(p=>p.PreferedColors.FirstOrDefault(pc=>pc.Color.ColorId==colorId.Value)!=null);
      return query;
    }
    The complex part is to invoke the web method from the client side because they aren't supported by data services 1.0, so you have to generate the whole query url and use the BeginExecute/EndExecute methods:
    ServiceReference context=new ServiceReference(...);
    context.BeginExecute<Person>(new Uri("/FilterPerson?$expand=FavoriteColors/Color&colorId=1"),s=>context.EndExecute(s).ToList());
    Regards
    Wednesday, January 13, 2010 9:45 AM
  • Hi,

    Usually data service doesn't expose the mapping table, it rather hides it underneath a many-many relationship. In which case the query is easier to write and will work. Your sample above might not work properly in all cases.

    Anyway as you noted, the best solution for complex queries which are not directly supported is to write your own service operation. You're also right that the client code gen doesn't have a support for that yet.

    All in all, I'm just wondering what was your original question about if you know the solution :-)

    Thanks,
    Vitek Karas [MSFT]
    Wednesday, January 13, 2010 9:51 AM
    Moderator
  • I'm sorry Vitek, you're right, I didn't read all the queries :(

    Thanks :)
    Wednesday, January 13, 2010 11:57 AM
  • Hi Vitek

    I didn't know the solution until a few hours ago and also the resource on this argument are scarce.
    Regarding the mapping table, alas is an old db model and the mapping tables contain also other fields like timestamps, for this reason the EF doesn't hide them.

    Thanks for the help anyway
    Wednesday, January 13, 2010 12:46 PM
  • Hi,

    Please be a bit more specific when asking questions. Which example are you referring to (there are 3 pieces of code there), and which part you don't understand.

    As for a guide - this thread was trying to solve a problem querying over navigation property. Documentation for that is part of the OData protocol (www.odata.org). Most of the time these questions come up because WCF Data Services don't support all the complex query operators EF does, so in these threads we're trying to find workarounds for specific scenarios. So unless you have a more specific scenario in mind, it's hard to give guidance.

    Also to comment on the above a little bit. If the service operation returns set of entities as IQueryable (as the one abvoe does), the client actually does "sort of" support it. It's not necessary to construct the entire URL by hand. You can use:

    context.CreateQuery("FilterPerson").AddQueryOption("colorId","1").Select(....

    A service operation without any parameters looks just like an entity set to the client. So you can use CreateQuery to get a LINQ over that entity set. If it takes parameters, you can use the AddQueryOption method to add those and still continue using LINQ to construct the rest of the URL. This also allows comre complex projections (projections to anonymous types and such) on the client.

    Thanks,


    Vitek Karas [MSFT]
    Tuesday, August 17, 2010 8:36 AM
    Moderator
  • Hi Vitek

    wilfred is just a spam bot who use phrases vague enough to fit in every question in order to disguise hidden link seo spams in a wide range of forums without changing the phrase too often



    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    Monday, January 31, 2011 5:03 AM