LINQ count in group from join

Question

I have a LINQ statement I am trying to get right, so maybe going about this all wrong. My objective is to query a table and join in another table to get counts.

Places
ID, Display

ProfilePlaces
ID, PlaceID, Talk, Hear

Basically Places have ProfilePlaces in a one to many relationship. I want to get the number SUM of ProfilePlaces that have Talkand Hear. Talkand Hear are bit fields.

The following gives me a unique list of Places, so I need to add in the Talkand Hear counts.

var counts = from p in db.Places
             join pp in db.ProfilePlaces on p.ID equals pp.PlaceID
             group new { Place = p } by p.Display;

I thought something like this, but not having any luck

var counts = from p in db.Places
             join pp in db.ProfilePlaces on p.ID equals pp.PlaceID
             group new { Place = p, 
                         Talk = pp.Count(t => t.Talk == true),
                         Hear = pp.Count(t => t.Hear == true) 
                       } by p.Display;

Thanks for any help.

Answer 1

You want to do a GROUP JOIN to get the counts for each Place.

var counts2 = from p in places
              join pp in profilePlaces on p.ID equals pp.PlaceID into g
              select new
              {
                   Place = p,
                   CountMeet = g.Count(a => a.Meet),
                   CountTalk = g.Count(a => a.Talk)
              };

Here's the documentation on the different joins from MSDN:
http://msdn.microsoft.com/en-us/library/bb311040.aspx