Heather Ingram Heather Ingram - 2 months ago 18
SQL Question

Convert SQL to LINQ Troubles

I have been stuck on this for an embarrassing day... can't seem to convert this to linq. My issue also is that Attendee can be null.

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


So far I have this code... but I am not getting distinct values

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


Attendee



AttendeeId
RegistrationId


Returns:

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


Currently there are 3 attendees that have a registrationid that matches... so this is why it is repeated 3 times in the output.

Answer

First, it helps if your original queries are readable. :)

Query:

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

My answer:

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

Given the cartesian join (typically bad!), this might be a better example on just executing SQL rather than trying to convert to Linq.