Full Text Search
PostgreSQL has built-in support for full-text search, which allows you to conveniently and efficiently query natural language documents.
Mapping
PostgreSQL full text search types are mapped onto .NET types built-in to Npgsql. The tsvector type is mapped to NpgsqlTsVector and tsquery is mapped to NpgsqlTsQuery. This means you can use properties of type NpgsqlTsVector directly in your model to create tsvector columns. The NpgsqlTsQuery type on the other hand, is used in LINQ queries.
public class Product
{
public int Id { get; set; }
public string Title { get; set; }
public string Description { get; set; }
public NpgsqlTsVector SearchVector { get; set; }
}
Setting up and querying a full text search index on an entity
As the PostgreSQL documentation explains, full-text search requires an index to run efficiently. This section will show two ways to do this, each having its benefits and drawbacks. Please read the PostgreSQL docs for more information on the two different approaches.
Method 1: tsvector column
This method adds a tsvector column to your table, that is automatically updated when the row is modified. First, add an NpgsqlTsVector property to your entity:
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public NpgsqlTsVector SearchVector { get; set; }
}
Then, configure the property to be a stored generated column: the provider has an API for setting that up:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Product>()
.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)
}
Note that HasGeneratedTsVectorColumn() is simply a bit of sugar of EF's standard HasComputedColumnSql(); if there's anything you need which isn't covered by HasGeneratedTsVectorColumn(), simply use HasComputedColumnSql().
Once your generated tsvector column is set up, any inserts or updates on the Products table will update the SearchVector column and maintain it automatically. You can query it as follows:
var context = new ProductDbContext();
var npgsql = context.Products
.Where(p => p.SearchVector.Matches("Npgsql"))
.ToList();
Method 2: Expression index
If you prefer to have an expression index rather than a generated column, use the following API to do so:
modelBuilder.Entity<Blog>()
.HasIndex(b => new { b.Title, b.Description })
.HasMethod("GIN")
.IsTsVectorExpressionIndex("english");
Once the index is created on the Title and Description columns, you can query as follows:
var context = new ProductDbContext();
var npgsql = context.Products
.Where(p => EF.Functions.ToTsVector("english", p.Title + " " + p.Description)
.Matches("Npgsql"))
.ToList();
Computed column over JSON columns
The provider can also create computed tsvector columns over JSON columns. Simply use HasGeneratedTsVectorColumn() as shown above, and when applied to JSON columns, the provider will automatically generate json_to_tsvector/jsonb_to_tsvector as appropriate.
Note that this will pass the filter all to these functions, meaning that all values in the JSON document will be included. To customize the filter - or to create the computed column on older versions of the provider - simply specify the function yourself via HasComputedColumnSql.
Operation translation
Almost all PostgreSQL full text search functions can be called through LINQ queries. All supported EF Core LINQ methods are defined in extension classes in the Microsoft.EntityFrameworkCore namespace, so simply referencing the Npgsql provider will light up these methods. The following table lists all supported operations; if an operation you need is missing, please open an issue to request for it.
| .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) |