Linq Querying with Grouping and Sum

I’m by no means an expert with SQL, to be honest I try to avoid it at all costs, however I have enough knowledge of it to get by, able to throw a query together into order to get the data I want.

C# implementation of data querying is great, however by no means simpler, in a lot of cases more complicated.

Recently I had a situation where I was required to to a selection from an SQL server database which group together distinct lines, as well as sums the values in a column. This was fairly straight forwards in SQL.

select distinct PeriodName, Con, Cat, Std, TaskSts, sum(Cnt) as TaskCount
from ProgressCount where PeriodName = 'JUL10'
group by PeriodName, Con, Cat, Std, TaskSts

However, I then had to do the same query in Linq, which I had no idea how to do it. After a bit of research I figured it out.

from r in cpx.ProgressCount
            where r.PeriodName.ToLower() == Month.ToLower()
            group r by new { r.PeriodName, r.Con, r.Cat, r.TaskSts} into g
            select new RScount()
            {
                PeriodName = g.Key.PeriodName,
                Con = g.Key.Con,
                Cat = g.Key.Cat,
                TaskSTs = g.Key.TaskSts,
                TaskCount = g.Sum(r => r.Cnt).Value                                    
             }

It’s pretty crazy, using an anonymous type for the grouping (which had to go before the select statement, as everything in linq is done in the opposite order from SQL), and my own type for the selection object, plus a Lambda expression to sum up the values.

I found a useful program, called ‘Linqpad’ http://www.linqpad.net/ which is like SQL Service Management Studio, except you can use C# linq queries against a database, which is great for testing whether your Linq query returns the correct data or not.

This entry was posted in C# and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *