Explicit Loading of child navigation properties with criteria

Question

Using DBContext in EF5 - after filtering and partial loading based on criteria like a date range.

I'm trying to produce a complete graph or tree of objects - Persons->Events where the only Events that are included are within a date range. All this whilst preserving the standard change tracking that one gets with the following:

 Dim Repository As Models.personRepository = New Models.personRepository

 Private Sub LoadData()
    Dim personViewModelViewSource As System.Windows.Data.CollectionViewSource = CType(Me.FindResource("personViewModelViewSource"), System.Windows.Data.CollectionViewSource)
    Repository.endDate = EndDate.SelectedDate
    Repository.startDate = StartDate.SelectedDate
    personViewModelViewSource.Source = Repository.collectionOfpersons
 End Sub

A listbox and a datagrid are both bound as a proper datasource. The POCO template has been modified to put INotifyProperty events in the navigation property class, Events.

I've been fighting with this for days now and filtering whether on Lazy loading or Explicit loading does not function. After masses of blog/chaper reading, I'm aware of the rather unreal limitation relating to Include; instead I'm trying explicit loading. I'm using the DBContext book btw.

Being unable to bring back only a subset of the Event data from the database is 100% deal breaker as there is likely to be hundreds of thousands of Events per Person. It doesn't make sense to me or my boss that the Entity Framework doesn't make this functionality fairly obvious-are we missing something?

I've left in the commented code to try and illustrate some of the paths I've attempted. The class itself is a repository that this method belongs to. I'll edit this question further to clarify just how many routes I've tried as it's been a LOT. The View uses a repository layer and a ViewModel so the code behind on the XAML is rather minimal.

In advance for any help, thank you!

 Public Overridable ReadOnly Property AllFiltered(startdate As Date, enddate As Date) As ObservableCollection(Of person) Implements IpersonRepository.AllFiltered
        Get
            Dim uow = New UnitOfWork
            context = uow.Context
            Dim personQuery = context.persons.Include(Function(p) p.events).AsQueryable.Where(Function(x) x.personID = 10).FirstOrDefault


            'Dim eventQuery = From e In context.Notes
            '                 Where e.eventDateTime >= startdate And e.eventDateTime <= enddate
            '                 Select e

            'Dim personQuery As person = From r In context.persons
            '                   From e In eventQuery
            '                   Where r.personID = e.personID
            '                   Select r, e

            Dim singleperson = personQuery

            'For Each r As person In personQuery
            '    persons.Add(r)
            'Next

            '   context.Entry(eventQuery).Collection()
            ' context.Entry(personQuery).Reference(personQuery).Load()

            context.Entry(singleperson).Collection(Function(d) d.events).Query().Where(Function(x) x.eventDateTime > startdate And x.eventDateTime < enddate).Load()

            Return context.persons.Local
        End Get
    End Property

Note: I'm using logging/exception handling via PostSharp rather than polluting the code.

Below are some of the errors I've generated with previous paths taken.

The entity type DbQuery`1 is not part of the model for the current context.

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties.

Parameter name: path

Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery1[VB$AnonymousType_02 [Entity.Person,Entity.Notes]]' to type 'System.Collections.ObjectModel.ObservableCollection`1[Entity.Person]'.

UPDATE: Yet another route I've tried, still cannot get this to fly either:

Private Property _collectionOfPersons As ObservableCollection(Of Person)

Public ReadOnly Property collectionOfPersons As ObservableCollection(Of Person)
        Get
            For Each Person In context.Persons
                _collectionOfPersons.Add(ReturnSinglePerson(startDate, endDate, Person.PersonID))
            Next
            Return _collectionOfPersons.Where(Function(x) x.events.Where(Function(e)         e.eventDateTime > startDate And e.eventDateTime < endDate))
        End Get
    End Property

