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

Wprowadzenie

Bazy danych SQL działają na 3-wartościowej logice (true, false), nullpodczas przeprowadzania porównań, w przeciwieństwie do logiki logicznej 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ą niepuste, więc kontrole wartości null nie są potrzebne. W drugim zapytaniu NullableInt może zawierać nullwartość , ale Id nie jest dopuszczana do wartości null; w null wyniku tego porównanie z wartościami niepustymi null , które byłyby filtrowane według WHERE operacji. 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 wartość null porównania Id <> NullableInt zwraca wartość null, która zostanie odfiltrowana według WHERE operacji. Jednak z punktu widzenia logiki logicznej 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 są dopuszczane do wartości 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)

Traktowanie wartości dopuszczanych do wartości null 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 bez wartości null jest prostsze i szybsze niż porównywanie kolumn dopuszczanych do wartości null. 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 negowanie == porównania jest rzeczywiście takie samo jak !=, więc nie powoduje poprawy wydajności.

• W niektórych przypadkach można uprościć złożone porównanie przez jawne odfiltrowanie null wartości z kolumny — na przykład wtedy, gdy żadne wartości nie null są obecne lub te wartości nie są istotne w 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 null wyniki są filtrowane jawnie z String1 kolumny. Program EF Core może bezpiecznie traktować kolumnę String1 jako niepustą podczas porównywania, co powoduje prostsze zapytanie.

Używanie semantyki relacyjnej wartości null

Można wyłączyć kompensację porównania wartości null i bezpośrednio użyć 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();

Ostrzeżenie

W przypadku korzystania z semantyki relacyjnej wartości null zapytania LINQ nie mają już takiego samego znaczenia, jak w języku C#, i mogą przynieść inne wyniki niż oczekiwano. Zachowaj ostrożność podczas korzystania z tego trybu.