Randoom a Michael Friis production

Posted
16 May 2010 @ 5pm

Categories
C#, LINQ, SQL

Tagged

You're reading Randoom, a Michael Friis production

Linq-to-SQL, group-by, subqueries and performance

If you’re using Linq-to-SQL, doing group-by and selecting other columns than those in the grouping-key, performance might suffer. This is because there is no good translation of such queries to SQL and Linq-to-SQL has to resort to doing multiple subqueries. Matt Warren explains here. I experienced this firsthand when grouping a lot of geocoded events by latitude and longitude and selecting a few more columns (EventId and CategoryId in the example below):

from e in db.Events
group e by new { e.Lat, e.Lng } into g
select new
{
    g.Key.Lat,
    g.Key.Lng,
    es = g.Select(_ => new { _.EventId, _.CategoryId })
};

One possible solution is to fetch all events, to a ToList() and do the grouping in-memory.

var foo =
    from e in db.Events
    select new { e.Lat, e.Lng, e.EventId, e.CategoryId };

var bar = from e in foo.ToList()
            group e by new { e.Lat, e.Lng } into g
            select new
            {
                g.Key.Lat,
                g.Key.Lng,
                es = g.Select(_ => new { _.EventId, _.CategoryId })
            };

2 Comments

Posted by
Pablo Bonilla
23 March 2011 @ 7am

Thanks! your tip save me a lot of time


Posted by
Sameh Fakoua
8 May 2012 @ 5pm

Thanks, you are 100% right, but sometime you may face network performance when you need to use having clause.

Here’s my question:
http://stackoverflow.com/questions/10501742/linq-to-sql-performance-with-grouping


Leave a Comment