Public Overridable ReadOnly Property SinglePerson(startdate As Date, enddate As Date) As ObservableCollection(Of Person) Implements IPersonRepository.AllFiltered
        Get

            Dim PersonQuery = context.Persons.Include(Function(p) p.events).AsQueryable.Where(Function(x) x.PersonID = 10).Select(Function(x) x).FirstOrDefault

            Dim Person = PersonQuery

            context.Entry(Person).Collection(Function(d) d.events).Query().Where(Function(x) x.eventDateTime > startdate And x.eventDateTime < enddate).Load()

            Return context.Persons.Local
        End Get
End Property

Public Function ReturnSinglePerson(startdate As Date, enddate As Date, id As Integer)

        Dim PersonQuery = context.Persons.Include(Function(p) p.events).AsQueryable.Where(Function(x) x.PersonID = id).Select(Function(x) x).FirstOrDefault
        Dim Person = PersonQuery
        Return Person

End Function

Another shot: Public Overridable ReadOnly Property FilteredPersons(startdate As Date, enddate As Date) As ObservableCollection(Of Person) Implements IPersonRepository.AllFiltered Get context.Persons.Load() Dim DateCriteria = Function(e) e.events.Where(Function(d) d.eventDateTime > startdate And d.eventDateTime < enddate)

            Dim Res = New ObservableCollection(Of Person)
            For Each Person In context.Persons.Local.Select(Function(x) x).Where(DateCriteria)
                Res.Add(Person)
            Next

            Return Res
        End Get
    End Property

Gives:

Public member 'Where' on type 'ObservableCollection(Of DailyNotes)' not found.

Tantalisingly close, only I get lots of duplicate names on the listbox - but the navigation carries through and the Date criteria work.

   <ExceptionAspect>
    Public Overridable ReadOnly Property FilteredPersons(startdate As Date, enddate As Date) As ObservableCollection(Of Person) Implements IPersonRepository.AllFiltered
        Get
            context.Persons.Load()


            Dim test = From r In context.Persons
                    From e In context.Notes
                    Where e.eventDateTime > startdate And e.eventDateTime < enddate
                    Join rr In context.Persons On e.PersonID Equals rr.PersonID
                    Select r, e


            Dim Res = New ObservableCollection(Of Person)
            For Each Person In test
                Res.Add(Person.r)
            Next

            Return Res
        End Get
    End Property

Don't try this one :). It simply selects the child properties only.

    Public ReadOnly Property collectionOfResidents As ObservableCollection(Of resident)
        Get
            For Each resident In context.residents
                _collectionOfResidents.Add(ReturnSingleResident(startDate, endDate, resident.residentID))
            Next
            Return _collectionOfResidents.Select(Function(x) x.events.Where(Function(e) e.eventDateTime > startDate And e.eventDateTime < endDate))
        End Get
    End Property

I'm hoping that adding my other attempts to this question may prompt both other answers and help others see the circles they can get into when first tackling this!

score 1 · Accepted Answer · answered Dec 17 '12 at 14:51

1

You can use the Select clause for finer control than with Include

Something like this:

context
  .Persons
  .Where( ... some predicate on Person ... )
  .Select( o => new
    {
      Person = o,
      Events = o.Events.Where( ... some predicate on Event ... )
    }
  )
;

This will translate both predicates into SQL which execute on the database server.

answered Dec 17 '12 at 14:51

Nick Butler

24,045
4
49
70

