Entity Framework и ограничения DbCommand
На работе у нас есть некое подобие самописной ORM, работающей напрямую с ADO.NET и одна из проблем с которой мы сталкивались при её разработке - это то, что у количества параметров используемых в DbCommand есть лимит, этот лимит накладывается базой данных и у разных баз данных он разный.
Например, у PostgreSQL в каждом SQL statement (под SQL statement имеется в виду то, что в разговорной речи называют SQL запросом) может использоваться не больше 65535 параметров (в одну DbCommand можно отправить множество SQL statement и таким образом в общем DbCommand может содержать больше 65535 параметров).
В большинстве запросов довольно сложно преодолеть разрешённую планку, мы столкнулись с этим ограничением в двух случаях - в INSERT, когда за один запрос вставляется множество строк:
INSERT INTO table_name (column_namе(s)) values (value1, ...), (valueN, ...), ...
Если таблица содержит 10 столбцов и для каждого вставляемого значения мы будем использовать параметр, то с проблемой мы столкнёмся уже при попытке вставить за один раз 6554 строки.
И в запросах с использованием оператора IN:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
Здесь список, в котором ищутся значения (для каждого значения из списка используется параметр), должен превышать 65535 элементов.
Первым делом я конечно же полез смотреть как эту проблему решает и решает ли вообще Entity Framework.
Очень многое в поведении может определить конкретный дата-провайдер, я исследовал только связку Entity Framework + Npgsql, поэтому что-то из текста может быть неактуально для других дата-провайдеров
Зачем вообще использовать параметры
Основная причина в использовании параметров - это защита от SQL инъекций:
Command objects use parameters to pass values to SQL statements or stored procedures, providing type checking and validation. Unlike command text, parameter input is treated as a literal value, not as executable code. This helps guard against “SQL injection” attacks, in which an attacker inserts a command that compromises security on the server into an SQL statement.
OWASP, например, рекомендует всегда использовать параметризированные запросы:
- Use Parameterized SQL commands for all data access, without exception.
- Do not use SqlCommand with a string parameter made up of a concatenated SQL String.
Insert множества значений в Entity Framework
В Entity Framework эта проблема не встречается (во всяком случае при работе с PostgreSQL), потому что для каждой вставляемой строки генерируется свой INSERT (SQL statement), а для каждого SQL statement мы можем использовать 65535 параметров, соответственно, чтобы превысить разрешённый лимит нужна таблица с 65536 столбцами:
dbContext.Countries.Add(new Models.Country { Name = "Албания"});
dbContext.Countries.Add(new Models.Country { Name = "Словения"});
dbContext.SaveChanges();
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (7ms) [Parameters=[@p0='?', @p1='?'], CommandType='Text', CommandTimeout='30']
INSERT INTO "Countries" ("Name")
VALUES (@p0)
RETURNING "Id";
INSERT INTO "Countries" ("Name")
VALUES (@p1)
RETURNING "Id";
В общем-то в том числе и поэтому для массовой вставки Entity Framework не очень пригоден.
Кстати, по поводу массового INSERT в дотнете и PostgreSQL недавно был доклад у DotNetRu: Евгений Фирстов - PostgreSQL: Under Pressure
IN оператор в Entity Framework
IN оператор генерируется, например, при вызове LINQ метода Contains():
var countryNames = Enumerable.Range(0, 100).Select(item => item.ToString()).ToList();
dbContext.Countries.Where(c => countryNames.Contains(c.Name)).Select(c => c.Name).ToArray();
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT c."Name"
FROM "Countries" AS c
WHERE c."Name" IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96', '97', '98', '99')
Тут проблемы в Entity Framework тоже нет, просто потому что он вообще не использует параметры при формировании такого запроса, беря на себя риски с возможными SQL инъекциями.
Кстати, если интересно можете изучить в коде, как Entity Framework генерирует запросы (ссылки в конце)
Как мы решили проблему в своей ORM
Мы считаем параметры и при достижении лимита начинаем новый SQL statement.
Таймаут времени выполнения команды
Как мы выяснили, проблема с лимитом параметров не актуальна для Entity Framework, но у команды есть и другое ограничение, вероятность столкнуться с которым больше - это таймаут времени выполнения (часто путают его с Connection Timeout - но это разные вещи, Connection Timeout отвечает за таймаут установления соединения).
У команды есть лимит времени, отведённый на её выполнение, если команда выполняется дольше, то выполнение прерывается выбрасыванием исключения. Выше мы видели, что при вставке множества новых строк, Entity Framework объединят INSERT’ы в одну команду, таким образом потенциально общее время выполнения такой команды может быть большим. Точно также он поступает и в случае операций обновления и удаления и вообще он склонен все возможные операции проводимые на базе данных в один момент времени упаковывать в одну команду (при вызове SaveChanges, например).
В логах Entity Framework выше, можно увидеть что CommandTimeout для запросов он устанавливает в 30 секунд:
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (7ms) [Parameters=[@p0='?', @p1='?'], CommandType='Text', CommandTimeout='30']
Выбирает такое значение он, вероятно, потому что это значение по умолчанию для Npgsql.
Изменить его можно либо задав прямо в connection string подключения к базе данных, либо в коде
Дополнительные ссылки
- Генерация запросов чтения данных: Entity Framework QuerySqlGenerator, Npgsql QuerySqlGenerator
- Генерация запросов изменения данных Entity Framework UpdateSqlGenerator, Npgsql UpdateSqlGenerator
- Статья в которой исследуется ограничение параметров применительно к SQL Server: Playing with parameters limit on SQL Server with Entity Framework