LINQ - how to join three tables in LINQ - Asked By aman on 16-Mar-12 02:37 AM

hi all,

i have to join three tables in Linq and i basically want data from Two Respective table.

please help,

thanks,
Somesh Yadav replied to aman on 16-Mar-12 04:01 AM

Below is the table structure an my attempt at writing the Linq.

Activities
  • ActivityID
  • ActivityName
  • OrganizationID
ActivityVolunteers
  • ActivityID
  • VolunteerID
  • ActivityRole
OrganizationVolunteers
  • OrganizationID
  • VolunteerID

(from a in Activities
join ov in OrganizationVolunteers on a.OrganizationID equals ov.OrganizationID
join av in ActivityVolunteers
on a.ActivityID equals av.VolunteerID into JoinedActVol
from av in JoinedActVol.DefaultIfEmpty()
where ov.VolunteerID==1
select new 
    {
        a.ActivityID,
        a.ActivityName,
        av.ActivityRole,
        a.OrganizationID,
        OrganizationName = (from o in Organizations where o.OrganizationID == a.OrganizationID select o.OrganizationName).FirstOrDefault()
    }).Union
    (from a in Activities
join av in ActivityVolunteers on a.ActivityID equals av.ActivityID 
where av.VolunteerID==1 && a.OrganizationID == null
select new 
    {
        a.ActivityID,
        a.ActivityName,
        av.ActivityRole,
        a.OrganizationID,
        OrganizationName = ""
    })


Suchit shah replied to aman on 16-Mar-12 01:36 PM
sample example of this are here :

Dim query = From ts In db.TimeSheets_ 
Join tr In db.TimeRecords On tr.TimeSheetId Equals ts.TimeSheetId _ 
Where ts.IsArchive = False And ts.IsCompleted = False And tr.TimeOut IsNot Nothing _
Group By key = New With {ts.Student, .MonthYear = (tr.TimeOut.Value.Month & "/" & tr.TimeOut.Value.Year)} Into TotalHour = Sum(BonusHour) _
Select key.Student.StudentId, key.Student.AssignedId, key.MonthYear, TotalHour