Entity Framework で IN句のサブクエリを使用する際の注意

概要

Entity FrameworkでIN句(Entity Framework では Contains)においてサブクエリを使用する場合、そのサブクエリが事前に実行される。その実行結果がメインのクエリのIN句においてリストとして用いられる。
そのため、実行結果の数が多いことが予想される場合は EXISTS句(Entity Framework では Any)を使用するべきである。

発生する問題の説明

次のテーブルを定義します。注文に対して複数の労働者が結びつきます。

CREATE TABLE Orders (
     OrderId int
)

CREATE TABLE Workers (
     WorkerId int
)

CREATE TABLE OrderWorkers (
    OrderId int,
    WorkerId int
)

これらのテーブルに対応する Entity Framework のクラスは次のようになる。

public class Order
{
    public int OrderId { get; set; }

    // Navigation property
    public ICollection<OrderWorker> OrderWorkers { get; set; }
}

public class Worker
{
    public int WorkerId { get; set; }

    // Navigation property
    public ICollection<OrderWorker> OrderWorkers { get; set; }
}

public class OrderWorker
{
    public int OrderId { get; set; }
    public int WorkerId { get; set; }

    // Navigation properties
    public Order Order { get; set; }
    public Worker Worker { get; set; }
}

これに対して指定した労働者と結びつく注文を抽出する Entity Framework のプログラムを書きます。

IN句を想定して次のように書きます。

var targetId = 1;

var subquery = context.OrderWorkers
    .Where(orderWorker => orderWorker.WorkerId = targetId)
    .Select(orderWorker => orderWorker.OrderId);

var query = context.Orders
    .Where(order => subquery.Contains(order.OrderId));

SQLは(1)のようになることを想定していますが、実際には(2)のようにサブクエリが事前に評価されて値が展開されます。サブクエリの実行結果行が非常に多い場合、実行計画を作成できず、エラーとなります。

-- (1)
SELECT * FROM Orders
WHERE
    Orders.OrderId IN (
        SELECT
	    OrderId
        FROM
	    OrderWorkers
	WHERE
	    WorkId = @targetId
    )

-- (2)
SELECT * FROM Orders
WHERE
    Orders.OrderId IN (1, 3, 4, 6, ..., 1000, 1001, ...)

次のエラーが発生した。

クエリ プロセッサの内部リソースの不足により、クエリ プランを作成できません。これはまれなイベントで、非常に複雑なクエリ、または非常に多数のテーブルまたはパーティションを参照するクエリに限り発生します。クエリを簡単にしてください。このメッセージがエラーにより表示されたと考えられる場合、詳細についてはご購入元に問い合わせてください。

解決策

IN句の実行結果が多いことが予想される場合は、Any を使用して、EXISTS句を作るクエリを書くのが良いです。

var targetId = 1;
var query = context.Orders
    .Where(
        order => context.OrderWorkers
	    .Any(orderWorker =>
	        orderWorker.WorkerId == targetId &&
		orderWorker.OrderId == order.OrderId
	    )
    );

次のクエリが生成されます。

SELECT * FROM Orders
WHERE
    EXISTS(
         SELECT
	     *
	 FROM
	     OrderWorkers
	 WHERE
	     OrderWorkers.WorkId = 1 AND
	     Orders.OrderId = OrderWorkers.OrderId
    )

あとがき

Entity Frameworkでエラーが発生し、生成されるクエリが複雑になったと思い調査した結果、上記で記載したようにIN句におびただしい数の値が入ったクエリが出力されて驚いた。