“WHERE” clause for composite keys in the Entity Framework
Most recently, when developing a project with versioning, I was faced with the problem of selecting elements according to the list of composite keys.
When developing “in conditions” of versionality, the primary key tables consist of Id and Revision. It is necessary to obtain a selection from the database table by the transmitted list of composite keys (Id, Revision). Such an SQL query would look like this (for five items in the list):
But the Entity Framework does not allow you to write such a request for a list of composite keys. The maximum that can be done by standard means is:
what will turn into such a request (conceptually):
This query will produce incorrect results if the Items table has items with such identifiers:
Id = 3, Revision = 2
Id = 3, Revision = 4
And in the list of composite keys there are such lines:
Id = 5, Revision = 4
Id = 3, Revision = 2
So how to be?
On the Internet, for ORM, the following method is distributed:
It is necessary to combine Id and Revision in the database and in the list and compare the resulting value. The union is the concatenation of strings, or, if Id and Revision are of type int, then the offset and the type of long (bigint).
For the case of concatenation:
If we compare “Request 1” and “Request 3”, then to perform the latter, we need to build an additional column (and to build it, we need to perform 2 type conversion operations and 2 concatenation operations). And in “Request 1” only comparison operations are used. Based on this, I assume that "Request 1" is cheaper.
But MSSQL 2008 R2 provides for both requests absolutely identical Execution Plan (for requests in the form in which they are presented here).
')
Entity Framework allows you to write such a request for a specific set of composite keys:
But what to do with the list? Generate Expression Dynamically! .NET allows you to do this. To do this, use the Expression class with a bunch of static methods for all occasions. We can write the expression as follows:
Then copy it as many times as necessary, substituting the necessary values from the list in the form of constants instead of list.Id and list.Revision and then gather them into one through an operation, for example, ||
How to do it:
Suppose our list of composite keys is a List. Where Identifier is a wrapper for a pair of Id, Revision.
In the Entity Framework where used expressions of type BinaryExpression, each expression of BinaryExpression consists of 3 main fields:
list.Id and list.Revision are also expressions, only of type MemberExpression. Through its ReflectedType property, you can find out what type of list we have, and if it is a Identifier, then we can replace MemberExpression (list.Id) with ConstantExpression (the Id value for a specific instance can be obtained via Reflection or using a delegate)
After that, we will have a list of expressions that need to be assembled into one.
The easiest option is to collect them alternately:
But there is one serious problem. Each expression substituted on the right is compared with the entire expression on the left, i.e. the following expression is obtained:
In this expression, the number of additional brackets at the beginning will be equal to the number of elements in the list. And if nothing else, then when parsing this expression to build an SQL query, the Entity Framework uses recursion to go deep into the expression, and, with ~ 1000 items in the list (practical observations), a StackOverflowException is thrown. By the way, the very interesting LINQ Dynamic Query Library project that I was trying to use in my own home suffers from the same problem, but refused because of the above problem.
But this problem can be defeated! To do this, you need to build an expression without substituting elements on the right, and building it as a binary tree:
The expression obtained in this way does not cause a StackOverflowException even with 1,000,000 items in the list (no longer checked, because already with so many parameters SQL Server refuses to execute the query in adequate time).
On the basis of all this, I made extension methods that overload the Where method and use it in my projects.
To share these insights, I created a project on codeplex, where I put the source code for EFCompoundkeyWhere
I hope it will be useful to someone.
Description of the problem:
When developing “in conditions” of versionality, the primary key tables consist of Id and Revision. It is necessary to obtain a selection from the database table by the transmitted list of composite keys (Id, Revision). Such an SQL query would look like this (for five items in the list):
/* 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) But the Entity Framework does not allow you to write such a request for a list of composite keys. The maximum that can be done by standard means is:
context.Items.Where(i=> idList.Contains(i.Id) && revisionList.Contains(i.Revision)) what will turn into such a request (conceptually):
/* 2 */ select * from dbo.[Items] i where i.Id in (1, 3, 5) and i.Revision in (2, 3, 4, 9) This query will produce incorrect results if the Items table has items with such identifiers:
Id = 3, Revision = 2
Id = 3, Revision = 4
And in the list of composite keys there are such lines:
Id = 5, Revision = 4
Id = 3, Revision = 2
So how to be?
On the Internet, for ORM, the following method is distributed:
It is necessary to combine Id and Revision in the database and in the list and compare the resulting value. The union is the concatenation of strings, or, if Id and Revision are of type int, then the offset and the type of long (bigint).
For the case of concatenation:
/* 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') If we compare “Request 1” and “Request 3”, then to perform the latter, we need to build an additional column (and to build it, we need to perform 2 type conversion operations and 2 concatenation operations). And in “Request 1” only comparison operations are used. Based on this, I assume that "Request 1" is cheaper.
But MSSQL 2008 R2 provides for both requests absolutely identical Execution Plan (for requests in the form in which they are presented here).
')
So, how to force the Entity Framework to make a request in the same form as "Request 1".
Entity Framework allows you to write such a request for a specific set of composite keys:
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)) But what to do with the list? Generate Expression Dynamically! .NET allows you to do this. To do this, use the Expression class with a bunch of static methods for all occasions. We can write the expression as follows:
i.Id == list.Id && i.Revision == list.Revision Then copy it as many times as necessary, substituting the necessary values from the list in the form of constants instead of list.Id and list.Revision and then gather them into one through an operation, for example, ||
How to do it:
Suppose our list of composite keys is a List. Where Identifier is a wrapper for a pair of Id, Revision.
In the Entity Framework where used expressions of type BinaryExpression, each expression of BinaryExpression consists of 3 main fields:
- Left (Expression type) - left side
- Right (type Expression) - the right side
- NodeType (type ExpressionType) - operation (OR, AND, etc.)
list.Id and list.Revision are also expressions, only of type MemberExpression. Through its ReflectedType property, you can find out what type of list we have, and if it is a Identifier, then we can replace MemberExpression (list.Id) with ConstantExpression (the Id value for a specific instance can be obtained via Reflection or using a delegate)
After that, we will have a list of expressions that need to be assembled into one.
The easiest option is to collect them alternately:
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; } But there is one serious problem. Each expression substituted on the right is compared with the entire expression on the left, i.e. the following expression is obtained:
((((((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)) In this expression, the number of additional brackets at the beginning will be equal to the number of elements in the list. And if nothing else, then when parsing this expression to build an SQL query, the Entity Framework uses recursion to go deep into the expression, and, with ~ 1000 items in the list (practical observations), a StackOverflowException is thrown. By the way, the very interesting LINQ Dynamic Query Library project that I was trying to use in my own home suffers from the same problem, but refused because of the above problem.
But this problem can be defeated! To do this, you need to build an expression without substituting elements on the right, and building it as a binary tree:
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())); } The expression obtained in this way does not cause a StackOverflowException even with 1,000,000 items in the list (no longer checked, because already with so many parameters SQL Server refuses to execute the query in adequate time).
On the basis of all this, I made extension methods that overload the Where method and use it in my projects.
To share these insights, I created a project on codeplex, where I put the source code for EFCompoundkeyWhere
I hope it will be useful to someone.
Source: https://habr.com/ru/post/152417/
All Articles