Wykonywanie zapytań względem semantyki o wartości null

Introduction

Bazy danych SQL działają na trójwartościowej logice (true, false, null) podczas przeprowadzania porównań, w przeciwieństwie do logiki języka C#. Podczas tłumaczenia zapytań LINQ na język SQL program EF Core próbuje zrekompensować różnicę, wprowadzając dodatkowe kontrole wartości null dla niektórych elementów zapytania. Aby to zilustrować, zdefiniujmy następującą jednostkę:

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; }
}

i wydaj kilka zapytań:

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);

Pierwsze dwa zapytania tworzą proste porównania. W pierwszym zapytaniu obie kolumny są niemogące zawierać wartości null, więc sprawdzanie wartości null nie jest potrzebne. W drugim zapytaniu NullableInt może zawierać wartość null, ale Id nie może być wartością null; porównanie null z wartością niepustą daje null jako wynik, który zostanie odfiltrowany przez operację WHERE. Dlatego nie są potrzebne żadne dodatkowe terminy.

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]

Trzecie zapytanie wprowadza sprawdzanie wartości null. Gdy NullableInt jest null, porównanie Id <> NullableInt daje wynik null, który zostanie odfiltrowany przez operację WHERE. Jednak z punktu widzenia logiki boolowskiej ten przypadek powinien zostać zwrócony w ramach wyniku. W związku z tym program EF Core dodaje niezbędne sprawdzenie, aby to upewnić.

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

Zapytania cztery i pięć pokazują wzorzec, gdy obie kolumny mogą przyjmować wartość null. Warto zauważyć, że <> operacja generuje bardziej skomplikowane (i potencjalnie wolniejsze) zapytanie niż == operacja.

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)

Przetwarzanie wartości nullable w funkcjach

Wiele funkcji w języku SQL może zwracać null wynik tylko wtedy, gdy niektóre z ich argumentów to null. Platforma EF Core wykorzystuje to do tworzenia bardziej wydajnych zapytań. Poniższe zapytanie ilustruje optymalizację:

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

Wygenerowany kod SQL jest następujący (nie musimy oceniać SUBSTRING funkcji, ponieważ będzie ona mieć wartość null tylko wtedy, gdy jeden z argumentów ma wartość 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

Optymalizację można również używać w przypadku funkcji zdefiniowanych przez użytkownika. Aby uzyskać więcej informacji, zobacz stronę mapowania funkcji zdefiniowanych przez użytkownika .

Pisanie wydajnych zapytań

• Porównywanie kolumn nieakceptujących wartości null jest prostsze i szybsze niż porównywanie kolumn nullable. Jeśli to możliwe, rozważ oznaczenie kolumn jako niepustych.

• Sprawdzanie równości (==) jest prostsze i szybsze niż sprawdzanie pod kątem braku równości (!=), ponieważ zapytanie nie musi rozróżniać wyników null i false . Używaj porównania równości, jeśli jest to możliwe. Jednak po prostu zanegowanie == porównania jest w istocie tożsame jak !=, więc nie powoduje poprawy wydajności.

• W niektórych przypadkach można uprościć złożone porównanie, wyraźnie odfiltrowując wartości null z kolumny — na przykład wtedy, gdy wartości null nie są obecne lub gdy nie mają one znaczenia dla wyniku. Rozważmy następujący przykład:

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));

Te zapytania tworzą następujący kod 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)))

W drugim zapytaniu wyniki null są filtrowane w sposób jawny z kolumny String1. Program EF Core może bezpiecznie traktować kolumnę String1 jako niepustą podczas porównywania, co powoduje prostsze zapytanie.

Używanie relacyjnej semantyki null

Można wyłączyć kompensację porównania wartości null i użyć bezpośrednio semantyki relacyjnej wartości null. Można to zrobić, wywołując UseRelationalNulls(true) metodę w konstruktorze opcji wewnątrz OnConfiguring metody:

new SqlServerDbContextOptionsBuilder(optionsBuilder).UseRelationalNulls();