I tried to translate this to VB with a large fail LOL, the syntax doesn't want to happen. I don't think I can do this method in VB :( Thank you for the suggestion, am still trying it now. – Richard Griffiths Dec 17 '12 at 15:40
I've got a bit further now with this: – Richard Griffiths Dec 17 '12 at 15:57
You can declare a class for the result of the select if anonymous types are difficult. But then you have to use the default constructor and object initializers. – Nick Butler Dec 17 '12 at 16:12
I've gotten this far, but it ain't right sadly; its getting closer. `code` Dim test = From r In context.residents From e In context.DailyOccurrences.Select(Function(x) x).Where(Function(s) s.eventDateTime > startdate And s.eventDateTime < enddate) Join rr In context.residents On e.residentID Equals rr.residentID Select r, e – Richard Griffiths Dec 17 '12 at 16:43
I think we're leaning towards not using EF5 because tbh I've been on this continuously for about three weeks now and whilst I've succeeded in having a straight forward interface, with a repository fully working from this-I'm completely unable to get any reasonable means of criteria selection with this technology. The only way I can is to manually do everything and forget most of the binding and EF goodness. I was quite excited when I found I could do the original project easily-drag and drop. But this part is so hard that I don't see that we can use it. :( – Richard Griffiths Dec 17 '12 at 16:47
My point being, if a couple of developers spending weeks on it, who can successfully use EF5 DBcontext on a simple level; alter the TT files to include PropertyChanged in the generated code and have made it work on SQL Azure within hours-can't get a criteria'd return query for a navigation property, I think there's something quite unusable about this specific part of EF5. Sadly-We've given it a really honest try, days of it. Hence why I'm resorting to posting trying to find answers. – Richard Griffiths Dec 17 '12 at 16:52
@ Nicholas Butler, Thank you for your help, I appreciate it. I'm going to try again tomorrow; though I have to be honest, judging by how every time I find a new place to put a LINQ filter in - it doesn't work, I don't hold out much hope LOL. – Richard Griffiths Dec 17 '12 at 17:48
@Richard - if this is the only thing holding you back, then I would recommend sticking with it. The solution in my answer does work and this is one of the few remaining pain points in EF. – Nick Butler Dec 17 '12 at 18:10
@Richard - in your comment above you have 2 `from` clauses - this is not right! It creates a Cartesian product. I suggest getting LINQPad - it's free | cheap and really helps as it shows you the SQL generated for an EF LINQ query. – Nick Butler Dec 17 '12 at 18:13
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/21286/discussion-between-richard-and-nicholas-butler) – Richard Griffiths Dec 17 '12 at 20:44
Thank you for Linqpad tip. I've a registered copy already and 3 minutes is all it took for me to make it talk to EF. So, thank you, I suspect it will accelerate things..as I can use several languages here now. – Richard Griffiths Dec 17 '12 at 21:28

score 0 · Answer 2 · answered Dec 17 '12 at 23:58

Ok after a LOT of fiddling and misunderstanding of anonymous types this evening, I think I succeeded. Nicholas's answer just needed to be done in VB which took me a while - I've not used anonymous types before.

This is what appears to work fine in my repository layer:

               <ExceptionAspect>
    Public Overridable ReadOnly Property FilteredPersons(startdate As Date, enddate As Date) As ObservableCollection(Of Person) Implements IPersonRepository.AllFiltered
        Get
            context.Persons.Load()

            Dim test = context.Persons.Where(Function(r) r.PersonActive).Select(Function(o) New With { _
                 .Person = o, _
                 .events = o.events.Where(Function(e) e.eventDateTime > startdate) _
            })


            Dim PersonList= New ObservableCollection(Of Person)

            For Each t In test
               PersonList.Add(t.person)
            Next

            Return PersonList
        End Get
    End Property

The crucial updating/saving in the wpf View is intact, I'm really happy and grateful to Nicholas for his help on here (and patience...re:cartesion product). So thank you. I hope this helps someone else!

That's looking better - sorry I can't speak VB! You shouldn't need `context.Persons.Load()`, though, and you'll have to attach the data in the anon type `.Events` property to your `Person` objects when you create `PersonList`. Please do contact me off-list if that doesn't make sense. — Nick Butler, Dec 18 '12 at 10:27
@NicholasButler - thank you, I've gotten to work and find that the view part does not refresh at all BUT that's a whole different question as the generated SQL is completely correct. — Richard Griffiths, Dec 18 '12 at 12:53

Explicit Loading of child navigation properties with criteria

2 Answers2

Linked