EF Core自定义映射PostgreSQL原生函数

背景

在 ASP.NET Core 应用开发中，使用 PostgreSQL的jsonb类型存储多语言数据是一种常见的方案。这种方式相比传统的多列存储或独立翻译表，在模式定义上更加灵活。
例如，对于一个包含多语言简介的“艺术家”实体，我们通常如下定义：

1. public class ArtistEntity
2. {
3.     public int Id { get; set; }
5.     // 使用 jsonb 存储多语言字典：Key=语言代码(en, zh-CN), Value=内容
6.     [Column(TypeName = "jsonb")]
7.     public Dictionary<string, string> Biography { get; set; } = [];
8. }

复制代码

性能考量

虽然存储方便，但在读取时会面临流量和性能问题。在大多数业务场景中，前端仅需要展示当前用户语言（如英语）的内容。如果直接查询实体，EF Core会将包含所有语言的JSONB对象完整加载到内存中。对于包含几十种语言的长文本字段，这不仅浪费数据库 I/O，也增加了网络传输开销。
尝试使用EF Core的字典索引器语法：

1. // 期望生成的 SQL 是直接取值
2. var bio = context.Artists.Select(x => x.Biography["en"]).FirstOrDefault();

复制代码

根据 Npgsql EF Core Provider 文档，虽然 Provider 提供了如EF.Functions.JsonContains、EF.Functions.JsonExists等丰富的 JSONB 操作函数，但在处理 Dictionary 索引器的投影翻译时仍存在局限性。在某些复杂的 Select 投影中，它可能无法生成最优的 ->> 操作符，或者导致查询在客户端求值。
目前Npgsql的EF.Functions中并没有直接对应jsonb_extract_path_text的方法，而这个原生函数恰恰是解决此类需求最直接的方式。它能在数据库服务端完成解析，仅返回指定路径的文本值。
什么是 jsonb_extract_path_text？

jsonb_extract_path_text是PostgreSQL的原生函数（等同于操作符 #>>），专门用于从 JSON 数据中根据路径提取文本。
相比于直接返回 JSON 对象，它能直接返回纯文本（text 类型），非常适合提取多语言字典中的单一语言值。
假设数据库里的 Biography 字段存储如下 JSON：

1. {
2.   "en": "Hello World",
3.   "zh-CN": "你好世界",
4.   "fr": "Bonjour le monde"
5. }

复制代码

如果我们只想获取中文简介：

1. -- 使用函数提取 'zh-CN' 键的值
2. SELECT jsonb_extract_path_text("Biography", 'zh-CN')
3. FROM "Artists";
5. -- 结果仅返回字符串： "你好世界"

复制代码

这种处理方式完全在数据库端完成，传输到应用层的只有这4个字符，而不是包含英文和法文的完整JSON 对象。
解决方案：映射自定义函数

为了在EF Core中使用jsonb_extract_path_text，我们可以通过自定义函数映射来实现。
什么是 EF Core自定义函数？

EF Core 的自定义函数映射（User-defined function mapping）允许开发者 C#方法直接映射到数据库中的 SQL 函数。在 LINQ 查询中使用这些被映射的 C# 方法时，EF Core不会在客户端执行它们，而是将它们“翻译”成对应的 SQL 片段发送给数据库执行。这就像是给了你一把钥匙，让你能够从 C# 代码中直接调用数据库特有的、强大的原生能力（如 PostgreSQL 的 JSON 处理、GIS 地理信息计算等），而无需编写原生的 SQL 字符串。
1. 定义函数存根

在 C# 中定义一个静态方法作为存根（Stub），用于告诉 EF Core 即使翻译 SQL。

1. public static class DbFunctionsExtensions
2. {
3.     // 此方法仅用于 EF Core 查询映射，客户端调用时抛出异常
4.     public static string JsonExtractPathText(this Dictionary<string, string> json, string key)
5.     {
6.         throw new NotSupportedException("此方法仅用于 EF Core 查询映射，不可在客户端执行。");
7.     }
8. }

复制代码

2. 配置模型映射

在 OnModelCreating 中通过 Fluent API 进行映射关系配置,  HasDbFunction 将其映射到数据库函数 jsonb_extract_path_text。

1. protected override void OnModelCreating(ModelBuilder modelBuilder)
2. {
3.     modelBuilder.HasDbFunction(typeof(DbFunctionsExtensions)
4.             .GetMethod(nameof(DbFunctionsExtensions.JsonExtractPathText),
5.                 [typeof(Dictionary<string, string>), typeof(string)])!)
6.         .HasName("jsonb_extract_path_text")
7.         .IsBuiltIn();
8. }

复制代码

或者，也可以使用 [DbFunction] 特性直接配置映射关系，这样代码更加紧凑。

1. using Microsoft.EntityFrameworkCore;
3. public static class DbFunctionsExtensions
4. {
5.     // 映射到 PostgreSQL 的内置函数 jsonb_extract_path_text
6.     [DbFunction("jsonb_extract_path_text", IsBuiltIn = true)]
7.     public static string JsonExtractPathText(this Dictionary<string, string> json, string key)
8.     {
9.        throw new NotSupportedException("此方法仅用于 EF Core 查询映射，不可在客户端执行。");
10.     }
11. }

复制代码

3. 使用示例

配置完成后，可以在 LINQ 查询中直接调用该扩展方法：

1. var query = db.Artists
2.     .Select(entity => new
3.     {
4.         Id = entity.Id,
5.         // 数据库仅返回当前语言的文本
6.         Bio = entity.Biography.JsonExtractPathText(CultureInfo.CurrentUICulture.Name)
7.     });

复制代码

生成的 SQL 将调用原生jsonb_extract_path_text函数，避免了全量传输 JSON 数据。

索引优化策略

针对 JSONB 字段的查询优化，需要根据具体需求选择合适的索引类型。

• GIN 索引：适用于“包含”类查询（如 Biography 是否包含 en 键）。
  

1. builder.Entity()
2.     .HasIndex(x => x.Biography)
3.     .HasMethod("gin");

复制代码

• 函数索引 (B-Tree)：如果业务中存在大量基于特定语言（如英文名称）的精确查找或排序需求，GIN 索引效率较低。此时应针对热点语言创建函数索引：
  

1. -- 针对英语内容建立 B-Tree 索引
2. CREATE INDEX idx_artist_bio_en ON "Artists" (( "Biography" ->> 'en' ));

复制代码

适用场景与局限性

虽然通过 jsonb_extract_path_text 可以减少网络传输，但这并不是所有场景下的万能解。

• 高并发读取：即便减少了传输量，解析 JSONB 在数据库层面依然有 CPU 开销。如果面临极高并发的读取请求（如首页热门列表），频繁让数据库解析 JSON 并不是最优解。在这种情况下，应当引入应用层缓存（如 Redis）或使用专门的搜索引擎（如 Elasticsearch）。
  
• 数据结构复杂度：此方法最适合扁平的 Key-Value 结构。如果 JSON 结构非常复杂且嵌套深，维护路径映射会变得困难。
  
• 数据库负载：将数据处理逻辑下推到数据库虽然方便，但会增加数据库 CPU 负载。在数据库资源已是瓶颈的系统中，需谨慎使用。
  

通过这种方式，我们在保持开发便利性的同时，通过利用数据库原生能力，解决了一个具体的性能问题。开发者应根据实际的数据量级和访问模式，决定是采用此直连方案还是引入更复杂的缓存架构。
参考链接

• https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping
  
• https://github.com/npgsql/efcore.pg/issues/2703
  

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！