• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1436
  • Last Modified:

Linq - use criteria on child records


I've just started using Linq and managed to get the records I needed using the code below. I have several criteria that are applied if Ids are selected by the user. This appraoch works well and is easy to maintain.

However...I now need to add further criteria based on records in a related table. I can't seem to find the syntax on how to do this after several hours searching.
The tables are related in the dbml. The records in the child table have a couple of Ids (on further lookup tables) that I need to also filter by.

E.g. Record in MyTable has two child records, I want to check if either of these records have an Id from a list of integers.

Thanks for any help..

Dim myRecs As IQueryable = m_db.MyTable
        If myCriteria1Ids.Count > 0 Then
            myRecs = myRecs.Where(Function(n) m_myCriteria1Ids.Contains(CInt(n.CriteriaId1)))
        End If
        If myCriteria2Ids.Count > 0 Then
            myRecs = myRecs.Where(Function(n) m_myCriteria2Ids.Contains(CInt(n.CriteriaId2)))
        End If
       'several more criteria added using same process
        Dim output = (From p In myRecs _
                      Order By p.MyId Ascending _
                      Select p)

Open in new window

  • 3
1 Solution
Sreedhar VengalaCommented:
Something as follows:
List<int> integers = new List<int>{1,2,3};
var result = from mt in Mytable where integers.Contains(mt.Id) select mt;

Open in new window

freddieheinekenAuthor Commented:
Thanks for you comment. I'm already using a similar approach though for all fields in the parent table, and this works fine. The problem I'm having is with the associated records. I need something like:

myRecs = myRecs.Where(Function(n) m_myCriteria3Ids.Contains(CInt(n.ChildTable.CriteriaId3)))

Applied on top of all my other selection criteria. The above of course does not work!
I can see the related records property, but not sure how it should be used.
freddieheinekenAuthor Commented:
OK I came up with a solution myself after a day of experimenting..

I needed the DataLoadOptions class to load the child records at the same time as the parents (LoadWith).

The AssociateWith filters the child records to be returned.
Dim dlo As New DataLoadOptions()
        If myChildRecsIdList.Count > 0 Then
            dlo.LoadWith(Of MyClass)(Function(b) b.ChildRecs)
            dlo.AssociateWith(Of MyClass)(Function(c As MyClass) _
           c.ChildRecs.Where(Function(p) myChildRecsIdList.Contains(p)))
            myDataContext.LoadOptions = dlo
        End If

Open in new window

freddieheinekenAuthor Commented:
The above works fine if only one criteria is needed. However I wanted to check two field on the child record matched two fields on an input class.
I implemented a custom IEqualityComparer for the Contains clause above but kept receiving a
{"Unsupported overload used for query operator 'Contains'."} message. The generated SQL was obviously too complex.

In the end I resorted to an unsatisfying loop of all records returned. Still it works...not sure about performance at this stage though.

            For Each b In output
                For Each sr In myChildClassesToSearchFor
                    If b.myChildRecs.Contains(sr, New myCustomComparer) Then
                    End If

Open in new window


Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now