none
LINQ to SQL Nested Select VB RRS feed

  • Question

  • Hi, I have a Location lookup table that has Loc_ID and Location_Desc, and a Loc_Preference table that has a Personnel_ID, and that persons 3 location choices (Loc_ID1, Loc_ID2 and Loc_ID3) that correspond to the Location.Loc_ID column in the Location table.

    I have the following SQL using nested select to retrieve a single row for each Personnel_ID and that person's 3 location choices.  I can't figure out how to "convert" this SQL into LINQ to SQL (if possible).  Thanks  for any help with this!

    Select t1.Personnel_ID, Choice1, Choice2, Choice3
    From(
    (Select lp.Personnel_ID, Loc_Desc as Choice1
    From Loc_Preference lp inner join Locations l
    on lp.Loc_ID1 = l.Loc_ID) t1
    INNER JOIN
    (Select lp.Personnel_ID, Loc_Desc as Choice2
    From Loc_Preference lp inner join Locations l
    on lp.Loc_ID2 = l.Loc_ID) t2
    ON t1.Personnel_ID = t2.Personnel_ID
    INNER JOIN
    (Select lp.Personnel_ID, Loc_Desc as Choice3
    From Loc_Preference lp inner join Locations l
    on lp.Loc_ID3 = l.Loc_ID) t3
    ON t2.Personnel_ID = t3.Personnel_ID)

    Wednesday, February 10, 2016 4:38 PM

Answers

  • Hi KimFromPA,

    >> I can't figure out how to "convert" this SQL into LINQ to SQL (if possible). 

    First, you could retrieve the data to different custom object, and then join them and retrieve the data what you want. And the code snippet as below for your reference.

    Using db = New DemoDataContext()
    	Dim query1 = From p In db.Loc_PreferencesJoin q In db.Locations On p.Loc_ID1 = q.Loc_IDNew With { _
    		Key .Personnel_ID = p.Personnel_ID, _
    		Key .Choice1 = q.Location_Desc _
    	}
    	Dim query2 = From p In db.Loc_PreferencesJoin q In db.Locations On p.Loc_ID2 = q.Loc_IDNew With { _
    		Key .Personnel_ID = p.Personnel_ID, _
    		Key .Choice2 = q.Location_Desc _
    	}
    	Dim query3 = From p In db.Loc_PreferencesJoin q In db.Locations On p.Loc_ID3 = q.Loc_IDNew With { _
    		Key .Personnel_ID = p.Personnel_ID, _
    		Key .Choice3 = q.Location_Desc _
    	}
    
    	Dim result = From q1 In query1Join q2 In query2 On q1.Personnel_ID = q2.Personnel_IDJoin q3 In query3 On q1.Personnel_ID = q3.Personnel_IDNew With { _
    		Key .Personnel_ID = q1.Personnel_ID, _
    		Key .Choice1 = q1.Choice1, _
    		Key .Choice2 = q2.Choice2, _
    		Key .Choice3 = q3.Choice3 _
    	}
    	For Each item As var In result
    		Console.WriteLine(String.Format("{0} + {1} + {2} + {3}", item.Personnel_ID, item.Choice1, item.Choice2, item.Choice3))
    	Next
    	Console.ReadKey()
    End Using
    

    Best regards,

    Cole Wu

    • Marked as answer by KimFromPA Thursday, February 11, 2016 12:12 PM
    Thursday, February 11, 2016 3:01 AM
    Moderator