Linq outer join with grouping in subquery

Question

Hi,

I need to convert the following correlated subquery in Linq to Sql. I am able to achieve the left outer join part. However, grouping in a subquery is where i am getting incorrect results.

SELECT 
  ae.Id,ae.Title
,(select COUNT(*) from [dbo].[AssociationEventRSVP]  where RSVPStatus='Y'
 group by  AssociationEventId, RSVPStatus having RSVPStatus='Y'
 and AssociationEventId=ar.AssociationEventId) as CountYes
,(select COUNT(*) from [dbo].[AssociationEventRSVP]  
 group by  AssociationEventId, RSVPStatus having RSVPStatus='N'
 and AssociationEventId=ar.AssociationEventId) as CountNo 
  FROM [dbo].[AssociationEvents] as ae
  left outer join AssociationEventRSVP as ar
  on ae.Id=ar.AssociationEventId

Thanks in advance for your help.

Tushar M.

Answer 1

I'd first refactor your query to this:

SELECT 
ae.Id,
ae.Title,
(select COUNT(*) FROM [dbo].[AssociationEventRSVP] WHERE RSVPStatus='Y' AND AssociationEventId=ae.Id) AS CountYes,
(select COUNT(*) FROM [dbo].[AssociationEventRSVP] WHERE RSVPStatus='N' AND AssociationEventId=ae.Id) AS CountNo 
FROM [dbo].[AssociationEvents] as ae

And here's a simple (not necessarily efficient) LINQ to SQL conversion:

var results = from ae in context.AssociationEvents
              select new
              {
                  ae.Id,
                  ae.Title,
                  CountYes = context.AssociationEventRSVP.Where(aer => aer.RSVPStatus == "Y" && aer.AssociationEventId == ae.Id).Count(),
                  CountNo  = context.AssociationEventRSVP.Where(aer => aer.RSVPStatus == "N" && aer.AssociationEventId == ae.Id).Count()
              };