相関サブクエリを使ったデータ取得 - EF Core

ef-core

EF Coreで相関サブクエリを使ってデータを取得するサンプルを書いてみました。

サブクエリはselect句、from句、where句に記述できますが、今回はwhere句内のサブクエリです。相関ではない単純なサブクエリを試したあと、相関サブクエリでのデータ取得を試しています。

テーブル作成、データの準備

まずはサンプル用のテーブルを作成してデータを投入します。飲食店のお品書きのようなデータです。*1

-- テーブル作成
create table dbo.MenuItem(
    Id int not null,
    Name nvarchar(6) not null,
    Category nvarchar(3) not null,
    Price decimal(3) not null,
    constraint PK_MenuItem primary key(Id)
);

-- データ投入
insert into dbo.MenuItem(Id, Name, Category, Price)
output inserted.*
values
    (1, N'純けい', N'串焼き', 500),
    (2, N'しろ', N'串焼き', 400),
    (3, N'若皮', N'串焼き', 300),
    (4, N'串カツ', N'揚げ物', 400),
    (5, N'ポテトフライ', N'揚げ物', 200),
    (6, N'レンコン揚げ', N'揚げ物', 300);
/*
Id  Name       Category  Price
--- ---------- --------- ------
1   純けい      串焼き     500
2   しろ        串焼き     400
3   若皮        串焼き     300
4   串カツ      揚げ物     400
5   ポテトフライ 揚げ物     200
6   レンコン揚げ 揚げ物     300
*/
エンティティ、DBコンテキスト

上記テーブルをマッピングするエンティティクラスを作成し、DBコンテキストも用意します。

// エンティティ
public class MenuItem {
    public int Id { get; set; }
    public string Name { get; set; }
    public string Category { get; set; }
    public decimal Price { get; set; }

    public override string ToString()
        => $"{nameof(Id)} = {Id}, {nameof(Name)} = {Name}, {nameof(Category)} = {Category}, {nameof(Price)} = {Price}";
}

// DBコンテキスト
public class AppDbContext : DbContext {
    public DbSet<MenuItem> MenuItems { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        modelBuilder.Entity<MenuItem>().ToTable(nameof(MenuItem));
    }
}

諸々準備ができました。サブクエリを使ったデータ取得を試したいと思います。

サブクエリでデータを取得

まずは相関ではないサブクエリ。（相関サブクエリに対していい呼び方があるといいんですが、なんて言うんでしょう。）

平均価格以上のMenuItemを取得してみます。ちなみに平均価格は350です。

var context = new AppDbContext();
var items = await context.MenuItems
    .Where(item => item.Price >=
        // 平均価格を求めるサブクエリ
        context.MenuItems.Average(item => item.Price))
    .ToListAsync();
foreach (var item in items) {
    Console.WriteLine(item);
}
// 結果
// ※平均価格は350
/*
Id = 1, Name = 純けい, Category = 串焼き, Price = 500
Id = 2, Name = しろ, Category = 串焼き, Price = 400
Id = 4, Name = 串カツ, Category = 揚げ物, Price = 400
*/

Whereメソッドの条件式のうち、DbSetを使ってAverageメソッドを呼び出している部分がサブクエリになります。

EF Coreが実行したSQLを確認してみると想像通りのサブクエリでした。

-- 実行されたSQL
SELECT [m].[Id], [m].[Category], [m].[Name], [m].[Price]
FROM [MenuItem] AS [m]
WHERE [m].[Price] >= (
    SELECT AVG([m0].[Price])
    FROM [MenuItem] AS [m0])
相関サブクエリでデータを取得

続いて相関サブクエリを試してみます。

カテゴリ別の平均価格以上のMenuItemを取得してみましょう。

Whereメソッド内のサブクエリになる部分で、内側のクエリのitem2.Categoryと外側のクエリのitem1.Categoryを比較するようにします。

var context = new AppDbContext();
var items = await context.MenuItems
    .Where(item1 => item1.Price >=
        // カテゴリ別平均価格を求める相関サブクエリ
        context.MenuItems
            .Where(item2 => item2.Category == item1.Category)
            .Average(item => item.Price))
    .ToListAsync();
foreach (var item in items) {
    Console.WriteLine(item);
}
// 結果
// ※串焼きの平均価格は400、揚げ物の平均価格は300
/*
Id = 1, Name = 純けい, Category = 串焼き, Price = 500
Id = 2, Name = しろ, Category = 串焼き, Price = 400
Id = 4, Name = 串カツ, Category = 揚げ物, Price = 400
Id = 6, Name = レンコン揚げ, Category = 揚げ物, Price = 300
*/

ログから実行されたSQLを確認してみると、だいたい想像した通りの相関サブクエリになっていました。

-- 実行されたSQL
SELECT [m].[Id], [m].[Category], [m].[Name], [m].[Price]
FROM [MenuItem] AS [m]
WHERE [m].[Price] >= (
    SELECT AVG([m0].[Price])
    FROM [MenuItem] AS [m0]
    WHERE ([m0].[Category] = [m].[Category]) OR ([m0].[Category] IS NULL AND [m].[Category] IS NULL))

というより実際はこういったSQLをイメージしながらLINQを組み立てた気もします。

以上、EF Coreでは生SQLを書かなくても相関サブクエリを実行してデータ取得できるというサンプルでした。

参考

*1:このデータは架空であり、実在するものとは一切関係ありません。