C# LINQ Group by句でカウント取得

snippet

グループ化してカウント

年齢をキーにグループ化してカウントするクエリをLINQで。
■SQL

SELECT Age,COUNT(*) FROM people GROUP BY Age ORDER BY Age

■LINQ

class Program
{
    static IEnumerable<Person> GetPeople()
    {
        var people = new List<Person>() {
            new Person() { Id = 1, Name = "Bob", Age = 17, State = "New York" }
            , new Person() { Id = 2, Name = "Nancy", Age = 18, State = "California" }
            , new Person() { Id = 3, Name = "Robert", Age = 18, State = "California" }
            , new Person() { Id = 4, Name = "Lisa", Age = 18, State = "California" }
            , new Person() { Id = 5, Name = "Saya", Age = 17, State = "New York" }
            , new Person() { Id = 6, Name = "Alice", Age = 17, State = "California" }
            , new Person() { Id = 7, Name = "Masa", Age = 18, State = "California" }
        };

        return people;
    }

    static void Main(string[] args)
    {
        var people = GetPeople();

        var ageGroup = people.GroupBy(c => c.Age)
            .Select(c => new { Age = c.Key, Count = c.Count() })
            .OrderBy(c => c.Age);

        foreach (var item in ageGroup)
        {
            Console.WriteLine(item);
        }

        Console.ReadKey();
    }
}

■結果

{ Age = 17, Count = 3 }
{ Age = 18, Count = 4 }

複合キーでグループ化してカウント

年齢、州をキーにグループ化してカウントするクエリをLINQで。
■SQL

SELECT Age,State,COUNT(*) FROM people GROUP BY Age,State ORDER BY Age,State

■LINQ

var ageStateGroup = people.GroupBy(c => new { age = c.Age, state = c.State })
    .Select(c => new { Age = c.Key.age, State = c.Key.state, Count = c.Count() })
    .OrderBy(c => c.Age)
    .ThenBy(c => c.State);

foreach (var item in ageStateGroup)
{
    Console.WriteLine(item);
}

■結果

{ Age = 17, State = California, Count = 1 }
{ Age = 17, State = New York, Count = 2 }
{ Age = 18, State = California, Count = 4 }

参考

https://msdn.microsoft.com/ja-jp/library/bb384063.aspx