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!
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.