如何在EF Core LINQ查询中追加ZQL原生SQL字符串

1. 自定义IQueryable扩展方法 #

EF Core原生没有支持直接传入原生SQL片段的Where重载，我们可以通过扩展方法实现该能力，依赖EF Core 5.0新增的ToQueryString方法获取当前LINQ查询生成的SQL，再拼接自定义条件：

using System;
using System.Linq;
using System.Runtime.CompilerServices;
using Microsoft.EntityFrameworkCore;

public static class CustomQueryExtensions
{
    /// <summary>
    /// 为IQueryable追加原生SQL Where条件
    /// </summary>
    public static IQueryable<T> Where<T>(this IQueryable<T> source, string rawSqlCondition)
    {
        if (source == null) throw new ArgumentNullException(nameof(source));
        if (string.IsNullOrWhiteSpace(rawSqlCondition)) return source;

        // 基于当前查询SQL嵌套一层拼接自定义条件
        var currentQuerySql = source.ToQueryString();
        return source.FromSqlRaw($"SELECT * FROM ({currentQuerySql}) AS inner_q WHERE {rawSqlCondition}");
    }

    /// <summary>
    /// 支持参数化的重载，避免SQL注入风险
    /// </summary>
    public static IQueryable<T> Where<T>(this IQueryable<T> source, FormattableString rawSqlCondition)
    {
        if (source == null) throw new ArgumentNullException(nameof(source));
        if (rawSqlCondition == null) return source;

        var currentQuerySql = source.ToQueryString();
        var fullQuery = FormattableStringFactory.Create(
            $"SELECT * FROM ({currentQuerySql}) AS inner_q WHERE {rawSqlCondition.Format}", 
            rawSqlCondition.GetArguments()
        );
        return source.FromSqlInterpolated(fullQuery);
    }
}

2. 对接ZomboDb全文检索 #

你只需要把ZomboDb的==>检索语法作为原生条件传入扩展方法即可，假设你的Customer表对应的ZomboDb检索字段为full_text_index，调用方式如下：

// 固定检索词调用
var result = Customers
    .Where(x => x.Age >= 18)
    .Where("full_text_index ==> 'Vasya, Ilya'")
    .ToList();

// 动态检索词参数化调用（推荐，避免SQL注入）
string searchKeywords = "Vasya, Ilya";
var safeResult = Customers
    .Where(x => x.Age >= 18)
    .Where($"full_text_index ==> {searchKeywords}")
    .ToList();

注意事项 #

• 需要确保你的PostgreSQL数据库已经正确安装启用ZomboDb扩展，且对应业务表已经创建ZomboDb全文索引
• 如果你封装的仓储实例返回的是自定义的集合类型而非EF Core的IQueryable，需要调整仓储实现返回IQueryable才能使用该扩展方法
• 该实现仅兼容EF Core 5.0及以上版本，低版本EF Core不支持ToQueryString方法，需要额外实现SQL获取逻辑

内容的提问来源于stack exchange，提问作者Alexandr Batenev