如何在LINQ-SQL中对分隔列查询且避免客户端求值

我需要查询包含Operator列的视图，示例数据如下：
Ed、Edward、Ed;Adam、[null]、Bob; Edward、Bob; Ed; Tom

给定List<string>类型的用户输入（如"Ed","Peter","Mark"），需返回完全包含任一输入的行（示例中为第1、3、6行）。等效SQL查询如下：

SELECT * 
FROM myview 
WHERE "Operator" LIKE 'Ed' OR "Operator" LIKE 'Ed;%' OR "Operator" LIKE '%;Ed' OR "Operator" LIKE '%;Ed;%' 
-- 对"Peter"和"Mark"等重复上述逻辑

我尝试了两种写法均报错：

写法1 #

var query = IQueryable<Myview> farms;
var List<string> operators = new List<string> { "Ed","Peter", "Mark" };

var filteredFarms = query.Where(t => t.Operator != null && operators.Any(op => t.Operator.Split(';').Any(o => o == op)));

报错信息：

"The LINQ expression ... could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'."

写法2 #

var filteredFarms = query.Where(t => t.Operator != null && operators.Any(op => EF.Functions.Like(t.Operator, ""+op+";%")));

同样报错。请问是否存在无需客户端求值的LINQ实现方式？假设分号仅用作分隔符。

可以通过手动构建包含所有Like条件的OR表达式来实现，EF Core能够正确翻译这种形式的查询，无需客户端求值。

实现思路 #

针对每个输入的操作符，生成四个匹配条件（完全等于、开头是op;、结尾是;op、中间包含;op;），然后将所有条件通过OR组合起来。可以使用PredicateBuilder（来自LINQKit）或者手动拼接表达式树来完成。

代码示例（使用LINQKit的PredicateBuilder） #

首先安装LINQKit包，然后编写如下代码：

using LINQKit;

var query = IQueryable<Myview> farms;
var operators = new List<string> { "Ed", "Peter", "Mark" };

var predicate = PredicateBuilder.New<Myview>(t => t.Operator != null);

foreach (var op in operators)
{
    // 为每个操作符添加四个匹配条件
    var opPredicate = PredicateBuilder.New<Myview>();
    opPredicate = opPredicate.Or(t => EF.Functions.Like(t.Operator, op));
    opPredicate = opPredicate.Or(t => EF.Functions.Like(t.Operator, $"{op};%"));
    opPredicate = opPredicate.Or(t => EF.Functions.Like(t.Operator, $";{op}"));
    opPredicate = opPredicate.Or(t => EF.Functions.Like(t.Operator, $";{op};%"));
    
    predicate = predicate.And(opPredicate);
}

var filteredFarms = query.AsExpandable().Where(predicate);

不使用第三方包的手动实现 #

如果不想依赖LINQKit，可以手动构建表达式树：

using System.Linq.Expressions;

var query = IQueryable<Myview> farms;
var operators = new List<string> { "Ed", "Peter", "Mark" };

// 初始条件：Operator不为null
Expression<Func<Myview, bool>> baseExpr = t => t.Operator != null;
var parameter = baseExpr.Parameters[0];

Expression finalExpr = baseExpr.Body;

foreach (var op in operators)
{
    // 构建四个Like条件
    var eqExpr = Expression.Call(
        typeof(EF.Functions),
        nameof(EF.Functions.Like),
        Type.EmptyTypes,
        Expression.Property(parameter, nameof(Myview.Operator)),
        Expression.Constant(op)
    );
    
    var startExpr = Expression.Call(
        typeof(EF.Functions),
        nameof(EF.Functions.Like),
        Type.EmptyTypes,
        Expression.Property(parameter, nameof(Myview.Operator)),
        Expression.Constant($"{op};%")
    );
    
    var endExpr = Expression.Call(
        typeof(EF.Functions),
        nameof(EF.Functions.Like),
        Type.EmptyTypes,
        Expression.Property(parameter, nameof(Myview.Operator)),
        Expression.Constant($";{op}")
    );
    
    var middleExpr = Expression.Call(
        typeof(EF.Functions),
        nameof(EF.Functions.Like),
        Type.EmptyTypes,
        Expression.Property(parameter, nameof(Myview.Operator)),
        Expression.Constant($";{op};%")
    );
    
    // 合并当前操作符的四个条件为OR
    var opOrExpr = Expression.OrElse(Expression.OrElse(eqExpr, startExpr), Expression.OrElse(endExpr, middleExpr));
    // 将当前操作符的条件与最终表达式合并为AND（满足任一操作符的匹配条件即可）
    finalExpr = Expression.AndAlso(finalExpr, opOrExpr);
}

var finalPredicate = Expression.Lambda<Func<Myview, bool>>(finalExpr, parameter);
var filteredFarms = query.Where(finalPredicate);

报错原因说明 #

• 写法1中Split(';')无法被EF Core翻译为SQL，EF不支持将字符串拆分操作转换为数据库可执行的语句。
• 写法2中operators.Any(op => ...)的嵌套逻辑无法被正确翻译，EF Core不能将内存集合的Any操作与数据库端的Like条件结合转换为对应SQL。

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