Полнотекстовый поиск в PostgreSQL используя Npgsql

PostgreSQL имеет встроенную поддержку полнотекстового поиска , что позволяет удобно и эффективно запрашивать документы на естественном языке.

Отображение

Типы полнотекстового поиска PostgreSQL сопоставляются с типами .NET, встроенными в Npgsql. Тип tsvectorсопоставляется NpgsqlTsVectorи tsqueryсопоставляется с NpgsqlTsQuery. Это означает, что вы можете использовать свойства типа NpgsqlTsVectorнепосредственно в своей модели для создания tsvectorстолбцов. Тип NpgsqlTsQuery, с другой стороны, используется в запросах LINQ.

public class Product
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public NpgsqlTsVector SearchVector { get; set; }
}

 

Настройка и запрос индекса полнотекстового поиска для объекта

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

 

Способ 1: столбец tsvector

Этот метод добавляет tsvectorстолбец в вашу таблицу, который автоматически обновляется при изменении строки. Сначала добавьте NpgsqlTsVectorсвойство к вашей сущности:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public NpgsqlTsVector SearchVector { get; set; }
}

Настройка столбца для автоматического обновления зависит от вашей версии PostgreSQL. В PostgreSQL 12 и выше столбец может быть простым сгенерированным столбцом , а версия 5.0.0 содержит сахар для его настройки. В предыдущих версиях необходимо вручную настроить триггеры базы данных, которые вместо этого обновляют столбец.

• PostgreSQL 12+
• Старые версии

 

ПРИМЕЧАНИЕ

Приведенное ниже работает только с PostgreSQL 12 и версией 5.0.0 поставщика EF Core.

Следующее создаст сгенерированный tsvectorстолбец, по которому вы можете легко создать индекс:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity()
        .HasGeneratedTsVectorColumn(
            p => p.SearchVector,
            "english",  // Text search config
            p => new { p.Name, p.Description })  // Included properties
        .HasIndex(p => p.SearchVector)
        .HasMethod("GIN"); // Index method on the search vector (GIN or GIST)
}

Как только ваш автоматически обновляемый tsvectorстолбец настроен, любые вставки или обновления в Productsтаблице теперь будут обновлять SearchVectorстолбец и поддерживать его автоматически. Вы можете запросить его следующим образом:

var context = new ProductDbContext();
var npgsql = context.Products
    .Where(p => p.SearchVector.Matches("Npgsql"))
    .ToList();

 

Способ 2: Индекс выражения

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

• Версия 5.0.0
• Старые версии

modelBuilder.Entity()
    .HasIndex(b => new { b.Title, b.Description })
    .HasMethod("GIN")
    .IsTsVectorExpressionIndex("english");

После создания индекса для столбцов Titleи Descriptionвы можете выполнить запрос следующим образом:

var context = new ProductDbContext();
var npgsql = context.Products
    .Where(p => EF.Functions.ToTsVector("english", p.Title + " " + p.Description)
        .Matches("Npgsql"))
    .ToList();

 

Вычисляемый столбец по столбцам JSON

Начиная с версии 7.0 поставщик также может создавать вычисляемые tsvectorстолбцы поверх столбцов JSON. Просто используйте HasGeneratedTsVectorColumn(), как показано выше, и при применении к столбцам JSON поставщик автоматически сгенерирует json_to_tsvector/jsonb_to_tsvectorсоответствующие данные.

Обратите внимание, что это передаст фильтр allэтим функциям, а это означает, что все значения в документе JSON будут включены. Чтобы настроить фильтр или создать вычисляемый столбец в более старых версиях провайдера, просто укажите функцию самостоятельно через HasComputedColumnSql.

 

Перевод операции

Почти все функции полнотекстового поиска PostgreSQL можно вызывать с помощью запросов LINQ. Все поддерживаемые методы EF Core LINQ определены в классах расширения в Microsoft.EntityFrameworkCoreпространстве имен, поэтому простое обращение к поставщику Npgsql подсветит эти методы. В следующей таблице перечислены все поддерживаемые операции; если нужная вам операция отсутствует, пожалуйста, откройте вопрос, чтобы запросить ее.

.NET	SQL
EF.Functions.ToTsVector(string)	to_tsvector(string)
EF.Functions.ToTsVector("english", string)	to_tsvector('english'::regconfig, string)
EF.Functions.ToTsQuery(string))	to_tsquery(string)
EF.Functions.ToTsQuery("english", string )	to_tsquery('english'::regconfig, string)
EF.Functions.PlainToTsQuery(string)	plainto_tsquery(string)
EF.Functions.PlainToTsQuery("english", string)	plainto_tsquery('english'::regconfig, string)
EF.Functions.PhraseToTsQuery(string)	phraseto_tsquery(string)
EF.Functions.PhraseToTsQuery("english", string)	phraseto_tsquery('english'::regconfig, string)
EF.Functions.WebSearchToTsQuery(string)	websearch_to_tsquery(string)
EF.Functions.WebSearchToTsQuery("english", string)	websearch_to_tsquery('english'::regconfig, string)
EF.functions.ArrayToTsVector(new[] { "a", "b" })	array_to_tsvector(ARRAY['a', 'b'])
NpgsqlTsVector.Parse(string)	CAST(string AS tsvector)
NpgsqlTsQuery.Parse(string)	CAST(queryString AS tsquery)
tsvector.Matches(string)	tsvector @@ plainto_tsquery(string)
tsvector.Matches(tsquery)	tsvector @@ tsquery
tsquery1.And(tsquery2)	tsquery1 && tsquery2
tsquery1.Or(tsquery2)	tsquery1 || tsquery2
tsquery.ToNegative()	!! tsquery
tsquery1.Contains(tsquery2)	tsquery1 @> tsquery2
tsquery1.IscontainedIn(tsquery2)	tsquery1 <@ tsquery2
tsquery.GetNodeCount()	numnode(query)
tsquery.GetQueryTree()	querytree(query)
tsquery.GetResultHeadline("a b c")	ts_headline('a b c', query)
tsquery.GetResultHeadline("a b c", "MinWords=1, MaxWords=2")	ts_headline('a b c', query, 'MinWords=1, MaxWords=2')
tsquery.Rewrite(targetQuery, substituteQuery)	ts_rewrite(to_tsquery(tsquery), to_tsquery(targetQuery), to_tsquery(substituteQuery))
tsquery1.ToPhrase(tsquery2)	tsquery_phrase(tsquery1, tsquery2)
tsquery1.ToPhrase(tsquery2, distance)	tsquery_phrase(tsquery1, tsquery2, distance)
tsvector1.Concat(tsvector2)	tsvector1 || tsvector2
tsvector.Delete("x")	ts_delete(tsvector, 'x')
tsvector.Delete(new[] { "x", "y" })	ts_delete(tsvector, ARRAY['x', 'y'])
tsvector.Filter(new[] { "x", "y" })	ts_filter(tsvector, ARRAY['x', 'y'])
tsvector.GetLength()	length(tsvector)
tsvector.Rank(tsquery)	ts_rank(tsvector, tsquery)
tsvector.RankCoverDensity(tsquery)	ts_rank_cd(tsvector, tsquery)
tsvector.SetWeight(NpgsqlTsVector.Lexeme.Weight.A)	setweight(tsvector, 'A')
tsvector.ToStripped()	strip(tsvector)
EF.Functions.Unaccent(string)	unaccent(string)
EF.Functions.Unaccent(regdictionary, string)	unaccent(regdictionary, string)