在ef core中使用postgres数据库的全文检索功能实战

时间:2021-01-02 01:24:37

起源

之前做的很多项目都使用solr/elasticsearch作为全文检索引擎,它们功能全面而强大,但是对于较小的项目而言,构建和维护成本显然过高,尤其是从关系数据库/文档数据库到全文检索引擎的数据同步工作非常繁琐,且容易出错。

记得很久以前就知道postgresql数据库内置全文检索,最近发现这个数据库越来越火,于是就又研究了一番,欣喜的发现居然支持ef core,于是对其进行了一些研究,并整理心得如下。

前提

本文假设读者熟悉entity framework core的基本概念和基本使用。

目的

建立dotnet core项目,使用postgres数据库和ef core,实现常见的全文检索功能,包括

  • 建立索引字段
  • 基本查询
  • 查询结果排名
  • 查询结果高亮显示

步骤1 - 新建项目并引入packages

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>netcoreapp3.1</TargetFramework>
</PropertyGroup> <ItemGroup>
<PackageReference Include="EFCore.NamingConventions" Version="1.1.0" />
<PackageReference Include="Microsoft.Extensions.Logging.Console" Version="3.1.4" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.1.3" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.1.3" />
</ItemGroup> </Project>

注意NamingConventions包是可选的,其作用是将表和字段名称翻译成蛇形,如MyData -> my_data,这样比较方便手写sql,不用写烦人的引号。

步骤2 - 建立model和dbcontext

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using NpgsqlTypes; public class Article
{
public int Id { get; set; } [Required]
[MaxLength()]
public string Title { get; set; } [MaxLength()]
public string Abst { get; set; } public NpgsqlTsVector TitleVector { get; set; }
public NpgsqlTsVector AbstVector { get; set; } [NotMapped]
public string TitleHL { get; set; } [NotMapped]
public string AbstHL { get; set; }
}

本model中的TitleVector和AbstVector分别用来存放Title和Abst字段的分词结果,便于后续的查询。不必担心代码会不小心改掉这些字段以至于查询出错,因为后续会设置一个触发器,每次更改数据的时候都会自动更新这些字段的内容。

using Microsoft.EntityFrameworkCore;

public class MyDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder
.UseNpgsql("Host=localhost;Database=ft;Username=postgres;Password=123456")
.UseLoggerFactory(PgFtSearch.Program.MyLoggerFactory)
.UseSnakeCaseNamingConvention(); protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder); modelBuilder.Entity<Article>().HasIndex(p => p.TitleVector).HasMethod("GIN");
modelBuilder.Entity<Article>().HasIndex(p => p.AbstVector).HasMethod("GIN");
} public DbSet<Article> Articles { get; set; }
}

首先UseNpgsql设置了要连接哪个数据库,然后UseLoggerFactory用来打印日志,主要是sql语句。MyLoggerFactory是怎么来的,参考后续的代码。

GIN的两行,用来告诉数据库这两个字段是采用倒排索引。

步骤3 - 生成migration并手动添加触发器

dotnet ef migrations add Init

然后,在生成的migration文件中手动添加触发器,在新增或者修改数据时,自动修改索引字段的内容,应用程序不必担心索引同步的问题。

