Derik Whittaker

Syndication

News


Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
Multi-Column Grouping with Linq2Sql

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,


Posted 04-30-2008 7:32 AM by Derik Whittaker
Filed under: , ,

[Advertisement]

Comments

Dew Drop - April 30, 2008 | Alvin Ashcraft's Morning Dew wrote Dew Drop - April 30, 2008 | Alvin Ashcraft's Morning Dew
on 04-30-2008 9:09 AM

Pingback from  Dew Drop - April 30, 2008 | Alvin Ashcraft's Morning Dew

Amir wrote re: Multi-Column Grouping with Linq2Sql
on 09-17-2009 1:44 PM

Thanks for the jump start.  It's even trickier if you add a where clause, order by and skip/take.

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)