Using Where Clause on Object Query Included Entities (Entity Framework)

Question

I am using Entity Framework as a foundation for a WPF database front end.

My Database Structure is for office buildings and for this issue all you need to understand about it is that the top level entity is called Markets (think Suburbs or Central Business Districts). Markets have many Properties and Properties have many Surveys.

I want to limit the Surveys returned to only the most recent 10 Surveys (surveys are conducted every 6 months).

I can see how the auto-generated code is building up the query:

Dim OMRMarketsQuery As System.Data.Objects.ObjectQuery(Of OMR.OMRInterfaceCustomCode.OMRMarket) = OMRInterfaceEntities.OMRMarkets

    OMRMarketsQuery = OMRMarketsQuery.Include("Properties")

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

I want to use a where clause to filter on a property of the OMRBuildingSurvey entity. I can write a where clause that will filter on the ID of the Market (the top level Entity) like this:

MRMarketsQuery = OMRMarketsQuery.Include("Properties.OMRBuildingSurveys").Where("it.ID >1000")

But I want to filter on a property of the OMRBuildingSurveys entity and I can't seem to find a way to navigate to it. I have tried:

OMRMarketsQuery = OMRMarketsQuery.Include("Properties.OMRBuildingSurveys").Where("it.Properties.OMRBuildingSurvey.ID >1000")

But I get the error:

An unhandled exception of type 'System.Data.EntitySqlException' occurred in System.Data.Entity.dll

    Additional information: 'OMRBuildingSurvey' is not a member of 'Transient.collection[OMRInterfaceModel.Property(Nullable=True,DefaultValue=)]'. To extract a property of a collection element, use a subquery to iterate over the collection.

If anyone could point me in the right direction, I'd really appreciate it!

Thanks very much and have a great day!

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