Метод Contains колекций в Entity Framework для SQL Server25.12.2024 14:46

Долгое время в метод Contains(), который используется в Entity Framework для фильтрации данных по списку значений, не вносили изменения. До выхода Entity Framework 8 коллекция с этим методом внутри оператора запросов where LINQ транслировалась в значения в виде констант внутри оператора IN для SQL. Если количество элементов равно одному, то преобразование происходило в выражение с оператором ‘=’ на стороне MS SQL Server. Аналогично транслируется и метод расширения Where() LINQ.

Использование в EF8 функции OPENJSON устраняет часть проблем с кэшем планов запросов для SQL Server, но не применимо к старым версиям (compatibility level) баз данных. Да и оптимальность генерируемого ею кода в некоторых случаях вызывает сомнения.

В недавно вышедшем Entity Framework 9 добавили больше настроек для возможности транслирования метода Contains() коллекций как с помощью OPENJSON, так и «по-старому» — в виде констант. Можно включить режим генерации кода совместимый со старыми версиями SQL Server, аналогичный версии EF7.

Преобразование запросов в EF7 и ранее

Несколько значений:

var ids = new[] { 1, 2, 3, 4, };
var employees = await dbcontext.Set()
    .Where(e => ids.Contains(e.Id));
    .ToListAsync();

SELECT [e].[Id], [e].[BirthDate], [e].[DepartmentId], [e].[IsActive], [e].[Name]
FROM [Employees] AS [e]
WHERE [e].[Id] IN (1, 2, 3, 4)

Одно значение:

var ids = new[] { 1, };
var employees = await dbcontext.Set()
    .Where(e => ids.Contains(e.Id));
    .ToListAsync();

SELECT [e].[Id], [e].[BirthDate], [e].[DepartmentId], [e].[IsActive], [e].[Name]
FROM [Employees] AS [e]
WHERE [e].[Id] = 1

Аналогично происходит транслирование LINQ операторов и инлайн массива в SQL.

var query = 
    from e in dbcontext.Set()
    where new[] { 1, 2, 3, 4, }.Contains(e.Id)
    select e;
var employees = await query.ToListAsync();

Можно ещё отметить преобразование запросов с группировкой данных. Так использование метода расширения GroupBy() вместе с Where() может транслироваться в GROUP BY и HAVING для SQL.

var departmentsIds = new[] { 1, 3, };
var query = dbcontext.Set()
    .GroupBy(g => g.DepartmentId)
    .Select(e => new { Id = e.Key, Count = e.Count() })                
    .Where(x => departmentsIds.Contains(x.Id))
    .Select(e => new { e.Id, e.Count });
var employeesInDepartments = await query.ToListAsync();

SELECT [e].[DepartmentId] AS [Id], COUNT(*) AS [Count]
FROM [Employees] AS [e]
GROUP BY [e].[DepartmentId]
HAVING [e].[DepartmentId] IN (1, 3)

Преобразование запросов в EF8 и EF9

Чрезмерное использование констант оказывает пагубное влияние на MS SQL Server. Хэш запроса отличается для каждого набора переданных значений. SQL-серверу необходимо рассчитать план запроса для каждого нового варианта, что занимает процессорное время. Созданным планам запросов отводится запись в кэше планов SQL Server, что может вызывать вытеснение других запросов. Борьба за ресурсы угрожает постоянными пересчётами планов запросов.

Entity Framework имеет свой внутренний кэш для запросов, а использование констант в SQL приводит к дополнительным накладным расходам и делает кэширование неэффективным.

В Entity Framework 8 стали использовать функцию SQL Server OPENJSON для обработки списка значений в виде массива JSON. Этот массив передаётся как простой строковый параметр SQL (nvarchar(4000) или nvarchar(max)). Так решается проблема кэширования SQL.

Но сам процесс транслирования может работать немного медленнее, чем старое преобразование в список IN. К сожалению, функция OPENJSON недоступна в SQL Server 2014 и ниже.

