Entity Frameworkの複合キーWHERE句

最近では、バージョン管理を使用してプロジェクトを開発するときに、複合キーのリストからアイテムを選択する問題に遭遇しました。

問題の説明：

バージョン管理の「条件」で開発する場合、プライマリキーテーブルはIdとリビジョンで構成されます。 送信された複合キーのリスト（Id、Revision）からデータベーステーブルから選択を取得する必要があります。 このようなSQLクエリは、次のようになります（リスト内の5つのアイテム）：

/*  1 */ select * from dbo.[Items] i where (i.Id = 1 and i.Revision = 2) OR (i.Id = 1 and i.Revision = 4) OR (i.Id = 3 and i.Revision = 3) OR (i.Id = 3 and i.Revision = 4) OR (i.Id = 5 and i.Revision = 9)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ただし、Entity Frameworkでは、複合キーのリストに対してこのようなクエリを作成することはできません。 標準的な手段で実行できる最大値は次のとおりです。

 context.Items.Where(i=> idList.Contains(i.Id) && revisionList.Contains(i.Revision))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そのような要求に変わるもの（概念的に）：

 /*  2 */ select * from dbo.[Items] i where i.Id in (1, 3, 5) and i.Revision in (2, 3, 4, 9)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Itemsテーブルに次の識別子を持つ要素がある場合、このクエリは誤った結果を返します。

Id = 3、Revision = 2

Id = 3、Revision = 4

そして、複合キーのリストには次のような行があります。

Id = 5、Revision = 4

Id = 3、Revision = 2



それではどうしますか？



インターネットでは、ORMの場合、次の方法が一般的です。

データベースとリストでIdとRevisionを組み合わせて、結果の値で比較する必要があります。 連結とは、文字列の連結を意味します。または、IdとRevisionがint型の場合、オフセットとlong型（bigint）の取得を意味します。

連結の場合：

 /*  3 */ select * from dbo.[Items] i where CONVERT(nvarchar(10), i.Id)+ ',' + CONVERT(nvarchar(10), i.Revision) in ('1,2', '1,4', '3,3', '3,4', '5,9')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

「リクエスト1」と「リクエスト3」を比較し、後者を実行するには、追加の列を構築する必要があります（さらに、構築するには、2つの型変換操作と2つの連結操作を実行する必要があります）。 また、「リクエスト1」では、比較演算のみが使用されます。 これに基づいて、「リクエスト1」の方が安いと思います。

ただし、MSSQL 2008 R2では、両方のクエリ（ここに示されている形式のクエリ）に対してまったく同じ実行プランが提供されます。

それでは、Entity Frameworkを取得して、「Request 1」と同じ形式でクエリを作成するにはどうすればよいですか。

Entity Frameworkを使用すると、複合キーの特定のセットに対するこのようなリクエストを作成できます。

 context.Items.Where(i=> (i.Id == 1 && i.Revision == 2) || (i.Id == 1 && i.Revision == 4) || (i.Id == 3 && i.Revision == 3) || (i.Id == 3 && i.Revision == 4) || (i.Id == 5 && i.Revision == 9))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

しかし、リストをどうするか？ 式を動的に生成します！ .NETではこれを行うことができます。 これを行うには、すべての場面でExpressionクラスを多数の静的メソッドとともに使用します。 次のように式を記述できます。

 i.Id == list.Id && i.Revision == list.Revision
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次に、必要な回数だけコピーし、list.Idおよびlist.Revisionの代わりに定数の形式でリストから必要な値を置き換えてから、たとえば||などの操作を通じて1つに収集します。



方法：

複合キーのリストがListであるとします。 Identifierは、Id、Revisionのペアのラッパーです。

Entity Frameworkでは、BinaryExpression型の式が使用される場合、各BinaryExpression式は3つの主要なフィールドで構成されます。

• 左（式を入力）-左側
• Right（type Expression）-正しい部分
• NodeType（type ExpressionType）-操作（OR、ANDなど）

list.Idおよびlist.Revisionも式であり、MemberExpression型のみです。 ReflectedTypeプロパティを使用して、どのタイプのリストがあるかを確認できます。識別子の場合、MemberExpression（list.Id）をConstantExpressionに置き換えることができます（特定のインスタンスのId値はReflectionまたはデリゲートを使用して取得できます）



その後、収集する必要がある式のリストを取得します。

最も簡単なオプションは、一度に1つずつ収集することです。

 BinaryExpression BuildExpression(ExpressionType type, List<BinaryExpression> expressions) { if(expressions.Count == 0) return null; if(expressions.Count == 1) return expressions[0]; var resExpression = expressions[0]; for (int i = 1; i < expressions.Count; i++) resExpression = Expression.MakeBinary(type, resExpression, expressions[i]); return resExpression; }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

しかし、重大な問題が1つあります。 右側で置換された各式は、左側の式全体と比較されます。 次の式がわかります。

 ((((((i.Id == 1 && i.Revision == 2) || (i.Id == 1 && i.Revision == 4)) || (i.Id == 3 && i.Revision == 3)) || (i.Id == 3 && i.Revision == 4)) || (i.Id == 5 && i.Revision == 9))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この式では、先頭の余分な括弧の数はリスト内の要素の数に等しくなります。 SQLクエリを構築するためにこの式を解析するとき、Entity Frameworkは再帰を使用して式をさらに深くし、リスト（実際の観測）に約1000個の要素があると、StackOverflowExceptionが飛びます。 ところで、私が自宅で使用しようとしたが、上記の問題のために拒否した非常に興味深いLINQ Dynamic Query Libraryプロジェクトは、同じ問題に苦しんでいます。



しかし、この問題は打ち負かすことができます！ これを行うには、右側の要素を置き換えずに式を構築し、バイナリツリーのように構築する必要があります。

 private static BinaryExpression BuildExpression(ExpressionType type, List<BinaryExpression> expressions) { if (expressions.Count == 0) return Expression.Equal(Expression.Constant(0), Expression.Constant(1)); //  ,        else if (expressions.Count == 1) return expressions[0]; var center = expressions.Count / 2; return Expression.MakeBinary(type, BuildExpression(type, expressions.Take(center).ToList()), BuildExpression(type, expressions.Skip(center).Take(expressions.Count - center).ToList())); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この方法で取得した式は、リストに1,000,000個のアイテムがあってもStackOverflowExceptionを発生させません（パラメーターが非常に多くても、SQL Serverは適切な時間にクエリを実行することを拒否するため、これ以上チェックしませんでした）。



これらすべてに基づいて、Whereメソッドをオーバーロードしてプロジェクトで使用する拡張メソッドを作成しました。



これらの開発を共有するために、EFCompoundkeyWhereのソースコードをアップロードしたコードプレックスでプロジェクトを作成しました



これが誰かに役立つことを願っています。