Linq: multiple relationships to same table

Question

I have the same problem as this guy:

I have a table that has references my tblstaff table twice for two different people. Now that I have added this second reference neither of them work.

What is up w/ that?

Answer 1

By adding two references you are saying select all rows where the PK = this and the PK = that. Since the Pk is unique that condition will always be false.

I don't know linq really well but in SQL you would reference the table twice and alias it as in

.... FROM tblstaff staff1, tblstaff staff2

Answer 2

I had to create a function to getStaffbyID and manually call it when I wanted the name instead of the id.

    Dim id As String = 1
    Session("BusinessPlanID") = id

    Dim oLinq As New Linq
    Dim bp As BusinessPlan = oLinq.getBusinessPlanById(id)

    Dim assignedStaff As Staff = oLinq.getStaffById(bp.AssignedStaffID)
    Dim mp As Staff = oLinq.getStaffById(bp.MPStaffID)

    Public Function getBusinessPlanById(ByVal inId As String) As BusinessPlan

        Dim db As New BusinessPlanDataDataContext

        Dim bpItem = (From b In db.BusinessPlans _
                      Select b _
                      Where b.BusinessPlanID = inId).SingleOrDefault

        Return bpItem

    End Function

    'Linq Class --------------------------------------------------------'

    Public Function getStaffById(ByVal inId As String) As Staff

       Dim db As New BusinessPlanDataDataContext

       Dim staffItem = (From s In db.Staffs _
                       Select s _
                       Where s.StaffID = inId).SingleOrDefault

       Return staffItem

    End Function