I have three tables and you need to write a linq query that extracts from all Acts based on two different joins.
- I need all the activities in which volunteers exist in ActivityVolunteers.
- I need all the actions in which the OrganizationID is in the action table and the volunteer belongs to the organizer (OrganizationVolunteers), but not to ActivityVolunteers. Volunteers may not belong to this activity, but they belong to the organization.
Below is the table structure and my attempt to write Linq.
activity
- ActivityID
- Activityname
- OrganizationID
ActivityVolunteers
- ActivityID
- VolunteerID
- ActivityRole
OrganizationVolunteers
- OrganizationID
- VolunteerID
Here is my weak linq attempt, I can't figure out how to join the two result sets to get unique actions.
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
I tried combining, but it duplicates the entries.
(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() })
source share