PostgreSQL имеет тип данных Jsonb, который позволяет добавлять к стандартной реляционной модели дополнительные свойства с возможностью поиска по ним.

EntityFramework Core с расширением Npgsql умеет вытягивать данные поля в тип System.String

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

Если вас это угнетает, так же как и меня, добро пожаловать под кат.

В конце статьи имеется ссылка на исходники!

Обозначим задачи

Я, как разработчик, хочу иметь инструмент для доступа к полям Jsonb с целью фильтрации и сортировки по ним, который:

• Будет совместим с EntityFramework Core 2 (мы используем именно его)
• Не потребует во время работы с ним самому писать SQL
• Будет работать с плоской структурой Json (внутри json есть только json свойства)

Добавлю еще, что есть Npgsql.Json.NET, который умеет проецировать Json и Jsonb значения в CLR типы. Если честно, не понимаю, для чего он может понадобиться, ведь раз у нас появилась необходимость в Json поле в реляционной БД, вероятнее всего у нас есть сущности с динамическим набором полей.

Алгоритм решения задачи

1. Определить метод (или методы), который будет покрывать наши потребности.
2. Создать транслятор, который будет участвовать в генерации SQL кода.
3. Прикрутить все это к Npgsql.

Решение

Для начала определим метод. Хочу, чтобы он использовался как то так:

context.Entity.Where(x => JsonbMethods.Value<string>(x.JsonbField, "jsonPropertyName") == "value")

Следовательно, вот наш метод:

public static TSource Value<TSource>(object jsonbProperty, string jsonbPropertyName)
{
    throw new NotSupportedException();
}

Я несколько часов ковырял исходники EF Core, Npgsql и не только в поисках способов расширить базовый функционал генерации SQL. Добрался вот до этой статьи, но подход автора по способу подключения транслятора мне не понравился, ведь он переопределяет стандартный инструмент, а значит может конфликтовать с другим похожим инструментом.
В итоге добрался до исходников Net Topology Suite. Все, что мне оттуда потребовалось, это способ подключения транслятора методов.

Но больше всего времени я потратил на то, чтобы сформировать нужный мне фрагмент sql.

Вот требуемый синтаксис

tableAlias."JsonField"->>"insideProperty"

Сначала я пробовал в трансляторе возвращать ColumnExpression. Первым параметром при его создании идет имя столбца (string). Я просто состряпал его из параметров, которые мне приходят в метод. Запустил, проверил, ошибка. Оказывается, то, что я передаю в качестве имени, оборачивается в кавычки. В итоге SQLполучился таким tableAlias.""JsonField"->>"insideProperty"".

В исходниках генератора я нашел метод VisitColumn, в котором это поведение было хардкодным и не зависело ни от каких параметров. То есть я не мог на это повлиять. Нужно было искать другое решение.

Тогда я создал собственный Expression — JsonbPropertyAccessorExpression: Expression

Осталось переопределить его метод Accept для ISqlExpressionVisitor.

Но вот беда, в данном интерфейсе нет метода, который бы мог сегнерировать кастомный оператор. Тогда мне пришла в голову мысль посетить не один метод, а несколько. Посетил сначала VisitColumn, который создал доступ к столбцу tableAlias.«JsonField», затем VisitSqlFragment, в который я прокинул "->>'insideFieldName'".

Я и не надеялся, но все заработало. Почти.

Когда я пытался фильтровать по тексту по точному совпадению почему то формировался такой фильтр tableAlias."JsonField"->>"insideProperty" = JSONB "value", что вызывало ошибку, так как привести текст к типу JSONB нельзя, если там не содержится валидный Json. Да и зачем мне что-то к чему-то приводить, когда я хочу текст?

Я было даже принял решение из маппинга модели убрать пометку со столбца Jsonb, что это Jsonb, добавив только эту пометку в MigrationContext, чтобы он генерировал правильные миграции. И это даже взлетело, но подход показался мне костыльным. Тем не менее на этом я и остановился.

После этого я принялся за CAST, ведь метод Value у меня универсальный и в Json свойствах могут быть различные типы данных, по которым тоже нужно сортировать и фильтровать.

В итоге из моего транслятора я стал возвращать ExplicitCastExpression, в который передавал свой кастомный Expression и тип, который содержался в универсальных аргументах метода Value.

когда я посмотрел на получившийся SQL при поиске по дате, я обнаружил, что к сравниваемое значение приводится к типу timestamp. timestamp 'some date value'. И тут до меня дошло. Предыдущая проблема, которую я решил костылем, ушла сама собой. Когда аксессор к полю Json кастился в текст, генератор больше не добавлял явное преобразование в JSONB, ведь слева операции сравнения уже был текст, а по умолчанию аксессор поля Jsonb возвращает тип Jsonb.

В завершении

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

Если кто-нибудь захочет расширить библиотеку в форках, пишите в личку, я постараюсь помочь. Ну или кидайте пулреквесты.

Вот ссылка на исходники