Using aggregate functions SUM and COUNT together in one LINQ query
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