转换SQL到LINQ问题

Question

我被困在这个尴尬的一天…好像不能把这个转换成linq。我的另一个问题是参加者可以是空的。

select c.activityId, count(distinct b.attendeeId)
from Attendee a, sponsor_activity c
left outer join sponsor_attendance b
on c.ActivityId = b.ActivityId
where  a.RegistrationId = 62
AND c.SponsorLevelId = 2
group by c.activityId 

到目前为止，我有这个代码…但是我没有得到不同的值

var activity_count = (from c in db.Sponsor_Activitys
where c.SponsorLevelId == pledgelvl
from a in db.Attendees.DefaultIfEmpty()
where a.RegistrationId == registration
select new { Activityid = c.ActivityId, NumAttending = db.Sponsor_Attendances.Count(x => x.ActivityId == c.ActivityId) })
.ToList();

Sponsor_Attendance

AttendanceId
AttendeeId
ActivityId

Sponsor_Activity

ActivityId
SponsorLevelId

与会者

AttendeeId
RegistrationId

的回报:

## ActivityID ##  ## NumAttending ##
2                   4
3                   0
4                   2
2                   4
3                   0
4                   2
2                   4
3                   0
4                   2

目前有3个与会者的registrationid匹配…这就是为什么它在输出中重复了3次。

Answer 1

首先，如果您的原始查询是可读的，这将有所帮助。:)

查询:

SELECT  c.activityId
      , COUNT(DISTINCT b.attendeeId)
FROM    Attendee a
      , sponsor_activity c
LEFT OUTER JOIN sponsor_attendance b
        ON c.ActivityId = b.ActivityId
WHERE   a.RegistrationId = 62 AND
        c.SponsorLevelId = 2
GROUP BY c.activityId; 

Linq:

var activity_count = (from activity in db.Sponsor_Activitys
                      where activity.SponsorLevelId == pledgelvl
                      from attendee in db.Attendees.DefaultIfEmpty()
                      where attendee.RegistrationId == registration
                      select new
                      {
                          Activityid = activity.ActivityId,
                          NumAttending = db.Sponsor_Attendances.Count(x => x.ActivityId == activity.ActivityId)
                      }).ToList();

我的回答:

   var query = from activity in db.Sponsor_Activitys
            // Left outer join onto sponsor_attendances
            join attendance in db.Sponsor_Attendances
            on activity.ActivityId equals attendance.ActivityId into g
            from q in g.DefaultIfEmpty()
            join attendee in db.Attendees
            on q.AttendeeId equals attendee.AttendeeId
            where attendee.RegistrationId == registration && 
                  activity.SponsorLevelId == pledgelvl
            select new
            {
                Activityid = activity.ActivityId,
                NumAttending = db.Sponsor_Attendances.Count(x => x.ActivityId == activity.ActivityId)
            }

考虑到笛卡尔连接(通常很糟糕!)，这可能是一个更好的示例，只执行SQL而不是尝试转换为Linq。