Метод Contains коллекций в Entity Framework для SQL Server

Долгое время в метод 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<Employee>()     .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<Employee>()     .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<Employee>()     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<Employee>()     .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(@"<CONNECTION STRING>", 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(         "<CONNECTION STRING>",         o => o.TranslateParameterizedCollectionsToConstants());

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

var ids = new[] { 1, 2, 3, 4, }; var employees = await dbcontext.Set<Employee>()     .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>(T). Это заставит проигнорировать настройку TranslateParameterizedCollectionsToConstants():

var ids = new[] { 1, 2, 3, 4, }; var employees = await dbcontext.Set<Employee>()     .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<Employee>()     .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]'

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

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

Преобразование 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<Employee>().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<Employee>().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 пользователя.

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

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

• Критические изменения в EF Core 8.0

• EF Core 8 Preview 4: Primitive collections and improved Contains

• Contains to OPENJSON translation regresses performance #32394

• IQueryableExtensions.cs