SQL to LINQ group by

Question

Hi, I'm trying to convert the following SQL to LINQ. I've had success with 1 group by variable but couldn't get this to work. Any help would be appreciated.

  select ContactID, EventID, Count=count(*) 
  from ScanLogs s, Exhibits e
  where s.ExhibitID = e.ExhibitID
  group by s.ContactID, e.EventID

The result looks something like this:

ContactID EventID Count
300009 2000048 2
300009 2000096 1
300036 2000096 1

Answer 1

I believe this will do it:

var query = from s in ScanLogs
            from e in Exhibits
            where s.ExhibitID == e.ExhibitID
            group 1 by new { s.ContactID, e.EventID } into g
            select new { g.Key.ContactID, g.Key.EventID, Count = g.Count() };

It isn't important what is grouped (1 in this case), you're just counting how many there are.

I probably would have used a join however. It should be equivalent.

var query = from s in ScanLogs
            join e in Exhibits on s.ExhibitID equals e.ExhibitID
            group 1 by new { s.ContactID, e.EventID } into g
            select new { g.Key.ContactID, g.Key.EventID, Count = g.Count() };

Answer 2

Another varian using join and group by.

var query = from c in ScanLogs
            join d in Exhibits on c.ExhibitID equals d.ExhibitID
            group new{
             g.Key.ContactID,
             g.Key.EventID,
             Count = g.Count()
            }
            by new {
              c.ContacID, d.EventID
            } into g
            select g;