title: Linq Join across three tables
body: <p>I have three tables and need to write a linq query that pulls from all Activities based on two different joins.  </p>
  
  <ul>
  <li>I need all Activities where the volunteer exist in ActivityVolunteers</li>
  <li>I need all Activities where the OrganizationID is in the Activity table and the volunteer belongs to the organizaiton (OrganizationVolunteers) but not in ActivityVolunteers. The volunteer may not belong to the activity but belongs to the organizaton.</li>
  </ul>
  
  <p>Below is the table structure an my attempt at writing the Linq.  </p>
  
  Activities
  
  <ul>
  <li>ActivityID</li>
  <li>ActivityName</li>
  <li>OrganizationID</li>
  </ul>
  
  ActivityVolunteers
  
  <ul>
  <li>ActivityID</li>
  <li>VolunteerID</li>
  <li>ActivityRole</li>
  </ul>
  
  OrganizationVolunteers
  
  <ul>
  <li>OrganizationID</li>
  <li>VolunteerID</li>
  </ul>
  
  <p>Here is my weak attempt at the linq, I can't figure out joining the two result sets to only get the unique activities.</p>
  
  <pre><code>from a in Activities
  join av in ActivityVolunteers on a.ActivityID equals av.ActivityID 
  where av.VolunteerID==1
  select new 
      {
          a.ActivityID,
          a.ActivityName,
          av.ActivityRole,
          a.OrganizationID
      }
  
  from org in (from a in Activities
  join ov in OrganizationVolunteers on a.OrganizationID equals ov.OrganizationID
  where ov.VolunteerID==1
  select new 
      {
          a.ActivityID,
          a.ActivityName,
          ActivityRole = "Prospect",
          a.OrganizationID
      })
  select org
  </code></pre>
  
  I tried a union but it is duplicating records.
  
  <pre><code>(from a in Activities
  join av in ActivityVolunteers on a.ActivityID equals av.ActivityID 
  where av.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 ov in OrganizationVolunteers on a.OrganizationID equals ov.OrganizationID
  where ov.VolunteerID==1
  select new 
      {
          a.ActivityID,
          a.ActivityName,
          ActivityRole = "Prospect",
          a.OrganizationID,
          OrganizationName = (from o in Organizations where o.OrganizationID == a.OrganizationID select o.OrganizationName).FirstOrDefault()
      })
  </code></pre>