在ASP.NET中基于OData实现动态$search的技术咨询

需求：

• 接收用户查询词，与每条数据的字符串或GUID字段对比计算得分
• 按得分排序后返回结果，支持Take/Skip分页

当前实现问题：
已通过DbContext注册的Similarity数据库函数计算得分，但仅做了布尔型阈值过滤，无法实现动态排序的搜索效果。

当前实现代码：

public class ExperimentalSearchBinder : QueryBinder, ISearchBinder
{
    public Expression BindSearch(SearchClause searchClause, QueryBinderContext context)
    {
        var parameter = Expression.Parameter(context.ElementClrType, "p");

        if (searchClause.Expression is not SearchTermNode node) throw new Exception("Only simple search clauses are allowed");

        switch (context.ElementClrType)
        {
            case { } clrType when clrType == typeof(ComponentModel):
                var properties = typeof(ComponentModel).GetProperties()
                    .Where(p => p.PropertyType == typeof(string) || p.PropertyType == typeof(Guid))
                    .ToList();

                Expression? combinedExpression = null;

                foreach (var propertyInfo in properties)
                {
                    Expression property = Expression.Property(parameter, propertyInfo.Name);
                    if (propertyInfo.PropertyType == typeof(Guid))
                    {
                        property = Expression.Call(property, typeof(Guid).GetMethod("ToString", Type.EmptyTypes)!);
                    }
                    var method = typeof(DatabaseContext).GetMethod(nameof(DatabaseContext.Similarity), [typeof(string), typeof(string)]);

                    if (method is null)
                    {
                        throw new MissingMethodException($"Method not found: {nameof(DatabaseContext.Similarity)}");
                    }

                    var searchTerm = Expression.Constant(node.Text);
                    var similarityCall = Expression.Call(method, property, searchTerm);
                    var comparison = Expression.GreaterThanOrEqual(similarityCall, Expression.Constant(0.1));

                    combinedExpression = combinedExpression == null ? comparison : Expression.OrElse(combinedExpression, comparison);
                }

                if (combinedExpression is null)
                {
                    throw new Exception("Failed to build combinedExpression");
                }

                return Expression.Lambda<Func<ComponentModel, bool>>(combinedExpression, parameter);

            default:
                //Default simply tries to match ID
                //TODO: Make a better default implementation
                var defaultProperty = Expression.Property(parameter, "Id");
                var defaultSearchTerm = Expression.Constant(node.Text);
                var defaultComparison = Expression.Equal(defaultProperty, defaultSearchTerm);

                return Expression.Lambda(defaultComparison, parameter);
        }
    }
}

期望生成的SQL：

SELECT *
FROM (
    SELECT *, SIMILARITY(name, '801') + similarity(description, '801') as Score
    FROM component
) subquery
WHERE Score > 0.3 order by Score desc;

已尝试方向：

• 参考OData官方博客的搜索绑定示例
• 尝试自定义UriFunctions但无进展

要实现带得分排序的搜索，不能仅通过ISearchBinder做过滤，需要结合投影计算得分+自定义排序，核心思路是将得分作为计算字段加入查询，再基于该字段过滤和排序。

步骤1：定义包含得分的DTO（或扩展实体） #

如果不想修改原实体，可以创建一个包含原实体属性+Score字段的DTO：

public class ComponentSearchResult
{
    public ComponentModel Component { get; set; }
    public double Score { get; set; }
}

步骤2：重写OData查询处理逻辑，替换SearchBinder并注入得分计算 #

放弃仅在ISearchBinder中做过滤，改为在查询管道中构建包含得分的子查询：

1. 自定义SearchBinder时，不仅生成过滤条件，还要保留得分计算表达式：

public class ScoredSearchBinder : QueryBinder, ISearchBinder
{
    // 保存当前查询的得分计算表达式，供后续排序使用
    public Expression ScoreExpression { get; private set; }

