Entity Framework: предварительно скомпилированный запрос для Enumerable.Contains

Предполагается, что Entity Framework 5+ прекомпилирует все запросы. Однако для запросов, таких как

List<Guid> ids;
var entities = context.MyEntities.Where(x => ids.Contains(x.Id)).ToArray();

Entity Framework не может предварительно скомпилировать запрос, и в зависимости от сложности всего запроса синтаксический разбор дерева выражений на SQL может потреблять несколько секунд. Кто-нибудь нашел обходное решение, чтобы получить предварительно скомпилированный запрос? Я не совсем понимаю, почему так сложно; конечно, это сложно сделать с параметрами, так как количество элементов может отличаться, но было бы достаточно, чтобы SQL выглядел как

SELECT a, b, c from MyEntities
WHERE c in __PLACEHOLDER__

а затем заменить местозаполнитель фактическими элементами списка. Конечно, это не так приятно, как прохождение параметров, но было бы намного лучше, чем ждать секунд для синтаксического разбора всего дерева выражений.

Ответы

Ответ 1

Вы должны сначала понять, как работает оператор IN в параметризованном SQL-запросе.

SELECT A FOM B WHERE C IN @p

не работает, параметр команды SQL не принимает значение ARRAY в качестве значения параметра, вместо этого запрос переводится в

SELECT A FROM B WHERE C IN (@p1, @p2, @p3 ... etc)

В этом запросе есть переменное количество параметров, и это причина, нет способа прекомпилировать этот запрос с помощью IEnumerable.Contains.

Единственной альтернативой (длинный длинный путь) является использование Xml или Json (входящий в Sql 2016).

Сохраните свой IEnumerable как xml.

[10,20,20,50] can be translated to
<data>
   <int value="10"/>
   <int value="20"/>
   <int value="20"/>
   <int value="50"/>
</data>

И затем вы можете определить VIEW с параметрами как

ВЫБЕРИТЕ А ОТ B ГДЕ ВХОД (SELECT INT FROM Xml (@P1))

И вы можете использовать этот вид, однако в EF есть больше проблем с тем, как запустить этот запрос, но этот запрос можно предварительно скомпилировать, поскольку он имеет только один параметр.

Пользовательский SQL для производительности Hack

Для довольно простых запросов, например,

List<Guid> ids;
var entities = context.MyEntities.Where(x => ids.Contains(x.Id)).ToArray();

Я мог бы просто использовать собственный SQL и fire,

var parameterList = ids.Select( 
   (x,i)=> new SqlCommandParameter(
      "@p"+i, x));

var pnames = String.Join(",", parameterList.Select(x=> x.ParameterName));

var entities = 
    context.SqlQuery<MyEntity>(
       "SELECT * FROM TABLE WHERE Id in (" + pnames + ")",
        parameterList.ToArray());

Временная таблица

Вы также можете использовать временную таблицу, но это увеличивает количество активных транзакций в вашей базе данных.

Guid sid = Guid.NewGuid();
foreach(var p in ids){
    db.TempIDs.Add(new TempID{ SID = sid, Value = p });
}
db.SaveChanges();

var qIDs = db.TempIDs.Where( x=> x.SID == sid );

var myEntities db.MyEntities.Where( x => qIDs.Any( q.Value == x.Id) );

// delete all TempIDs...
db.SqlQuery("DELETE FROM TempIDs WHERE [email protected],
     new SqlCommandParameter("@sid", sid));