Linq 跨三个表联接

Question

我有三个表，需要编写一个基于两个不同联接从所有事件中提取的 linq 查询。

我需要 ActivityVolunteers 中志愿者所在的所有事件

我需要组织ID在事件表中并且志愿者属于组织(OrganizationVolunteers)但不属于ActivityVolunteers的所有事件。志愿者可能不属于事件，但属于组织。

下面是我尝试编写 Linq 时的表结构。

事件

事件ID

事件名称

组织 ID

事件志愿者

事件ID

志愿者ID

事件角色

组织志愿者

组织 ID

志愿者ID

这是我在 linq 上的弱尝试，我无法弄清楚加入两个结果集只获得独特的事件。

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

我尝试了一个联合，但它正在复制记录。

(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()
    })

Answer 1

(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 = ""
    })