Entity Framework - EXISTSを使ったサブクエリのサンプル
WHERE句にEXISTSを使うようなデータ取得をEntity Frameworkで実行したい場合、LINQのAnyメソッドを使うようです。想像通りという気もしますが適当な1対多のテーブルとデータで試して出力されるクエリを確認してみます。
まずはモデルとDBコンテキストを用意します。ユーザが複数メールアドレスを持つ1対多の関係です。
// ユーザ public class User { public int Id { get; set; } public string Name { get; set; } // メールを複数持つ public ICollection<Mail> Mails { get; set; } } // メール public class Mail { public int Id { get; set; } public int UserId { get; set; } public string Address { get; set; } public User User { get; set; } } public class AppDbContext : DbContext { public AppDbContext(string nameOrConnectionString) : base(nameOrConnectionString) { } public IDbSet<User> Users { get; set; } public IDbSet<Mail> Mails { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); Database.Log = Console.WriteLine; // ログ出力 modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); // テーブルは単数形 } }
テーブルを作ってデータも用意します。
-- ユーザ create table [User]( Id int identity not null, Name nvarchar(10) not null, constraint PK_User primary key(Id) ); -- メール create table Mail( Id int identity not null, UserId int not null, [Address] nvarchar(20) not null, constraint PK_Mail primary key(Id), constraint FK_Mail_User foreign key(UserId) references [User](Id) ); -- サンプルデータ insert into [User](Name) values(N'Aさん'), (N'Bさん'), (N'Cさん'); insert into Mail(UserId, [Address]) values (1, N'sample-a@gmail.com'), -- Aさんのメール (2, N'sample-b@outlook.jp'), (2, N'sample-b@gmail.com'), -- Bさんのメール2つ (3, N'sample-c@outlook.com'); -- Cさんのメール -- 確認 select * from [User] inner join Mail on [User].Id = Mail.UserId; /* Id Name Id UserId Address ----------- ---------- ----------- ----------- -------------------- 1 Aさん 1 1 sample-a@gmail.com 2 Bさん 2 2 sample-b@outlook.jp 2 Bさん 3 2 sample-b@gmail.com 3 Cさん 4 3 sample-c@outlook.com */
ではgmail.com
のアドレスを持つユーザを取得してみます。今回はクエリの結果として、UserだけでなくMailも読み込みたいということにしてIncludeメソッドも使っています。
using(var dbContext = new AppDbContext(_connectionString)) { var users = dbContext.Users .Include(user => user.Mails) .Where(user => user.Mails.Any(mail => mail.Address.EndsWith("@gmail.com"))) .ToList(); foreach (var user in users) { Console.WriteLine($"{user.Name}: {(string.Join(", ", user.Mails.Select(mail => mail.Address)))}"); } } // 結果 /* Aさん: sample-a@gmail.com Bさん: sample-b@outlook.jp, sample-b@gmail.com */
Includeメソッドについてはこのあたりを参考に。
- QueryableExtensions.Include(T, TProperty) メソッド (IQueryable(T), Expression(Func(T, TProperty))) (System.Data.Entity)
- Entity Framework Loading Related Entities
ログを確認すると次のクエリが実行されています。想像していたSELECT文とは違うかなと思いますが(SELECT文が入れ子になっているとか、OUTER JOINとか、取得するカラムとか)、FROM句の中にある内側のSELECT文だけを見てみるとWHERE句にEXITSが使われていることがわかります。
// クエリ(ログ) SELECT [Project2].[Id] AS [Id], [Project2].[Name] AS [Name], [Project2].[C1] AS [C1], [Project2].[Id1] AS [Id1], [Project2].[UserId] AS [UserId], [Project2].[Address] AS [Address] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent2].[Id] AS [Id1], [Extent2].[UserId] AS [UserId], [Extent2].[Address] AS [Address], CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM [dbo].[User] AS [Extent1] LEFT OUTER JOIN [dbo].[Mail] AS [Extent2] ON [Extent1].[Id] = [Extent2].[UserId] WHERE EXISTS (SELECT 1 AS [C1] FROM [dbo].[Mail] AS [Extent3] WHERE ([Extent1].[Id] = [Extent3].[UserId]) AND ([Extent3].[Address] LIKE N'%@gmail.com') ) ) AS [Project2] ORDER BY [Project2].[Id] ASC, [Project2].[C1] ASC
上ではIncludeメソッドを使ってMailを結果セットに含めていましたが、単にUserだけ取得したいときはIncludeメソッドを使わなくても大丈夫みたいです。MSDNの説明に書いてあるとおりなんですが、「Includeメソッドは結果セットに含めるものを指定する」だけの様子。Whereで使うためにはIncludeする必要があるのかと思い込んでましたが、どうも違うみたいです。
using (var dbContext = new AppDbContext(_connectionString)) { var users = dbContext.Users //.Include(user => user.Mails) .Where(user => user.Mails.Any(mail => mail.Address.EndsWith("@gmail.com"))) .ToList(); foreach (var user in users) { Console.WriteLine($"{user.Name}"); } } // 結果 /* Aさん Bさん */
またログを確認してみると、今度のクエリは想像していたSELECT文に近いかなと思います。
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name] FROM [dbo].[User] AS [Extent1] WHERE EXISTS (SELECT 1 AS [C1] FROM [dbo].[Mail] AS [Extent2] WHERE ([Extent1].[Id] = [Extent2].[UserId]) AND ([Extent2].[Address] LIKE N'%@gmail.com') )
とりあえずこんなところで。