Using aggregate functions SUM and COUNT together in one LINQ query

April 20th, 2010 by ganton | Print

Today, I needed to use more then one aggregate function together in one LINQ query and I did a brief search in the internet. I found some sites that can be useful but not really a sample for my case. One of the nice web sites with samples is 101 LINQ Samples. It contains samples in VB.Net but it should not be a problem for a .Net developer. Back to my case. I wanted to have a query that joins a table with meta info with a many-to-many table. I needed to get the total count of the data limited by the FK from many-to-many table and to get the sum of a size field from the meta info table. Stop talking and show an example. :) See below the LINQ query.

   1: from f in dataContext.GetRepository<MetaData>()

   2: join af in dataContext.GetRepository<AnswerMetaData>()

   3:     on f.Id equals af.MetaDataId

   4: where af.AnswerId == answerId

   5: group f.Size by af.AnswerId into result

   6: select new

   7: {

   8:     Size = result.Sum(),

   9:     Count = result.Count()

  10: };

This query groups the size field from meta info table by answer id. In other words it will return all related meta info size per a concrete answer and the number of meta info records. This LINQ query produces a T-SQL statement like those below.

   1: SELECT SUM([t0].[Size]) AS [Size], COUNT(*) AS [Count]

   2: FROM [MetaData] AS [t0]

   3: INNER JOIN [AnswerMetaData] AS [t1] ON [t0].[Id] = [t1].[MetaDataId]

   4: WHERE [t1].[AnswerId] = @p0

   5: GROUP BY [t1].[AnswerId]

It works!. :) I believe this solution can be used in LINQ query that requires more aggregate functions in it.

Leave a Reply