相関サブクエリを使ったデータ取得 - 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:このデータは架空であり、実在するものとは一切関係ありません。