[Entity Framework] LINQ で Group by および Having を使用してデータを取得する

Entity Framework は、Group by、および Having を使用した集計を行うことができます。

正確には、Having は実際の SQL では使用されず、Having を使用したのと同等の SQL を使用します。具体的には、Group by による集計を行った後に、where でデータの絞り込みを行います。

なかなか高度な SQL を Entity Framework は作成してくれますね。

前提条件

以下の前提条件で動作検証しています。

• Visual Studio Express 2013 for Windows Desktop
• Entity Framework 6.0.2
• SQL Server 2012 Express
• コードファーストのコンソールアプリケーション

Entity Framework 関連

作成するデータベースは、注文マスターを想定しています。

・Order.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations;

namespace EFRelation
{
  public class Order
  {
    //注文数
    public int OrderId { get; set; }

    //顧客名
    public string CustomerName { get; set; }

    //商品番号
    public int ItemId { get; set; }

    //注文数
    public int Quantity { get; set; }

  }
}

テーブルの定義を行うコンテキストクラスを作成する必要があります。

・OrderContext..cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;

namespace EFRelation
{
  class OrderContext : DbContext
  {
    public DbSet<Order> Orders { get; set; }
  }
}

App.config にデータベースの接続文字列を追加します。コンテキストクラスと同じ名前である必要があるので注意してください。

・App.config

<connectionStrings>
  <add name="OrderContext"
       connectionString="Data Source=(local)\SQLEXPRESS;Initial Catalog=Order;Integrated Security=True;"
       providerName="System.Data.SqlClient" />
</connectionStrings>

データ更新・参照プログラム

データを更新・参照するプログラムは以下のようになります。

Group by のキーは 2 つ使用して集計しています。

また、最初に Group by だけの集計を行い、次に Having まで含めた集計を行い結果をコンソールに出力しています。

なお、実際にどのような SQL が発行されたか分かるように、SQL ログをデバッグ出力しています。

・Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;
using System.Diagnostics;

namespace EFRelation
{
  class Program
  {
    static void Main(string[] args)
    {
      //データベース初期化
      Database.SetInitializer(new DropCreateDatabaseIfModelChanges<OrderContext>());

      using (var context = new OrderContext())
      {
        //SQLログ設定
        context.Database.Log = Logger;

        var o1 = new Order() { CustomerName = "東京本社", ItemId = 1, Quantity = 5 };
        context.Orders.Add(o1);

        var o2 = new Order() { CustomerName = "東京本社", ItemId = 1, Quantity = 2 };
        context.Orders.Add(o2);

        var o3 = new Order() { CustomerName = "東京本社", ItemId = 2, Quantity = 3 };
        context.Orders.Add(o3);

        var o4 = new Order() { CustomerName = "千葉支店", ItemId = 1, Quantity = 5 };
        context.Orders.Add(o4);

        var o5 = new Order() { CustomerName = "千葉支店", ItemId = 2, Quantity = 2 };
        context.Orders.Add(o5);

        var o6 = new Order() { CustomerName = "千葉支店", ItemId = 3, Quantity = 1 };
        context.Orders.Add(o6);

        context.SaveChanges();

        //Group by による集計
        var sum = from order in context.Orders
             group order by new { order.CustomerName, order.ItemId } into orderGroup
             select new {
               CustomerName = orderGroup.Key.CustomerName,
               ItemId = orderGroup.Key.ItemId,
               Sum = orderGroup.Sum(order => order.Quantity)
             };
        Console.WriteLine("[Group by による集計]");

        foreach (var s in sum)
        {
          Console.WriteLine("CustomerName:{0}, ItemId:{1}, Sum:{2}", s.CustomerName, s.ItemId, s.Sum);
        }

        Console.WriteLine("");

        //Group by + Having 相当による集計
        var sum2 = from order in context.Orders
             group order by new { order.CustomerName, order.ItemId } into orderGroup
             where orderGroup.Sum(order => order.Quantity) > 1
             select new
             {
               CustomerName = orderGroup.Key.CustomerName,
               ItemId = orderGroup.Key.ItemId,
               Sum = orderGroup.Sum(order => order.Quantity)
             };

        Console.WriteLine("[Group by + Having 相当による集計]");
        foreach (var s in sum2)
        {
          Console.WriteLine("CustomerName:{0}, ItemId:{1}, Sum:{2}", s.CustomerName, s.ItemId, s.Sum);
        }

        Console.ReadKey();
      }
    }

    private static void Logger(string message)
    {
      Debug.Write(message);
    }

  }
}

コンソールに出力された実行結果は以下のようになります。集計結果が、2未満のデータ（赤字部分）が、Having 相当の集計では表示されていないので正しく処理されていますね。

[Group by による集計]
CustomerName:千葉支店, ItemId:1, Sum:5
CustomerName:東京本社, ItemId:1, Sum:7
CustomerName:千葉支店, ItemId:2, Sum:2
CustomerName:東京本社, ItemId:2, Sum:3
CustomerName:千葉支店, ItemId:3, Sum:1

[Group by + Having 相当による集計]
CustomerName:千葉支店, ItemId:1, Sum:5
CustomerName:東京本社, ItemId:1, Sum:7
CustomerName:千葉支店, ItemId:2, Sum:2
CustomerName:東京本社, ItemId:2, Sum:3

そして、Group by の SQL ログは以下のようになります。

SELECT
    [GroupBy1].[K2] AS [ItemId],
    [GroupBy1].[K1] AS [CustomerName],
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        [Extent1].[CustomerName] AS [K1],
        [Extent1].[ItemId] AS [K2],
        SUM([Extent1].[Quantity]) AS [A1]
        FROM [dbo].[Orders] AS [Extent1]
        GROUP BY [Extent1].[CustomerName], [Extent1].[ItemId]
    )  AS [GroupBy1]
-- Executing at 2014/01/04 13:51:55 +09:00
-- Completed in 4 ms with result: SqlDataReader

Having 相当の SQL ログは以下のようになります。Having はありませんが、かなり複雑ですね。

SELECT
    [GroupBy1].[K2] AS [ItemId],
    [GroupBy1].[K1] AS [CustomerName],
    [GroupBy1].[A2] AS [C1]
    FROM ( SELECT
        [Extent1].[CustomerName] AS [K1],
        [Extent1].[ItemId] AS [K2],
        SUM([Extent1].[Quantity]) AS [A1],
        SUM([Extent1].[Quantity]) AS [A2]
        FROM [dbo].[Orders] AS [Extent1]
        GROUP BY [Extent1].[CustomerName], [Extent1].[ItemId]
    )  AS [GroupBy1]
    WHERE [GroupBy1].[A1] > 1
-- Executing at 2014/01/04 13:51:55 +09:00
-- Completed in 2 ms with result: SqlDataReader

おわりに

Entity Framework でも、Group by と Having 相当の処理ができることを見てきました。また、Group by はキーを 2つ持たせることができることも分かりました。

大分、Entity Framework についての理解が深まってきた気がします。

参考サイト

• データをグループ化する方法 (Entity Framework)
• select - Entity Framework - GROUP and SUM - Stack Overflow
• .net - Group By Multiple Columns - Stack Overflow
• linq - Entity Framework T-Sql "having" Equivalent - Stack Overflow