    public Expression BindSearch(SearchClause searchClause, QueryBinderContext context)
    {
        var parameter = Expression.Parameter(context.ElementClrType, "p");
        if (searchClause.Expression is not SearchTermNode node) 
            throw new InvalidOperationException("仅支持简单搜索词");

        var properties = context.ElementClrType.GetProperties()
            .Where(p => p.PropertyType == typeof(string) || p.PropertyType == typeof(Guid))
            .ToList();

        Expression? scoreSum = null;
        Expression? filterExpr = null;

        foreach (var prop in properties)
        {
            Expression propExpr = Expression.Property(parameter, prop.Name);
            // GUID转字符串
            if (prop.PropertyType == typeof(Guid))
            {
                propExpr = Expression.Call(propExpr, typeof(Guid).GetMethod("ToString", Type.EmptyTypes)!);
            }

            // 获取Similarity方法引用
            var simMethod = typeof(DatabaseContext).GetMethod(nameof(DatabaseContext.Similarity), 
                new[] { typeof(string), typeof(string) }) 
                ?? throw new MissingMethodException("未找到Similarity方法");

            var termConst = Expression.Constant(node.Text);
            var simCall = Expression.Call(simMethod, propExpr, termConst);

            // 累加得分
            scoreSum = scoreSum == null ? simCall : Expression.Add(scoreSum, simCall);
            // 过滤条件：单个字段相似度≥阈值
            var propFilter = Expression.GreaterThanOrEqual(simCall, Expression.Constant(0.1));
            filterExpr = filterExpr == null ? propFilter : Expression.OrElse(filterExpr, propFilter);
        }

        if (scoreSum == null || filterExpr == null)
            throw new InvalidOperationException("无法构建搜索表达式");

        ScoreExpression = scoreSum;
        return Expression.Lambda(filterExpr, parameter);
    }
}

2. 在OData配置中注册自定义SearchBinder，并在控制器中处理查询：

// Program.cs中注册
builder.Services.AddControllers().AddOData(options =>
{
    options.Select().Filter().OrderBy().Expand().Count().SetMaxTop(100);
    options.AddRouteComponents("odata", GetEdmModel(), services =>
    {
        services.AddSingleton<ISearchBinder, ScoredSearchBinder>();
    });
});

// 控制器方法
[HttpGet]
[EnableQuery]
public IQueryable<ComponentSearchResult> Get(ODataQueryOptions<ComponentModel> options)
{
    var searchBinder = options.Context.RequestContainer.GetRequiredService<ScoredSearchBinder>();
    var dbQuery = _context.Components.AsQueryable();

    // 应用搜索过滤
    if (options.Search != null)
    {
        var filterExpr = options.Search.ApplyTo(dbQuery, options.Context) as IQueryable<ComponentModel>;
        dbQuery = filterExpr ?? dbQuery;

        // 构建得分投影
        var parameter = Expression.Parameter(typeof(ComponentModel), "p");
        var scoreExpr = Expression.Invoke(searchBinder.ScoreExpression, parameter);
        var projectionExpr = Expression.MemberInit(
            Expression.New(typeof(ComponentSearchResult)),
            Expression.Bind(typeof(ComponentSearchResult).GetProperty(nameof(ComponentSearchResult.Component))!, parameter),
            Expression.Bind(typeof(ComponentSearchResult).GetProperty(nameof(ComponentSearchResult.Score))!, scoreExpr)
        );

        var projectionLambda = Expression.Lambda<Func<ComponentModel, ComponentSearchResult>>(projectionExpr, parameter);
        var scoredQuery = dbQuery.Select(projectionLambda);

        // 过滤得分阈值（比如0.3）+ 按得分降序
        return scoredQuery.Where(r => r.Score > 0.3).OrderByDescending(r => r.Score);
    }

    // 无搜索时默认返回
    return dbQuery.Select(c => new ComponentSearchResult { Component = c, Score = 0 });
}

步骤3：验证生成的SQL #

上述代码会生成类似你期望的子查询结构，EF Core会将得分计算、过滤和排序翻译为对应的SQL语句，支持Take/Skip分页。

后续优化方向 #

• 移除switch分支：通过反射通用处理所有实体类型，约定需要搜索的字段（比如标记特性）
• 支持多搜索词：扩展SearchBinder处理SearchAndNode/SearchOrNode
• 权重配置：给不同字段设置不同的相似度权重（比如name权重×2）

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