Referencing another table in a where clause RRS feed

  • Question

  • I have a function that dynamically builds a LINQ query based on user-selected search criteria.  I begin building the query like so:


    Dim qServiceTechQry = From st In db.ServiceTechs Select st


    Then I use a Case statement to build the where clause.  For example:


    Case "ServiceTechID"

       qServiceTechQry = qServiceTechQry.Where(Function(st) st.ServiceTechID = sExactVal)


    All well and good so far.  The problem comes if the user wants to search for equipment assigned to a service tech.  ServiceTechID is a foreign key in the Equipment table.  In SQL terms, I am looking for something like this:


    ...Where st.ServiceTechID = (Select eq.ServiceTechID From Equipment As eq Where eq.EquipmentID = @paramID)


    I haven't been able to find any information about how to do this in LINQ.  Any suggestions?  Or do I need to approach the query in a whole different way?

    Thursday, August 28, 2008 7:47 PM


  • If you make an association between the tables you can select it without needing to join or subselect yourself.


    In the case of a 1-to-1 you can just access the properties of the associated object, e.g.


    Where st.ServiceTech.Equipment.EquipmentID = 1


    In the case of 1-to-many you can use the contains operator.




    Friday, August 29, 2008 1:15 AM