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

Answer 2

It is very difficult for us to 'debug' your LinQ without access to your code. However, when I'm writing complex LinQ queries, I always try to build each consecutive step at a time, so that if I do get an error, I know that it comes from the last bit that I added on. Also, pay close attention to the required input and return types of the LinQ methods that you are calling.

As an example of this, your error says that part of your query is expecting a Boolean type rather than a 'System.Collections.Generic.IEnumerable(Of OMR.OMRInterfaceCustomCode.OMRBuildingSurvey) type... the only required Boolean values that I can see in your query is from the Where method:

public static IEnumerable<TSource> Where<TSource>(
    this IEnumerable<TSource> source,
    Func<TSource, bool> predicate
)

Therefore, I can only assume that part of your query is returning a collection of OMRBuildingSurvey objects instead of the required Func<TSource, bool> predicate. If we look at this part p.OMRBuildingSurveys.Where(Function(s) s.PeriodID > 50), we can see that this will return an IEnumerable of the OMRBuildingSurvey instances that fulfil the predicate condition.

However, this resulting collection is being fed to the m.Properties.Where clause which is also expecting a Func<TSource, bool> predicate... so it seems like we have found the error.

UPDATE >>>

Ok... against my better judgement, I'll give this a go, blind as I am to the structure of your classes.

Starting on the right side, we have p.OMRBuildingSurveys.Where(Function(s) s.PeriodID > 50) we learnt returns an IEnumerable of the OMRBuildingSurvey instances that fulfil the predicate condition. So what's next for this collection?

We need to find the Property objects that contain any of these OMRBuildingSurvey instances:

m.Properties.Where(Function(p) p.OMRBuildingSurveys.Intersect(p.OMRBuildingSurveys.
Where(Function(s) s.PeriodID > 50)).Any())

I'm not even sure if this much will work... what we're trying to do here is return an IEnumerable of type Property containing the items that have any of the OMRBuildingSurvey instances that fulfil the PeriodID predicate condition in their OMRBuildingSurveys (CLR) property. The Intersect method joins the value of each OMRBuildingSurveys property with the output of the IEnumerable of the OMRBuildingSurvey instances that fulfil the predicate condition and Any returns any that are the same.

The final step would be something like this:

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

I've basically done the same thing... used the previous 'query so far' as the input parameter for the Intersect method called on the OMRBuildingSurvey.Properties property. Now, I hope that this works because I don't have any more time for this. Also, the VB may be 'off' because I write C#, but I am trusting that you can complete this with the information that I have provided if this example does not work... these queries aren't so bad when you break them down as I did.

By the way, this example follows the requirement in your question and not in your last comment.