Using Linq Methods to Navigate Multiple Entity Framework Entities with Where Clause

Question

I am trying to query across multiple entity relationships using an Object Query.

The entity chain is basically OMRMARKET (one to many) PROPERTIES (one to many) OMRBUILDINGSURVEYS (one to many) PERIODS. Or in other words an Market has many Properties, Properties have many Surveys, Surveys have many Periods.

I want to filter the following Object Query:

OMRMarketsQuery = OMRMarketsQuery.Include("Properties.OMRBuildingSurveys")

On the Period ID (Pseudocode) OMRMarket.Properties.OMRBuildingSurveys.PeriodID > 50

Then I thought I might be able to nest subsequent Where functions such as:

OMRMarketsQuery = OMRMarketsQuery.Include("Properties.OMRBuildingSurveys").Where(
Function(m) m.Properties.Where(Function(p) p.OMRBuildingSurveys.Where(Function(s) 
s.PeriodID > 50)))

And I get intellisense support that helps me build up that query but then I get the error

Value of type 'System.Collections.Generic.IEnumerable(Of OMR.OMRInterfaceCustomCode.OMRBuildingSurvey)' cannot be converted to 'Boolean'

Any help would be hugely appreciated. I know this must be doable. Thanks very much in advance.

Answer 1

Okay the answer is simple.

You can't filter when using Eager Loading or Lazy Loading for that matter. Myself and a couple of freelancers tried all kinds of methods but the answer was to load the markets and the properties and then comment out the following line of code:

'OMRMarketsQuery = OMRMarketsQuery.Include("Properties.OMRBuildingSurveys")

To load the survey detail we caught the Property Selector listbox changed event and that is where we could filter as follows:

Private Sub Lbx_PropsByNameSelector_SelectionChanged(sender As Object, e As SelectionChangedEventArgs) Handles Lbx_PropsByNameSelector.SelectionChanged

    Dim propertyAdListBox = CType(sender, ListBox)
    Dim selectedProperty = CType(propertyAdListBox.SelectedItem, OMRInterfaceCustomCode.Property)

    If Not IsDBNull(selectedProperty) Then

        Dim RSurveysQuery = From r In OMRInterfaceEntities.OMRBuildingSurveys Where r.PeriodID > 80 And r.PropertyID = selectedProperty.ID

        Dim RSurveysList = RSurveysQuery.ToList

        If RSurveysList.Any() Then
            Dim RecentSurveysSource = CType(Me.FindResource("OMRMarketsPropertiesOMRBuildingSurveysViewSource"), CollectionViewSource)
            RecentSurveysSource.Source = RSurveysList
        End If
    End If

End Sub