Derik Whittaker

Today as I was diving further into my Linq2Sql Odyssey I ran into a need to do grouping on data.  Bud not just single column grouping, I needed to group by multiple columns from multiple tables.  Because it took me a few minutes to get right, I thought I would share my experiences.

Imagine if you will you have the following SQL statement

SELECT  COUNT(*) AS Count,
        cs.SendID,
        cs.Name AS SendName,
        cts.ListID,
        cts.ListName,
        ctom.EmailAddress
FROM    CampaignTrackingOpenedMail AS ctom
        INNER JOIN CampaignTrackingSummary AS cts 
			ON ctom.CampaignTrackingSummaryID = cts.[ID]
        INNER JOIN CampaignSend AS cs 
			ON cts.SendID = cs.SendID
GROUP BY cs.SendID,
        cs.Name,
        cts.ListID,
        cts.ListName,
        ctom.EmailAddress

And you would like to turn that into a Linq Statement.  At first I was a little stumped because I was not using the correct syntax.  My first attempt had me trying the following

from ctom in CampaignTrackingOpenedMails
join cts in CampaignTrackingSummaries on ctom.CampaignTrackingSummaryID equals cts.ID
join cs in CampaignSends on cts.SendID equals cs.SendID
group ctom by cs.SendID, cs.Name, cts.ListID, cts.ListName, ctom.EmailAddress into emailItems
select new { SendID = emailItems.Key.SendID,
			SendName = emailItems.Key.Name,
			ListID = emailItems.Key.ListID,
			ListName = emailItems.Key.ListName,
			EmailAddress = emailItems.Key.EmailAddress,
			Count = emailItems.Count()
			}

After a bit of searching I realized the error of my ways.  I needed to use an anonymous type as my group by object value.  So I made the changes and here is my correct attempt

from ctom in CampaignTrackingOpenedMails
join cts in CampaignTrackingSummaries on ctom.CampaignTrackingSummaryID equals cts.ID
join cs in CampaignSends on cts.SendID equals cs.SendID
group ctom by new { cs.SendID, cs.Name, cts.ListID, cts.ListName, ctom.EmailAddress } into emailItems
select new { SendID = emailItems.Key.SendID,
			SendName = emailItems.Key.Name,
			ListID = emailItems.Key.ListID,
			ListName = emailItems.Key.ListName,
			EmailAddress = emailItems.Key.EmailAddress,
			Count = emailItems.Count()
			}

A few things to notice here are:

• The use of anonymous types
  group ctom by new { cs.SendID, cs.Name, cts.ListID, cts.ListName, ctom.EmailAddress } into emailItems
  
• How the group by columns are used
  select new { SendID = emailItems.Key.SendID ......

Well, hope this helps someone else.

BTW, If you are NOT using LinqPad for your Linq2Sql stuff, you really need to check it out.

Till next time,