none
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

Answers

  • 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.

     

    [)amien

     

    Friday, August 29, 2008 1:15 AM
    Moderator