C# Linq Grouping

Question

Hi,

I'm experimenting with Linq and am having trouble figuring out grouping I've gone through several tutorials but for some reason can't figure this out.

As an example, say I have a table (SiteStats) with multiple website IDs that stores a count of how many visitors by type have accessed each site in total and for the past 30 days.

SiteID.........VisitorType.........Last30........Total
.......1.......................1................10.........100
.......1.......................2................40.........140
.......2.......................1................20.........180

etc.

In SQL, I can easily get the counts for SiteID 1 with the following:

SELECT SiteID,
SUM(Last30) AS Last30Sum
FROM Sites
WHERE SiteID = 1
GROUP BY SiteID

and should get a row like

SiteID.........Last30Total
.......1......................50

However I'm not sure how to get this result using Linq. I've tried

var statsRecord = from ss in db.SiteStats
where ss.SiteId == siteId
group ss by ss.SiteId into ss
select ss;

but I'm not able to get back the total with something like

statsRecord.Last30

Can someone please let me know where I'm going wrong. Any help is appreciated.

Answer 1

Easiest way for me to illustrate is using in-memory objects so it's clear what's happening. LINQ to SQL should be able to take that same LINQ query and translate it into appropriate SQL.

public class Site
{
    static void Main()
    {
        List<Site> sites = new List<Site>()
        {
            new Site() { SiteID = 1, VisitorType = 1, Last30 = 10, Total = 100, },
            new Site() { SiteID = 1, VisitorType = 2, Last30 = 40, Total = 140, },
            new Site() { SiteID = 2, VisitorType = 1, Last30 = 20, Total = 180, },
        };

        var totals =
            from s in sites
            group s by s.SiteID into grouped
            select new
            {
                SiteID = grouped.Key,
                Last30Sum = 
                    (from value in grouped
                     select value.Last30).Sum(),
            };

        foreach (var total in totals)
        {
            Console.WriteLine("Site: {0}, Last30Sum: {1}", total.SiteID, total.Last30Sum);
        }
    }

    public int SiteID { get; set; }
    public int VisitorType { get; set; }
    public int Last30 { get; set; }
    public int Total { get; set; }
}

Answer 2

Actually, although Thomas' code will work, it is more succint to use a lambda expression:

        var totals =
        from s in sites
        group s by s.SiteID into grouped
        select new
        {
            SiteID = grouped.Key,
            Last30Sum = grouped.Sum( s => s.Last30 )
        };

which uses the Sum extension method without the need for a nested LINQ operation.

as per the LINQ 101 examples - http://msdn.microsoft.com/en-us/vcsharp/aa336747.aspx#sumGrouped