LINQ tricks for performance (LINQ2SQL Old School)

I’m working quite a bit on the new Silicon Valley Code Camp site these days and introducing lots of new ways to view sessions.  Much of this code I’ve written over 8 years ago, long before I had so many tricks in my bag.

So, one of the things we track is session interest by attendee.  To do that we have two tables, SessionAttendee and Sessions.

Doing a simple join and count of the number of SessionAttendee records per session pretty much gives us what we need. Basically, we create a dictionary so we can get all the counts at once so that way, just one sql to the database

interestCountsDict = (from data in meta.SessionAttendee
                    where data.Interestlevel == 2
                    group data by data.Sessions_id
                    into g
                    orderby g.Key ascending
                    select new {cnt = g.Count(), id = g.Key}).
                       ToDictionary(k => k.id, v => v.cnt);

As I’m debugging this (now, not 8 years ago) I notice that the dictionary is over 1000 records (yes, we’ve had over 1000 sessions over the past 8 years of code camp.  So, the obvious thing is to add a where by codeCampYearId and constrain it to this year only.  Problem is, the sort above only looks in the SessionAttendee table and for that, we need to join to the Session table because that is where the year is.

At first, I shudder to think of making this linq more complex (yes, I think it’s kind of complex with just a group by and order by).  So, I decide the easiest thing to is to take it to pieces.  That is, create the linq one piece at a time.  In my case, only join to the sessions table if we are looking at one year only.  So, here is my final code which I claim is much more efficient, and for this year, only brings in this year sessions.

if (query.CodeCampYearIds.Count == 1)
{
    var xx = from data in meta.SessionAttendee
        where data.Interestlevel == 2
        select data;

    var yy = from data in xx
        join data1 in meta.Sessions on data.Sessions_id
            equals data1.Id
        where data1.CodeCampYearId == query.CodeCampYearId
        select data;

    interestCountsDict = (from data in yy
        where data.Interestlevel == 2
        group data by data.Sessions_id
        into g
        orderby g.Key ascending
        select new {cnt = g.Count(), id = g.Key}).
        ToDictionary(k => k.id, v => v.cnt);
}
else
{
    interestCountsDict = (from data in meta.SessionAttendee
        where data.Interestlevel == 2
        group data by data.Sessions_id
        into g
        orderby g.Key ascending
        select new {cnt = g.Count(), id = g.Key}).
        ToDictionary(k => k.id, v => v.cnt);
}

HTH’s