Совсем недавно при разработке проекта с версионностью, я столкнулся с проблемой выборки элементов по списку составных ключей.
При разработке «в условиях» версионности primary key таблиц состоит из Id и Revision. Нужно получить выборку из таблицы БД по передаваемому списку составных ключей (Id, Revision). Такой SQL запрос выглядел бы так (для пяти элементов в списке):
Но Entity Framework не позволяет написать такой запрос для списка составных ключей. Максимум что можно сделать стандартными средствами это:
что превратится в такой запрос (концептуально):
Этот запрос будет выдавать неверные результаты, если таблице 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).
Для случая с конкатенацией:
Если сравнивать «Запрос 1» и «Запрос 3», то для выполнения последнего нужно строить дополнительный столбец (причем для его построения нужно провести 2 операции преобразования типа и 2 операции конкатенации). А в «Запрос 1» используются только операции сравнения. Исходя из этого, я предполагаю что «Запрос 1» дешевле.
Но MSSQL 2008 R2 выдает по обоим запросам абсолютно одинаковый Execution Plan (для запросов в том виде, в котором они представлены здесь).
Entity Framework позволяет написать такой запрос для определенного набора составных ключей:
Но что делать со списком? Сформировать Expression динамически! .NET позволяет это сделать. Для этого используется класс Expression с кучей статических методов на все случаи жизни. Мы можем записать выражение следующим образом:
Затем скопировать его необходимое количество раз, подставляя вместо list.Id и list.Revision необходимые значения из списка в виде констант и потом собрать их в одно через операцию, например, ||.
Как это сделать:
Предположим, что наш список составных ключей это List. Где Identifier – это обертка для пары Id, Revision.
В Entity Framework в Where используются выражения типа BinaryExpression, каждое выражение BinaryExpression состоит из 3х основных полей:
list.Id и list.Revision – это тоже выражения, только типа MemberExpression. Через его свойство ReflectedType можно узнать, какого типа у нас list, и если это Identifier, то мы можем заменить MemberExpression (list.Id) на ConstantExpression (значение Id для конкретного экземпляра – можно получить через Reflection или с помощью делегата)
После этого у нас получится список выражений, которые нужно будет собрать в одно.
Самый простой вариант – это собирать их поочередно:
Но возникает одна серьезная проблема. Каждое выражений, подставляемое справа сравнивается со всем выражением слева, т.е. получается вот такое выражение:
В этом выражении количество дополнительных скобок в начале, будет равно количеству элементов в списке. И все бы ничего, то при разборе этого выражения, для построения SQL запроса, Entity Framework использует рекурсию для прохода вглубь выражения, и, при ~1000 элементах в списке (практические наблюдения) вылетает StackOverflowException. Кстати этой же проблемой страдает довольно интересный проект LINQ Dynamic Query Library который я пытался использовать у себя, но отказался из-за вышеуказанной проблемы.
Но эту проблему можно победить! Для этого нужно строить выражение не подставляя элементы справа, и строя его как бинарное дерево:
Выражение, полученное этим способом, не вызывает StackOverflowException даже при 1 000 000 элементов в списке (больше не проверял, так как уже при таком количестве параметров SQL Server отказывается выполнять запрос за адекватное время).
На основе всего этого я сделал extension методы, перегружающие метод Where и пользуюсь им в своих проектах.
Чтобы поделиться этими наработками я создал проект на codeplex, куда выложил исходники EFCompoundkeyWhere
Надеюсь это кому-нибудь пригодится.
Описание проблемы:
При разработке «в условиях» версионности primary key таблиц состоит из Id и Revision. Нужно получить выборку из таблицы БД по передаваемому списку составных ключей (Id, Revision). Такой SQL запрос выглядел бы так (для пяти элементов в списке):
/* Запрос 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 выдает по обоим запросам абсолютно одинаковый Execution Plan (для запросов в том виде, в котором они представлены здесь).
Итак, как же заставить Entity Framework составить запрос в таком же виде как «Запрос 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))
Но что делать со списком? Сформировать Expression динамически! .NET позволяет это сделать. Для этого используется класс Expression с кучей статических методов на все случаи жизни. Мы можем записать выражение следующим образом:
i.Id == list.Id && i.Revision == list.Revision
Затем скопировать его необходимое количество раз, подставляя вместо list.Id и list.Revision необходимые значения из списка в виде констант и потом собрать их в одно через операцию, например, ||.
Как это сделать:
Предположим, что наш список составных ключей это List. Где Identifier – это обертка для пары Id, Revision.
В Entity Framework в Where используются выражения типа BinaryExpression, каждое выражение BinaryExpression состоит из 3х основных полей:
- Left (тип Expression) — левая часть
- Right (тип Expression) — правая часть
- NodeType (тип ExpressionType) — операция (OR, AND, и т.д.)
list.Id и list.Revision – это тоже выражения, только типа MemberExpression. Через его свойство ReflectedType можно узнать, какого типа у нас list, и если это Identifier, то мы можем заменить MemberExpression (list.Id) на ConstantExpression (значение Id для конкретного экземпляра – можно получить через Reflection или с помощью делегата)
После этого у нас получится список выражений, которые нужно будет собрать в одно.
Самый простой вариант – это собирать их поочередно:
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;
}
Но возникает одна серьезная проблема. Каждое выражений, подставляемое справа сравнивается со всем выражением слева, т.е. получается вот такое выражение:
((((((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()));
}
Выражение, полученное этим способом, не вызывает StackOverflowException даже при 1 000 000 элементов в списке (больше не проверял, так как уже при таком количестве параметров SQL Server отказывается выполнять запрос за адекватное время).
На основе всего этого я сделал extension методы, перегружающие метод Where и пользуюсь им в своих проектах.
Чтобы поделиться этими наработками я создал проект на codeplex, куда выложил исходники EFCompoundkeyWhere
Надеюсь это кому-нибудь пригодится.