实体框架如何在Navigation属性表中查询数据 [英] Entity Framework How to query data in a Navigation property table

问题描述

我有以下设置

m_handsets = From p In RL.App.TComEntities.tblTelephoneNumbers _
                                               Where p.companyId = m_CompanyID _
                                               Select p

m_handsets = DirectCast(m_handsets, ObjectQuery(Of RL.TelephoneNumbers)).Include("tblCalls")

其中m_handsets定义为

where m_handsets is defined as

Private m_handsets As IQueryable(Of RL.tblTelephoneNumbers)

它的工作原理，但我想做的知道是查询导航属性（tblCalls），所以我可以做一些像以下

which works as expected however what I want to do know is query the Navigation property (tblCalls) so I can do something like the following

From p In m_handsets.tblCalls 
Where m_handsets.tblCalls.price > 100

但我不知道正确的语法，任何人都可以帮忙？

but I have no idea of the proper syntax, can anyone help?

编辑：

我认为复杂性在这里，因为在这种情况下，我可以在m_handsets中有5个tblTelephoneNumbers然后x该电话号码的电话号码。我对每个tblCalls感兴趣，但我想为每个tblTelehoneNumber过滤它们。

I think the complexity comes here because in this instance I could have 5 tblTelephoneNumbers in m_handsets and then x amount of calls for that particular telephone number. I am interested in the tblCalls for each but I would like to filter them all for each tblTelehoneNumber.

实体图（希望）应该进一步说明

Entity Diagram which (hopefully) should illustrate further

所以我目前知道所有与之相关的手机与我感兴趣的公司，我也可以看到在调试模式下作为导航属性加载的电话，但我想说的是使用这个过滤器（在这个例子中，价格> 100，并将其应用于所有手机 - >调用

So I currently know all the handsets that are associated with the company I am interested in. I can also see the calls loaded as a navigation property in debug mode, but I want to say is take this filter (in this example price>100 and apply it to all handsets->calls

推荐答案

如果正确理解你的问题，那么你有两个解决方案可以实现这一点：$ b​​ $ b在这两个解决方案中，你会看到我删除包含方法，因为包含不允许您过滤相关数据



1。过滤投影（返回匿名类型）：

If understand your question correctly, then you have 2 solutions to accomplish this: In both solution you'll see that I remove the Include method since Include does NOT allow you to filter the related data.

1. Filtered Projection (Returns Anonaymous Type):

Dim results = From p In RL.App.TComEntities.tblTelephoneNumbers _
              Where p.companyId = m_CompanyID _
              Select New With {.Handsets = p, _
                               .tblCalls = p.tblCalls.Where(Function(t) t.price > 100)} 

但是，在所有情况下可能不需要它，因为它提供了匿名类型对象的集合。 >



2。两个跟踪查询（返回EntityObjects）：


这一个给你一个实体对象的集合tblTelephoneNumbers：

However, it might not be desirable in all situations as it gives a collection of anonymous type objects.


2. Two Tracked Queries (Returns EntityObjects):
This one gives you a collection of your entityobject tblTelephoneNumbers:

Dim m_handsets = (From p In RL.App.TComEntities.tblTelephoneNumbers _
                  Where p.companyId = m_CompanyID Select p).ToList()

Dim m_tblCalls = (From t In RL.App.TComEntities.tblCalls _
                  Where t.price > 100 Select t).ToList();

ForEach(Dim t In m_tblCalls) 
    m_handsets.Single(Function(h) h.ID = t.tblTelephoneNumberID).tblCalls.Add(t)
End ForEach

3。利用附加方法（返回EntityObjects）：


最后也可能是最好和最优雅的解决方案是使用 EntityCollection.Attach 方法以及 EntityCollection.CreateSourceQuery ：

foreach (var tel in m_handsets) {

    IQueryable<tblCalls> sourceQuery = tel.tblCalls.CreateSourceQuery()
                                                   .Where(c => c.price > 100);
    tel.tblCalls.Attach(sourceQuery);
}

对于任何VB语法错误，我写了他们都在我头顶。

Sorry for any VB syntax mistake, I wrote them all off the top of my head.

这篇关于实体框架如何在Navigation属性表中查询数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！