migrationBuilder.Sql(
@"CREATE TRIGGER article_title_search_vector_update BEFORE INSERT OR UPDATE
ON articles FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(title_vector, 'pg_catalog.english', title);"); migrationBuilder.Sql(
@"CREATE TRIGGER article_abst_search_vector_update BEFORE INSERT OR UPDATE
ON articles FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(abst_vector, 'pg_catalog.english', abst);");

步骤4 - 编写程序

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging; namespace PgFtSearch
{
class Program
{
public static readonly ILoggerFactory MyLoggerFactory
= LoggerFactory.Create(builder => { builder.AddConsole(); }); static void Main(string[] args)
{
using (var db = new MyDbContext())
{
if (!db.Articles.Any())
{
var articles = new List<Article>{
new Article{Title="testing is ok", Abst="this is a test about postgre full text searching"},
new Article{Title="tested all bugs", Abst="there is no bug exists in this app"}
}; db.AddRange(articles);
db.SaveChanges();
} var query = "test"; var data = db.Articles
.Where(p => p.TitleVector.Matches(query) || p.AbstVector.Matches(query))
.OrderByDescending(p=>p.TitleVector.Rank(EF.Functions.ToTsQuery(query)) * 2.0 + p.AbstVector.Rank(EF.Functions.ToTsQuery(query)))
.Select(p=>new Article{
Title = p.Title,
Abst = p.Abst,
TitleHL = EF.Functions.ToTsQuery(query).GetResultHeadline(p.Title),
AbstHL = EF.Functions.ToTsQuery(query).GetResultHeadline(p.Abst),
}); foreach (var article in data)
{
Console.WriteLine($"{article.Title}\t{article.Abst}\t{article.TitleHL}\t{article.AbstHL}");
}
}
}
}
}

首先,如果没有数据,插入几条测试数据。

下面到了最关键的地方,编写数据查询的代码,实现的具体功能是:

  • 使用test关键字在title或abst字段中查询数据
  • 对查询结果进行排序,title字段排序权重=2.0,高于abst字段权重=1.0
  • 检索结果的title和abst进行高亮显示

最终生成的SQL如下:

SELECT 
  a.title AS "Title",
  a.abst AS "Abst",
  ts_headline(a.title, to_tsquery(@__query_0)) AS "TitleHL",
  ts_headline(a.abst, to_tsquery(@__query_0)) AS "AbstHL"
FROM articles AS a
WHERE (a.title_vector @@ plainto_tsquery(@__query_0)) OR (a.abst_vector @@ plainto_tsquery(@__query_0))
ORDER BY (ts_rank(a.title_vector, to_tsquery(@__query_0))::double precision * 2.0) + ts_rank(a.abst_vector, to_tsquery(@__query_0))::double precision DESC

代码在这儿,相信大家都能看懂,有问题欢迎交流。

总结

目前还未研究中文分词的支持情况,也没有测试性能。不过大致看来,完全可以在中小型项目中使用postgres数据库的内置全文检索功能替代solr/es等搜索引擎,减少系统的复杂程度,提升全文检索功能的稳定性。

在ef core中使用postgres数据库的全文检索功能实战的更多相关文章

  1. 在ef core中使用postgres数据库的全文检索功能实战之中文支持

    前言 有关通用的postgres数据库全文检索在ef core中的使用方法,参见我的上一篇文章. 本文实践了zhparser中文插件进行全文检索. 准备工作 安装插件,最方便的方法是直接使用安装好插件 ...

  2. EF Core中如何设置数据库表自己与自己的多对多关系

    本文的代码基于.NET Core 3.0和EF Core 3.0 有时候在数据库设计中,一个表自己会和自己是多对多关系. 在SQL Server数据库中,现在我们有Person表,代表一个人,建表语句 ...

  3. EF Core中,通过实体类向SQL Server数据库表中插入数据后,实体对象是如何得到数据库表中的默认值的

    我们使用EF Core的实体类向SQL Server数据库表中插入数据后,如果数据库表中有自增列或默认值列,那么EF Core的实体对象也会返回插入到数据库表中的默认值. 下面我们通过例子来展示,EF ...

  4. EF Core 中多次从数据库查询实体数据,DbContext跟踪实体的情况

    使用EF Core时,如果多次从数据库中查询一个表的同一行数据,DbContext中跟踪(track)的实体到底有几个呢?我们下面就分情况讨论下. 数据库 首先我们的数据库中有一个Person表,其建 ...

  5. EF Core中怎么实现自动更新实体的属性值到数据库

    我们在开发系统的时候,经常会遇到这种需求数据库表中的行被更新时需要自动更新某些列. 数据库 比如下面的Person表有一列UpdateTime,这列数据要求在行被更新后自动更新为系统的当前时间. Pe ...

  6. &lbrack;小技巧&rsqb;EF Core中如何获取上下文中操作过的实体

    原文地址:https://www.cnblogs.com/lwqlun/p/10576443.html 作者:Lamond Lu 源代码:https://github.com/lamondlu/EFC ...

  7. EF Core中避免贫血模型的三种行之有效的方法(翻译)

    Paul Hiles: 3 ways to avoid an anemic domain model in EF Core 1.引言 在使用ORM中(比如Entity Framework)贫血领域模型 ...

  8. EF Core中DbContext可以被Dispose多次

    我们知道,在EF Core中DbContext用完后要记得调用Dispose方法释放资源.但是其实DbContext可以多次调用Dispose方法,虽然只有第一次Dispose会起作用,但是DbCon ...

  9. 9&period;翻译系列:EF 6以及EF Core中的数据注解特性(EF 6 Code-First系列)

    原文地址:http://www.entityframeworktutorial.net/code-first/dataannotation-in-code-first.aspx EF 6 Code-F ...

随机推荐

  1. html 5 canvas画布整理

    1. 创建canvas画布<canvas id="myCanvas" width="800" height="800" >&lt ...

  2. tomcat 清理日志

    clear_log.sh #!/bin/bash #clear tomcat logs #log size (1M bytes),if lt, clear LOG_FILE_SIZE=1024000 ...

  3. Flume NG之Interceptor简介

    转载地址:http://www.cnblogs.com/lxf20061900/p/3658172.html 有的时候希望通过Flume将读取的文件再细分存储,比如讲source的数据按照业务类型分开 ...

  4. 文件夹添加右键DOS快捷入口

    1.自带的方法 win7: 按住shift键然后右键点击文件夹,菜单里会出现“在此处打开命令窗口”一项,其实就相当于在当前位置打开Dos窗口,这个是系统自带的. winxp: 打开“我的电脑”,点击菜 ...

  5. 百度富文本编辑器ueditor使用总结

    最近做的项目用到了ueditor这个东东,但是他的一些配置文档对初次使用者来说很难以理解,故作此总结 相关详细操作链接地址: http://blog.csdn.net/wusuopubupt/arti ...

  6. 【Spring MVC系列】--(5)理解AOP

    1.java反射 实用:JAVA反射机制及应用例子 http://407827531.iteye.com/blog/1173930 系统:java反射原理 http://china-jianchen. ...

  7. SD卡在单片机上的应用

    (1)SD卡的引脚定义:  SD卡SPI模式下与单片机的连接图: 注意:SPI模式时,这些信号需要在主机端用10~100K欧的电阻上拉.      SD卡支持两种总线方式:SD方式与SPI方式.    ...

  8. nginx添加需要代理的域名 配置

    dap.cns.360buy.com为需要nginx代理的域名,被代理的端口为:8089,配置需要在${nginx_home}/conf/nginx.conf的文件中加入如下配置即可 upstream ...

  9. mysql禁用autocommit&comma;以及遇到的问题(转)

    MySQL的autocommit(自动提交)默认是开启,其对mysql的性能有一定影响,举个例子来说,如果你插入了1000条数据,mysql会commit1000次的,如果我们把autocommit关 ...

  10. Left Menu抽屉效果ScrollView姿态共存冲突

    我们有一个小项目,需要做主页ScrollView嵌套TableView. 再就是Left与Right抽屉效果. 课前有眼似的,然后完成忘. 非常糟糕的记忆.真应了那句话:好记性不如烂博客. 由于我首页 ...