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メソッドについてはこのあたりを参考に。

ログを確認すると次のクエリが実行されています。想像していた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')
    )

とりあえずこんなところで。