CODEWITHSUNDEEP
vb.netentity-frameworklinq-to-entities
Dean
Dean

Reputation: 5946

Entity Framework How to query data in a Navigation property table

I have a the following setup 

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")

where m_handsets is defined as 

Private m_handsets As IQueryable(Of RL.tblTelephoneNumbers)

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?

EDIT:

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 diagram

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

Upvotes: 1

Views: 5001

Answers (1)

Morteza Manavi
Morteza Manavi

Reputation: 33206

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)}
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. Leveraging Attach Method (Returns EntityObjects):
The last and probably the best and most elegant solution is to use EntityCollection.Attach Method along with EntityCollection.CreateSourceQuery:

foreach (var tel in m_handsets) {

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


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

Upvotes: 1

Related Questions