Семантика значений NULL в запросах

Введение

При выполнении сравнений базы данных SQL работают с 3-значной логикой (true, false, null), в отличие от C#, где для этих целей применяется бинарная логика. При преобразовании запросов LINQ в SQL EF Core пытается компенсировать это различие, вводя дополнительные проверки значений NULL для некоторых элементов запроса. Чтобы продемонстрировать это, определим следующую сущность:

public class NullSemanticsEntity
{
    public int Id { get; set; }
    public int Int { get; set; }
    public int? NullableInt { get; set; }
    public string String1 { get; set; }
    public string String2 { get; set; }
}

и выполним несколько запросов.

var query1 = context.Entities.Where(e => e.Id == e.Int);
var query2 = context.Entities.Where(e => e.Id == e.NullableInt);
var query3 = context.Entities.Where(e => e.Id != e.NullableInt);
var query4 = context.Entities.Where(e => e.String1 == e.String2);
var query5 = context.Entities.Where(e => e.String1 != e.String2);

В результате первых двух запросов выполняются простые сравнения. В первом запросе оба столбца не допускают значения NULL, в связи с чем проверка значений NULL не требуется. Во втором запросе NullableInt может содержать значение null, а Id не допускает такие значения. При сравнении null с отличным от NULL значением получается результат null, который будет отфильтрован операцией WHERE. Соответственно, не нужны никакие дополнительные условия.

SELECT [e].[Id], [e].[Int], [e].[NullableInt], [e].[String1], [e].[String2]
FROM [Entities] AS [e]
WHERE [e].[Id] = [e].[Int]

SELECT [e].[Id], [e].[Int], [e].[NullableInt], [e].[String1], [e].[String2]
FROM [Entities] AS [e]
WHERE [e].[Id] = [e].[NullableInt]

Третий запрос приводит к выполнению проверки значений NULL. Если NullableInt имеет значение null, при сравнении Id <> NullableInt получается результат null, который будет отфильтрован операцией WHERE. Тем не менее с точки зрения бинарной логики этот вариант должен возвращаться в составе результата. Поэтому EF Core добавляет соответствующую проверку, чтобы убедиться в этом.

SELECT [e].[Id], [e].[Int], [e].[NullableInt], [e].[String1], [e].[String2]
FROM [Entities] AS [e]
WHERE ([e].[Id] <> [e].[NullableInt]) OR [e].[NullableInt] IS NULL

Запросы 4 и 5 демонстрируют ситуацию, в которой оба столбца допускают значения NULL. Следует отметить, что операция <> выдает более сложный (и потенциально более медленный) запрос по сравнению с операцией ==.

SELECT [e].[Id], [e].[Int], [e].[NullableInt], [e].[String1], [e].[String2]
FROM [Entities] AS [e]
WHERE ([e].[String1] = [e].[String2]) OR ([e].[String1] IS NULL AND [e].[String2] IS NULL)

SELECT [e].[Id], [e].[Int], [e].[NullableInt], [e].[String1], [e].[String2]
FROM [Entities] AS [e]
WHERE (([e].[String1] <> [e].[String2]) OR ([e].[String1] IS NULL OR [e].[String2] IS NULL)) AND ([e].[String1] IS NOT NULL OR [e].[String2] IS NOT NULL)

Обработка значений, допускающих значения NULL, в функциях

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

var query = context.Entities.Where(e => e.String1.Substring(0, e.String2.Length) == null);

Созданный код SQL выглядит следующим образом (нам не нужно рассчитывать функцию SUBSTRING, так как она будет иметь значение NULL, только если какой-либо из ее аргументов имеет значение NULL).

SELECT [e].[Id], [e].[Int], [e].[NullableInt], [e].[String1], [e].[String2]
FROM [Entities] AS [e]
WHERE [e].[String1] IS NULL OR [e].[String2] IS NULL

Для определяемых пользователем функций такая оптимизация также возможна. Дополнительные сведения см. на странице Сопоставление определяемых пользователем функций.

Написание эффективных запросов

• Сравнение столбцов, не допускающих значения NULL, выполняется проще и быстрее, чем сравнение столбцов, допускающих такие значения. Соответственно, во всех возможных случаях рекомендуется помечать столбцы как не допускающие значения NULL.

• Проверка равенства (==) выполняется проще и быстрее, чем проверка неравенства (!=), так как запросу не требуется различать результаты null и false. Во всех возможных случаях рекомендуется использовать сравнение на равенство. Обратите внимание, что простое отрицание сравнения == фактически аналогично сравнению != и не приводит к повышению производительности.

• В некоторых случаях можно упростить сложное сравнение, явно отфильтровав значения null из столбца, например если значения null отсутствуют или не являются значимыми для результата. Рассмотрим следующий пример:

var query1 = context.Entities.Where(e => e.String1 != e.String2 || e.String1.Length == e.String2.Length);
var query2 = context.Entities.Where(
    e => e.String1 != null && e.String2 != null && (e.String1 != e.String2 || e.String1.Length == e.String2.Length));

Эти запросы приведут к созданию следующего кода SQL.

SELECT [e].[Id], [e].[Int], [e].[NullableInt], [e].[String1], [e].[String2]
FROM [Entities] AS [e]
WHERE ((([e].[String1] <> [e].[String2]) OR ([e].[String1] IS NULL OR [e].[String2] IS NULL)) AND ([e].[String1] IS NOT NULL OR [e].[String2] IS NOT NULL)) OR ((CAST(LEN([e].[String1]) AS int) = CAST(LEN([e].[String2]) AS int)) OR ([e].[String1] IS NULL AND [e].[String2] IS NULL))

SELECT [e].[Id], [e].[Int], [e].[NullableInt], [e].[String1], [e].[String2]
FROM [Entities] AS [e]
WHERE ([e].[String1] IS NOT NULL AND [e].[String2] IS NOT NULL) AND (([e].[String1] <> [e].[String2]) OR (CAST(LEN([e].[String1]) AS int) = CAST(LEN([e].[String2]) AS int)))

Во втором запросе результаты null явно отфильтровываются из столбца String1. Во время сравнения EF Core может безопасно обрабатывать столбец String1 как не допускающий значения NULL, что приводит к выполнению более простого запроса.

Использование реляционной семантики значений NULL

При необходимости можно отключить компенсацию сравнения значений NULL и напрямую использовать реляционную семантику значений NULL. Это можно сделать, вызвав метод UseRelationalNulls(true) в построителе параметров в методе OnConfiguring.

new SqlServerDbContextOptionsBuilder(optionsBuilder).UseRelationalNulls();

Предупреждение

При использовании реляционной семантики значений NULL запросы LINQ будут иметь значение, отличное от C#, и могут возвращать не те результаты, которые ожидались. Используйте этот режим с осторожностью.