EF查询PostgreSQL时LINQ表达式无法翻译问题求助

问题原因 #

EF Core（尤其是旧版本的Npgsql EF Core Provider）对可空整数数组的Any方法结合区间条件的SQL翻译支持不足，而WPF版本能正常运行是因为它基于LINQ to Objects（内存中直接过滤），无需翻译为数据库SQL。

可行解决方案 #

方案1：PostgreSQL专属数组重叠判断（推荐，性能最优）

利用PostgreSQL原生的数组重叠运算符&&，通过EF Core的EF.Functions.Overlaps方法实现，该写法会被直接翻译为高效的原生SQL：

// 生成目标区间的整数数组
var yearRange = Enumerable.Range(this.MinYear, this.MaxYear - this.MinYear + 1).ToArray();

var query = this.db.Titles
    .Where(m => m.Votes >= this.MinimumVotes)
    .Where(m => (decimal)m.Rating >= this.MinimumRating)
    .Where(m => EF.Functions.Overlaps(m.Years, yearRange));

方案2：原生SQL子查询

直接编写PostgreSQL原生SQL，通过unnest函数拆解数组并判断存在性，确保查询逻辑完全可执行：

var query = this.db.Titles
    .FromSqlRaw(@"
        SELECT t.* 
        FROM Titles t
        WHERE t.Votes >= {0}
          AND t.Rating >= {1}
          AND EXISTS (
              SELECT 1 
              FROM unnest(t.Years) AS year
              WHERE year >= {2} AND year <= {3}
          )
    ", this.MinimumVotes, this.MinimumRating, this.MinYear, this.MaxYear)
    .AsQueryable();

方案3：过滤空值后执行Any判断（适用于EF Core新版本）

如果使用较新版本的Npgsql.EntityFrameworkCore.PostgreSQL，可先过滤数组中的空值再执行区间判断，部分版本能正确翻译该逻辑：

var query = this.db.Titles
    .Where(m => m.Votes >= this.MinimumVotes)
    .Where(m => (decimal)m.Rating >= this.MinimumRating)
    .Where(m => m.Years
        .Where(year => year.HasValue)
        .Any(year => year.Value >= this.MinYear && year.Value <= this.MaxYear));

内容的提问来源于stack exchange，提问作者Andrew Scott