Вычисляемые колонки с EF Core и PostgreSQL

Фильтрация и сортировка в EF

Итак, мы работаем с PostgreSQL, а в качестве ORM используем Entity Framework Core. Предположим, у нас есть следующая сущность:

public class Person
{
    public int Id { get; init; }

    public string FirstName { get; set; }

    public string LastName { get; set; }
}

И задача отфильтровать и/или отсортировать вывод по полям FirstName и LastName.

Можно было бы хранить все в одном поле FullName, но нам не везде нужно полное имя.

Простейший вариант, который может первым прийти в голову - получить все записи и отсортировать в памяти:

var result = dbContext.Persons
    .Where(x => (x.FirstName + " " + x.LastName).Contains(pattern))
    .OrderBy(x => x.FirstName + " " + x.LastName)

Равносильный ему, но чуть более изящный, завести отдельное поле и работать уже с ним:

public string FullName => x.FirstName + " " + x.LastName

Минусы фильтрации в памяти очевидны, мы получаем все данные, а потом просто отбрасываем ненужные, то есть потенциально получаем слишком много лишних данных. Минус сортировки в памяти не столь серьезный, но все таки было бы здорово все делегировать нашему провайдеру БД. Специализированный код должен справиться лучше, чем код общего назначения.

Проекцию аналогичную той, что мы делали в памяти нашего приложения - объединение двух полей в новое - можно построить и в запросе, но начиная с 12й версии PostrgeSQL предлагает другой удобный инструмент.

GeneratedColumns

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

modelBuilder.Entity<Person>()
    .Property(x => x.FullName)
    .HasComputedColumnSql(@"""FirstName"" || ' ' || ""LastName""", stored: true);

Есть два типа генерируемых столбцов: сохранённые и виртуальные. Сохранённый генерируемый столбец вычисляется при записи и, стало быть, храниться в таблице. Виртуальный генерируемый столбец вычисляется при чтении. В настоящее время в PostgreSQL реализованы только сохранённые генерируемые столбцы. Так что это материализованное представление на уровне столбцов.

О том, что мы создаем сохраненный генерируемый столбец говорит параметр stored, без установки этого параметра в true приложение упадет на этапе создания схемы БД. Произвести запись в генерируемый столбец нельзя. Поэтому в командах INSERT или UPDATE нельзя задать значение для таких столбцов, хотя ключевое слово DEFAULT указать можно.

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

Immutable functions

Предположим, наши поля не являются обязательными и могут принимать значение NULL. Оператор || (конкатенация строк) принимая одним из параметров NULL вернет NULL. В тех строках, где одно из значений не заполнено мы получим FullName = NULL, а это не совсем то, чего мы пытаемся добиться. Можно поправить дело призвав на помощь еще один оператор:

COALESCE(string, '')

Оператор подставляет второй параметр вместо первого, если первый равен NULL.

Полное выражение будет:

modelBuilder.Entity<Person>()
   .Property(x => x.FullName)
   .HasComputedColumnSql(@"COALESCE(""FirstName"", '') || ' ' || COALESCE(""LastName"", '')", stored: true);

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

SELECT * FROM person;

Можно добавить функцию, убирающую пробелы вначале и в конце строки.

TRIM(string)

Но выражение становится все сложнее:

modelBuilder.Entity<Person>()
   .Property(x => x.FullName)
   .HasComputedColumnSql(@"TRIM(COALESCE(""FirstName"", '') || ' ' || COALESCE(""LastName"", ''))", stored: true);

А ведь есть замечательная функция, которая как раз решает нашу задачу:

CONCAT_WS(' ', arg1, arg2)

Эта функция соединяет все аргументы, кроме первого, через разделитель, игнорируя аргументы NULL. Разделитель указывается в первом аргументе.

modelBuilder.Entity<Person>()
                .Property(x => x.FullName)
                .HasComputedColumnSql(@"concat_ws(' ', ""FirstName"", ""LastName"")", stored: true);

Если запустим наше приложение, нас ждет неудача:

Npgsql.PostgresException: 42P17: генерирующее выражение не является постоянным

А дело в том, что функция CONCAT_WS может использовать форматирование строк, которое зависит от настроек текущего региона, поэтому она не считается постоянной и для генерации ее использовать нельзя.

Условные выражения

Можно было бы остановиться на функции TRIM, но что если наша модель данных выглядит так:

public class Person
{
    public int Id { get; init; }

    public string FirstName { get; set; }

    public string SecondName { get; set; }

    public string LastName { get; set; }
}

И нам надо собирать поле FullName уже из трех. Даже если для простоты предположить, что не обязательно только поле SecondName, функция TRIM нам уже не поможет, так как не убирает пробелы из середины строки.

В таком случае мы можем воспользоваться условными выражениями:

modelBuilder.Entity<Person>()
                .Property(x => x.FullName)
                .HasComputedColumnSql(@"CASE WHEN ""SecondName"" IS NULL THEN ""FirstName"" || ' ' || ""LastName""
                                             ELSE ""FirstName"" || ' ' || ""SecondName"" || ' ' || ""LastName"" END", stored: true);

Bonus

Есть интересная библиотека EntityFrameworkCore.Projectables, которая при помощи Source Generators создает выражения для свойств или методов. Она позволяет решить нашу задачу просто добавив аттрибут [Projactable].

[Projectable]
public string FullName => SecondName == null
    ? FirstName + " " + LastName
    : FirstName + " " + SecondName + " " + LastName;