locked
Filtering based on Expanded table data RRS feed

  • Question

  • Hello,

    Calendars - CalendarCode(PK), Name with Navigation Property CalendarItems (List of CalendarItem)

    CalendarItem - CalendarCode(FK), locationCode, StartTime.

    Now I would like to fetch the Calendar based on CalendarCode and locationCode......

      var calendars = from p in _container.Calendars .Expand("CalendarItems ")
                                where p.CalendarCode == calendarCode select p;
      List<Calendars > cals= calendars.ToList<Calendars>();

    I can fetch easily by CalendarCode, but how to filter it based on location Code ??

     

     

     

     

     

    Tuesday, June 15, 2010 1:10 PM

Answers

  • Hi,

    In that design each calendar can have (in theory) many location codes. So it depends what you mean by "filter on it".

    If the locationCode is a PK on CalendarItem and if each CalendarItem belongs to exactly one Calendar you can use navigation:

    /CalendarItems('locationCode')/Calendar

    In code this is something like _container.CalendarItems.Where(c => c.locationCode == 'locationCode').Select(c => c.Calendar);

    If locationCode is not a PK or the relationship is many-many you can do this:

    /CalendarItems?$filter=locationCode eq 'locationCode'&$expand=Calendar

    In code this is something like _container.CalendarItems.Expand("Calendar").Where(c => c.locationCode == 'locationCode');

    The shape of the results will be a bit different (you will get all the matching items as well as calendars), but you could limit the amount of data through projections ($select).

    Thanks,


    Vitek Karas [MSFT]
    Tuesday, June 15, 2010 3:48 PM
    Moderator
  • You can only filter on primitive properties of the Entity from the set that you are querying; you are not able to filter on properties from Entities that are associated to the Entity that you are querying.  Quite often you can approach the problem from a slightly different angle to accomplish what you want.  In this case try querying for CalendarItems filtering on locationCode and then expanding Calendars.

    Thanks,
    -jeff

     

    Tuesday, June 15, 2010 3:53 PM

All replies

  • Hi,

    In that design each calendar can have (in theory) many location codes. So it depends what you mean by "filter on it".

    If the locationCode is a PK on CalendarItem and if each CalendarItem belongs to exactly one Calendar you can use navigation:

    /CalendarItems('locationCode')/Calendar

    In code this is something like _container.CalendarItems.Where(c => c.locationCode == 'locationCode').Select(c => c.Calendar);

    If locationCode is not a PK or the relationship is many-many you can do this:

    /CalendarItems?$filter=locationCode eq 'locationCode'&$expand=Calendar

    In code this is something like _container.CalendarItems.Expand("Calendar").Where(c => c.locationCode == 'locationCode');

    The shape of the results will be a bit different (you will get all the matching items as well as calendars), but you could limit the amount of data through projections ($select).

    Thanks,


    Vitek Karas [MSFT]
    Tuesday, June 15, 2010 3:48 PM
    Moderator
  • You can only filter on primitive properties of the Entity from the set that you are querying; you are not able to filter on properties from Entities that are associated to the Entity that you are querying.  Quite often you can approach the problem from a slightly different angle to accomplish what you want.  In this case try querying for CalendarItems filtering on locationCode and then expanding Calendars.

    Thanks,
    -jeff

     

    Tuesday, June 15, 2010 3:53 PM