Entity Framework 6 は NULL 可のカラムに対する条件で特に非効率なクエリを生成する

Entity Framework 6 で NULL 可のカラムに対しての条件を書くと、非常に効率の悪いクエリが生成される問題にはまって事故りかけたのでメモを残します。

テスト用に以下のようなテーブルとインデックスを作成しておきます。

CREATE TABLE [Product] (
    [Id] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    [Name] [nvarchar](50) NULL
)

CREATE UNIQUE INDEX [UX_Product_Name] ON [Product]([Name]) WHERE [Name] IS NOT NULL

このテーブルと Entity Framework 6 を使って、どのようなクエリになるか紹介します。

Entity Framework 6

単純にレコードを取得するだけのクエリを Entity Framework 6 で書くと以下のようなコードになります。

public class AppDbContext : DbContext
{
    public AppDbContext()
        : base(@"Server=.\SQLEXPRESS;Database=EFSample;Trusted_Connection=True;")
    {
    }

    public DbSet<Product> Products { get; set; }
}

[Table("Product")]
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        var context = new AppDbContext();

        var name = "kazuakix";

        var product = context.Products.FirstOrDefault(x => x.Name == name);
    }
}

実行された SQL を Profiler で確認すると、以下のようなクエリが実行されていました。

SELECT TOP (1) 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name]
FROM [dbo].[Product] AS [Extent1]
WHERE ([Extent1].[Name] = @p__linq__0) OR (([Extent1].[Name] IS NULL) AND (@p__linq__0 IS NULL))

FirstOrDefault では Name に対するフィルタを行うようにしていますが、Entity Framework 6 ではパラメータが NULL だった場合の処理が勝手に追加されています。

このクエリに対して実行プランを確認すると、当然のごとく Table Scan になっています。

パラメータが null じゃないことはクエリ生成前にわかるはずなので、どう考えても余計な処理です。

このﾌｧｯｷﾝな挙動を変更するためには UseDatabaseNullSemantics を有効にする必要があります。リファレンスにもどのような SQL が生成されるか書いてありました。

For example (operand1 == operand2) will be translated as: (operand1 = operand2) if UseDatabaseNullSemantics is true, respectively (((operand1 = operand2) AND (NOT (operand1 IS NULL OR operand2 IS NULL))) OR ((operand1 IS NULL) AND (operand2 IS NULL))) if UseDatabaseNullSemantics is false.

DbContextConfiguration.UseDatabaseNullSemantics Property (System.Data.Entity.Infrastructure) | Microsoft Learn

設定自体は DbContext のコンストラクタで行うだけなのでとても簡単です。

public class AppDbContext : DbContext
{
    public AppDbContext()
        : base(@"Server=.\SQLEXPRESS;Database=EFSample;Trusted_Connection=True;")
    {
        Configuration.UseDatabaseNullSemantics = true;
    }

    public DbSet<Product> Products { get; set; }
}

この設定を追加して同様に実行してみると、今度はまともな SQL がちゃんと生成されていました。

SELECT TOP (1) 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name]
FROM [dbo].[Product] AS [Extent1]
WHERE [Extent1].[Name] = @p__linq__0

これで実行プランを確認すると、ちゃんとインデックスが使われていることが確認できました。

これでめでたしめでたしと行けば良かったのですが、この設定を有効にしておくと以下のようにパラメータで null を扱うケースで問題となることがあります。

class Program
{
    static void Main(string[] args)
    {
        var context = new AppDbContext();

        var name = (string)null;

        var product = context.Products.FirstOrDefault(x => x.Name == name);
    }
}

Entity Framework 6 のクエリジェネレータはかなり頭が悪いので、この場合でも以下のようなクエリを出力します。この場合は NULL のデータが存在していたとしても、結果は 0 件になります。

SELECT TOP (1) 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name]
FROM [dbo].[Product] AS [Extent1]
WHERE [Extent1].[Name] = @p__linq__0

NULL の時には IS NULL を使わないといけないので、このようにパラメータに null が来る可能性がある場合には設定を有効にすると問題となります。回避策としては null をラムダ式で直接指定する方法になります。

試してみると LINQ 中で null を直接指定した場合には、正しく SQL が生成されるようになるみたいです。

class Program
{
    static void Main(string[] args)
    {
        var context = new AppDbContext();

        var product = context.Products.FirstOrDefault(x => x.Name == null);
    }
}

この場合はちゃんと IS NULL を使う形になるので、動作するようになります。正直いまいちですが。

ちなみに対象のプロパティに Required 属性が付いている場合には問題は発生しません

Entity Framework Core 1.0

.NET Framework 4.6.1 でも Entity Framework Core 1.0 は動作するので、同じようにコンソールアプリで試してみました。設定ファイル周りが変わったので、その分だけ互換性が失われてますがそれ以外は同じです。

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=.\SQLEXPRESS;Database=EFSample;Trusted_Connection=True;");
    }
}

[Table("Product")]
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        var context = new AppDbContext();

        var name = "kazuakix";

        var product = context.Products.FirstOrDefault(x => x.Name == name);
    }
}

実行して SQL Profiler で生成された SQL を確認すると、EF 6 で UseDatabaseNullSemantics を有効にしたときと同じものが生成されていました。と言っても普通のクエリですが。

SELECT TOP(1) [x].[Id], [x].[Name]
FROM [Product] AS [x]
WHERE [x].[Name] = @__name_0

実行プランは EF 6 の時と同じなので省略しますが、当然ながらちゃんとインデックスが使われます。

EF 6 で問題になったパラメータで NULL を渡したときの挙動も、EF Core 1.0 では正しく IS NULL を使うように修正が行われているようでした。

SELECT TOP(1) [x].[Id], [x].[Name]
FROM [Product] AS [x]
WHERE [x].[Name] IS NULL

EF Core 1.0 にも UseRelationalNulls という設定がありますが、具体的な挙動はよくわかりません。

今月末には EF Core 1.0 も RTM となるはずなので、既存の EF 6 を使っているコードを全て置き換えたい気持ちでいっぱいですね。Lazy Loading などを使っていない場合は簡単に対応できるはずです。