C# Linq Grouping

Question

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 ║
╚════════╩═════════════╩════════╩══════╝

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

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

Answer 2

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; }
}