使用条件显式加载子导航属性 [英] Explicit Loading of child navigation properties with criteria

问题描述

在EF5中使用DBContext-在根据日期范围等条件进行过滤和部分加载之后。

我正在尝试生成一个完整的图形或对象树-人->事件，其中包括的唯一事件在日期范围内。 所有这些都保留了标准更改跟踪一个可以得到以下结果：

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

列表框和数据网格都绑定为适当的数据源。 POCO模板已经过修改，可以将INotifyProperty事件放置在导航属性类事件中。

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.

我已经为此工作了好几天，并过滤了延迟加载还是延迟加载显式加载不起作用。 在大量阅读博客/聊天程序之后，我意识到与Include有关的相当不真实的限制； 相反，我正在尝试显式加载。我正在使用DBContext一书。

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.

无法从数据库中仅带回事件数据的子集是100％的破坏者，因为很可能每人数十万个事件。 对于我或我的老板来说，Entity Framework不能使此功能变得相当明显没有意义-我们是否遗漏了某些东西？

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?

我留在了注释代码中，以尝试说明我尝试过的一些路径。类本身是此方法所属的存储库。我将进一步编辑该问题，以阐明我尝试过多少条路线。 View使用存储库层和ViewModel，因此XAML上的代码非常少。

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

注意：我使用的是通过PostSharp进行日志记录/异常处理，而不是污染代码。

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.

实体类型DbQuery`1不是当前上下文模型的一部分。

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

包含路径表达式必须引用在类型上定义的导航属性。使用虚线路径作为参考导航属性，使用Select运算符作为集合导航属性。

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.

参数名称：path

无法转换类型为'System.Data.Entity.Infrastructure.DbQuery 1 [VB $ AnonymousType_0 2
[Entity.Person，Entity.Notes]]'的对象键入 System.Collections.ObjectModel.ObservableCollection`1 [Entity.Person]。

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

另一个镜头：
公共可重写ReadOnly属性FilteredPersons（开始日期为日期，结束日期为日期）作为ObservableCollection（Of Person）实现IPersonRepository.AllFiltered
获取
context.Persons.Load（）
Dim DateCriteria = Function（e）e.events.Where（Function（d）d.eventDateTime> startdate And d.eventDateTime<结束日期）

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

礼物：

公共成员哪里

令人讨厌的是，只有我在列表框上得到了很多重复的名称-但导航仍然进行，并且使用了Date条件

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

不要尝试这个:)。

    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!

推荐答案

与 Include <相比，可以使用 Select 子句进行更好的控制。 / code>

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

类似这样的东西：

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

这会将两个谓词转换为在数据库服务器上执行的SQL。

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

这篇关于使用条件显式加载子导航属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！