Explicit loading of child navigation properties with criteria

Question

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

I am trying to create a complete graph or object tree - Persons->Events where the only Events that are included are within a date range. All this, while maintaining the standard change tracking, which is obtained 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 

The list and datagrid are bound as the correct data source. The POCO template has been modified to include INotifyProperty events in the Events navigation property class.

I struggled with this for several days, and filtering for what on Lazy load or Explicit load does not work. After mass reading a blog / reading, I know of a rather unrealistic restriction regarding Include; instead, I am trying to do an explicit download. I am using the DBContext btw book.

It is impossible to return only a subset of the Event data from the database, it is a 100% transaction breaker, since there are likely to be hundreds of thousands of events per person. It doesn't make sense to me or my boss that the Entity Framework does not make this function obvious enough - are we missing something?

I left a code in the comments to try to illustrate some of the paths I tried. The class itself is the repository to which this method belongs. I will edit this question further to clarify how many routes I tried, since it was a LOT. The view uses the repository level and ViewModel, so the code located on XAML is minimal.

Thanks in advance for any help!

  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 use logging / exception handling through PostSharp instead of polluting the code.

Below are some of the errors that I created with the previous paths.

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

The Include path expression must specify the navigation property defined in the type. Use dashed paths for referenced navigation properties and the Select statement for navigation properties for the collection.

Parameter Name: Path

Unable to create object of type 'System.Data.Entity.Infrastructure.DbQuery 1[VB$AnonymousType_0 2 [Entity.Person, Entity.Notes]]' for input 'System.Collections.ObjectModel.ObservableCollection`1 [Entity.Person]'.

UPDATE: Another route I tried still cannot make it fly:

 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 snapshot: Public overridable property ReadOnly FilteredPersons (startdate As Date, enddate As Date) How 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 element 'Where' on type 'ObservableCollection (Of DailyNotes)' not found.

It closes carefully, only I get a lot of duplicate names in the list - but the navigation goes through and the Date criteria works.

  <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 

Do not try to do this :). It just selects only child properties.

  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 hope that adding my other attempts to this question can lead to other answers and help others see the circles that they can fall into when they first decide!

Answer 1

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

Something like that:

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

This converts both predicates to SQL, which are executed on the database server.

Answer 2

Well, after I unlearned a lot and misunderstood anonymous types tonight, I think I succeeded. Nicholas replied that you just need to do it in VB, which took me some time - I have not used anonymous types before.

This is what works 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 

Important update / saving in wpf View intact, I am very happy and grateful to Nikolai for his help here (and patience ... re: cartesion product). So thank you. Hope this helps someone else!