PostgreSQL中带ANY运算符的SQL如何转换为正确LINQ语句

问题根因 #

直接调用x.Collaborators.Contains(userId)抛出错误，是因为Npgsql EF Core提供器默认将Enumerable.Contains映射为PostgreSQL的数组包含运算符@>，该运算符要求两侧操作数均为数组类型。当类型推导存在偏差时，会生成不符合预期的SQL结构，与目标SQL'User1'=ANY(collaborators)的转译逻辑不匹配。

可行实现方案 #

方案1：使用原生Any转译（推荐） #

Npgsql为PostgreSQL的ANY数组语法提供了开箱即用的LINQ转译支持，无需额外配置，直接在数组属性上调用带相等判断的Any方法即可：

1. 确保引入必要命名空间：

   using Microsoft.EntityFrameworkCore;
   using Npgsql.EntityFrameworkCore.PostgreSQL;
   

2. 编写LINQ查询：

   var userId = "User1";
   var result = context.Projects
       .Where(p => p.Collaborators.Any( collaborator => collaborator == userId ))
       .ToList();
   

该写法会被精确转译为目标SQL：

SELECT * FROM "schema_test".project WHERE @__userId = ANY("collaborators")

无类型兼容问题，执行性能与原生SQL完全一致。

方案2：修正类型映射后使用Contains #

如果更习惯Contains的写法，只需要在实体配置中明确数组字段的数据库类型，修正Npgsql的类型推导逻辑即可：
在DbContext的OnModelCreating方法中添加字段配置：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Project>(entity =>
    {
        entity.Property(p => p.Collaborators)
            .HasColumnType("varchar[]"); // 显式指定为PG端可变长字符串数组类型
    });
}

配置完成后，原有context.Projects.Where(x => x.Collaborators.Contains(userId))的写法即可正常执行，不会抛出数组类型比较错误。

方案3：自定义函数映射（兼容旧版本） #

如果使用的Npgsql版本较低，不支持上述两种转译逻辑，可以手动映射ANY运算符对应的数据库函数：

1. 在DbContext中添加自定义函数声明与转译配置：

   // 自定义扩展方法，用于LINQ调用
   public static class PgFunctionExtensions
   {
       public static bool InArray<T>(this DbFunctions functions, T value, T[] array)
           => throw new NotSupportedException("该方法仅用于LINQ转译，不可在内存中直接调用");
   }
   
   public class YourDbContext : DbContext
   {
       public DbSet<Project> Projects { get; set; }
   
       protected override void OnModelCreating(ModelBuilder modelBuilder)
       {
           // 配置自定义函数的SQL转译规则
           modelBuilder.HasDbFunction(
               typeof(PgFunctionExtensions).GetMethod(nameof(PgFunctionExtensions.InArray))!
           ).HasTranslation(arguments =>
           {
               var sqlFactory = (ISqlExpressionFactory)this.GetService(typeof(ISqlExpressionFactory))!;
               // 生成 value = ANY(array) 的SQL结构
               return sqlFactory.Any(arguments[1], arguments[0]);
           });
       }
   }
   

2. 调用方式如下：

   var userId = "User1";
   var result = context.Projects
       .Where(p => EF.Functions.InArray(userId, p.Collaborators))
       .ToList();
   

该方案可以强制生成目标ANY语法，完全绕开默认Contains的转译逻辑，适配所有Npgsql版本。

内容的提问来源于stack exchange，提问作者Rakshitha Vasishta