exec sp_executesql N'SELECT [e].[Id], [e].[BirthDate], [e].[DepartmentId], [e].[IsActive], [e].[Name]
FROM [Employees] AS [e]
WHERE [e].[Id] IN (
    SELECT [i].[value]
    FROM OPENJSON(@__ids_0) WITH ([value] int ''$'') AS [i]
)',N'@__ids_0 nvarchar(4000)',@__ids_0=N'[1,2,3,4]'

EF8 для SQL Server 2014 и ниже

Функция OPENJSON была добавлена в SQL Server 2016. Для её использования уровень совместимости базы данных должен быть не ниже 130. Проверить можно с помощью следующей команды:

SELECT name, compatibility_level FROM sys.databases; 

Вызов OPENJSON для SQL Server 2014 и ниже, а также с уровнем совместимости 120 и ниже, закончится ошибкой такого вида:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '$'.

Если по какой-то причине нельзя изменить уровень совместимости базы данных, то можно настроить EF для генерации SQL, соответствующего нужной версии.

protected override void OnConfiguring(
    DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
        .UseSqlServer(@"", o => o.UseCompatibilityLevel(120));

Where () и Contains () и EF9

В Entity Framework 9 также возможно настроить работу со старыми версиями SQL Server с помощью UseCompatibilityLevel(). Ещё в EF9 добавили метод TranslateParameterizedCollectionsToConstants(), который настраивает транслирование Contains() для коллекций в константы внутри IN «по старому»:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseSqlServer(
        "",
        o => o.TranslateParameterizedCollectionsToConstants());

Можно заставить EF преобразовать определённую коллекцию в определённом запросе в константы, обернув переменную коллекции в Constant(T). Это переопределяет поведение по умолчанию:

var ids = new[] { 1, 2, 3, 4, };
var employees = await dbcontext.Set()
    .Where(e => EF.Constant(ids).Contains(e.Id))
    .ToListAsync();

SELECT [e].[Id], [e].[BirthDate], [e].[DepartmentId], [e].[IsActive], [e].[Name]
FROM [Employees] AS [e]
WHERE [e].[Id] IN (1, 2, 3, 4)

Аналогично можно принудительно преобразовать коллекцию в отдельном запросе в один параметр, обернув переменную коллекции в Parameter(T). Это заставит проигнорировать настройку TranslateParameterizedCollectionsToConstants():

var ids = new[] { 1, 2, 3, 4, };
var employees = await dbcontext.Set()
    .Where(e => EF.Parameter(ids).Contains(e.Id))
    .ToListAsync();

exec sp_executesql N'SELECT [e].[Id], [e].[BirthDate], [e].[DepartmentId], [e].[IsActive], [e].[Name]
FROM [Employees] AS [e]
WHERE [e].[Id] IN (
    SELECT [i].[value]
    FROM OPENJSON(@__ids_0) WITH ([value] int ''$'') AS [i]
)',N'@__ids_0 nvarchar(4000)',@__ids_0=N'[1,2,3,4]'

Планы запросов

Используя функцию OPENJSON удалось добиться прогресса с кэшированием планов запросов SQL. Это важно для повторных вызовов с такими же или другими параметрами.

Но вот сами планы запросов не всегда выглядят оптимальными. В EF8 и EF9 даже одно значение в коллекции преобразуется в SQL с помощью OPENJSON.

var ids = new[] { 1, };
var employees = await dbcontext.Set()
    .Where(e => ids.Contains(e.Id));
    .ToListAsync();

exec sp_executesql N'SELECT [e].[Id], [e].[BirthDate], [e].[DepartmentId], [e].[IsActive], [e].[Name]
FROM [Employees] AS [e]
WHERE [e].[Id] IN (
    SELECT [i].[value]
    FROM OPENJSON(@__ids_0) WITH ([value] int ''$'') AS [i]
)',N'@__ids_0 nvarchar(4000)',@__ids_0=N'[1]'

План запроса для OPENJSON

Есть большая вероятность уйти в INDEX SCAN там, где запросы без OPENJSON стабильно приводят к INDEX SEEK.

План запроса для EF7

План запроса для одного параметра

Использование INDEX SCAN для OPENJSON в примере для одной записи может оправдать маленькое количество тестовых записей в таблице. Если таблица будет содержать большое число строк, то план запроса поменяется, в том числе и для выборки по нескольким Id.

План запроса для OPENJSON большой таблицы

Преобразование Contain () в условия OR

До сих пор не потеряли актуальность самописные методы, которые транслируют Contains() внутри Where() в набор условий OR для SQL. Упрощенный результат работы такого метода для массива из трёх элементов выглядит так:

exec sp_executesql
N'SELECT *
FROM Employees
WHERE Id = @v1 OR Id = @v2 OR Id = @v3',
N'@v1 int,@v2 int,@v3 int',
@v1=1,@v2=2,@v3=3

Есть варианты принимающие на вход коллекцию, а для уникальных значений лучше сразу передать HashSet.

  var ids = new[] { 1, 2, 3, 4, 5, 6, 7, };
  var employees = await dbcontext.Set().In(
      [.. ids], x => x.Id).ToListAsync();

exec sp_executesql N'SELECT [e].[Id], [e].[BirthDate], [e].[DepartmentId], [e].[IsActive], [e].[Name]
FROM [Employees] AS [e]
WHERE [e].[Id] = @__v1_0 OR [e].[Id] = @__v2_1 OR [e].[Id] = @__v1_2 OR [e].[Id] = @__v1_3 OR [e].[Id] = @__v2_4 OR [e].[Id] = @__v1_5 OR [e].[Id] = @__v2_6 OR [e].[Id] = @__v1_7 OR [e].[Id] = @__v1_8 OR [e].[Id] = @__v2_9',N'@__v1_0 int,@__v2_1 int,@__v1_2 int,@__v1_3 int,@__v2_4 int,@__v1_5 int,@__v2_6 int,@__v1_7 int,@__v1_8 int,@__v2_9 int',
@__v1_0=1,@__v2_1=2,@__v1_2=3,@__v1_3=4,@__v2_4=5,@__v1_5=6,@__v2_6=7,@__v1_7=7,@__v1_8=7,@__v2_9=7

У этого решения есть хорошие шансы на использование индексов. Создание ограниченного набора корзин (бакетов) для разного количества значений защищает SQL Server от захламления планами запросов.

В примере выше количество элементов попало между размерами корзин 6 и 10. Поэтому для последнего Id были созданы ещё три переменные, чтобы не создавался новый план запроса для семи параметров. Всего для 7 их получилось четыре:

@__v2_6=7,@__v1_7=7,@__v1_8=7,@__v2_9=7

Есть у такого решения минусы. Текст SQL запроса для крупных коллекций значительно больше, чем с OPENJSON или обычным условием IN. В MS SQL существует ограничение на количество параметров в одном запросе — нельзя передавать больше 2100. Но это можно обойти, выкачивая данные несколькими запросами.

var ids = new[] { 1, 2, 3, 4, 5, 6, 7, };
var employees = await dbcontext.Set().ToListByPortionsAsync(
    [.. ids], x => x.Id, portionSize: 4);

exec sp_executesql N'SELECT [e].[Id], [e].[BirthDate], [e].[DepartmentId], [e].[IsActive], [e].[Name]
FROM [Employees] AS [e]
WHERE [e].[Id] = @__v1_0 OR [e].[Id] = @__v2_1 OR [e].[Id] = @__v1_2 OR [e].[Id] = @__v2_3',N'@__v1_0 int,@__v2_1 int,@__v1_2 int,@__v2_3 int',@__v1_0=1,@__v2_1=2,@__v1_2=3,@__v2_3=4
go
exec sp_executesql N'SELECT [e].[Id], [e].[BirthDate], [e].[DepartmentId], [e].[IsActive], [e].[Name]
FROM [Employees] AS [e]
WHERE [e].[Id] = @__v1_0 OR [e].[Id] = @__v2_1 OR [e].[Id] = @__v1_2',N'@__v1_0 int,@__v2_1 int,@__v1_2 int',@__v1_0=5,@__v2_1=6,@__v1_2=7
go

Размеры корзин лучше задавать в соответствии со сценариями использования, например, с количеством строк на странице в UI пользователя.

Исходный код и тесты для примеров этой статьи:

Ссылки на статьи, которые использованы для подготовки данного материала:

© Habrahabr.ru