Linq - use criteria on child records

Posted on 2009-04-20
Last Modified: 2013-12-20

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

Question by:freddieheineken
    LVL 9

    Expert Comment

    by:Sreedhar Vengala
    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


    Author Comment

    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.

    Accepted Solution

    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


    Author Comment

    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

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now