基于C#/EF实现PostgreSQL数据库的布尔查询文本搜索

1. 解析用户查询语句为EF可用结构 #

要将用户输入的带逻辑运算符的查询转换为EF能处理的表达式，需完成两步：分词和构建抽象语法树(AST)。

步骤1：分词（Tokenization） #

将用户输入字符串拆分为有意义的标记（tokens），包括搜索词、逻辑运算符（AND/OR/NOT，不区分大小写）、括号。

示例分词实现：

enum TokenType { Term, And, Or, Not, LParen, RParen }

class Token
{
    public TokenType Type { get; }
    public string Value { get; }
    public Token(TokenType type, string value) => (Type, Value) = (type, value);
}

List<Token> Tokenize(string input)
{
    var tokens = new List<Token>();
    var currentTerm = new StringBuilder();
    input = input.Trim();
    int index = 0;

    while (index < input.Length)
    {
        char c = input[index];
        if (char.IsWhiteSpace(c))
        {
            if (currentTerm.Length > 0)
            {
                AddToken(currentTerm.ToString(), tokens);
                currentTerm.Clear();
            }
            index++;
        }
        else if (c is '(' or ')')
        {
            if (currentTerm.Length > 0)
            {
                AddToken(currentTerm.ToString(), tokens);
                currentTerm.Clear();
            }
            tokens.Add(new Token(c == '(' ? TokenType.LParen : TokenType.RParen, c.ToString()));
            index++;
        }
        else
        {
            currentTerm.Append(c);
            index++;
        }
    }

    if (currentTerm.Length > 0)
        AddToken(currentTerm.ToString(), tokens);

    return tokens;
}

void AddToken(string value, List<Token> tokens)
{
    var upperValue = value.ToUpperInvariant();
    switch (upperValue)
    {
        case "AND": tokens.Add(new Token(TokenType.And, upperValue)); break;
        case "OR": tokens.Add(new Token(TokenType.Or, upperValue)); break;
        case "NOT": tokens.Add(new Token(TokenType.Not, upperValue)); break;
        default: tokens.Add(new Token(TokenType.Term, value)); break;
    }
}

步骤2：构建AST（抽象语法树） #

使用递归下降解析器，将分词结果转换为表示查询逻辑的AST。AST节点分为搜索词、NOT、AND、OR四种类型：

// AST节点基类
public abstract class SearchNode
{
    public abstract Expression<Func<Item, bool>> ToExpression();
}

// 搜索词节点
public class TermNode : SearchNode
{
    public string Term { get; }
    public TermNode(string term) => Term = term;

    public override Expression<Func<Item, bool>> ToExpression()
    {
        return x => EF.Functions.ILike(x.Text, $"%{Term}%");
    }
}

// NOT操作节点
public class NotNode : SearchNode
{
    public SearchNode Child { get; }
    public NotNode(SearchNode child) => Child = child;

    public override Expression<Func<Item, bool>> ToExpression()
    {
        var childExpr = Child.ToExpression();
        var negatedBody = Expression.Not(childExpr.Body);
        return Expression.Lambda<Func<Item, bool>>(negatedBody, childExpr.Parameters[0]);
    }
}

// AND操作节点
public class AndNode : SearchNode
{
    public SearchNode Left { get; }
    public SearchNode Right { get; }
    public AndNode(SearchNode left, SearchNode right) => (Left, Right) = (left, right);

    public override Expression<Func<Item, bool>> ToExpression()
    {
        var leftExpr = Left.ToExpression();
        var rightExpr = Right.ToExpression();

        // 重绑定右侧表达式参数，确保和左侧使用同一参数实例
        var paramRebinder = new ParameterRebinder(rightExpr.Parameters[0], leftExpr.Parameters[0]);
        var rebindedRightBody = paramRebinder.Visit(rightExpr.Body);

        var combinedBody = Expression.AndAlso(leftExpr.Body, rebindedRightBody);
        return Expression.Lambda<Func<Item, bool>>(combinedBody, leftExpr.Parameters[0]);
    }
}

// OR操作节点
public class OrNode : SearchNode
{
    public SearchNode Left { get; }
    public SearchNode Right { get; }
    public OrNode(SearchNode left, SearchNode right) => (Left, Right) = (left, right);

    public override Expression<Func<Item, bool>> ToExpression()
    {
        var leftExpr = Left.ToExpression();
        var rightExpr = Right.ToExpression();

        var paramRebinder = new ParameterRebinder(rightExpr.Parameters[0], leftExpr.Parameters[0]);
        var rebindedRightBody = paramRebinder.Visit(rightExpr.Body);

        var combinedBody = Expression.OrElse(leftExpr.Body, rebindedRightBody);
        return Expression.Lambda<Func<Item, bool>>(combinedBody, leftExpr.Parameters[0]);
    }
}

// 参数重绑定器
class ParameterRebinder : ExpressionVisitor
{
    private readonly ParameterExpression _oldParam;
    private readonly ParameterExpression _newParam;

    public ParameterRebinder(ParameterExpression oldParam, ParameterExpression newParam)
    {
        _oldParam = oldParam;
        _newParam = newParam;
    }

    protected override Expression VisitParameter(ParameterExpression node)
    {
        return node == _oldParam ? _newParam : base.VisitParameter(node);
    }
}

// 递归下降解析器
class SearchQueryParser
{
    private List<Token> _tokens;
    private int _currentPos;

    public SearchNode Parse(List<Token> tokens)
    {
        _tokens = tokens;
        _currentPos = 0;
        return ParseOrExpression();
    }

    // 解析OR表达式（优先级最低）
    private SearchNode ParseOrExpression()
    {
        var left = ParseAndExpression();
        while (_currentPos < _tokens.Count && _tokens[_currentPos].Type == TokenType.Or)
        {
            _currentPos++;
            var right = ParseAndExpression();
            left = new OrNode(left, right);
        }
        return left;
    }

    // 解析AND表达式（优先级高于OR）
    private SearchNode ParseAndExpression()
    {
        var left = ParseNotExpression();
        while (_currentPos < _tokens.Count && _tokens[_currentPos].Type == TokenType.And)
        {
            _currentPos++;
            var right = ParseNotExpression();
            left = new AndNode(left, right);
        }
        return left;
    }

    // 解析NOT表达式（优先级最高）
    private SearchNode ParseNotExpression()
    {
        if (_currentPos < _tokens.Count && _tokens[_currentPos].Type == TokenType.Not)
        {
            _currentPos++;
            var child = ParsePrimaryExpression();
            return new NotNode(child);
        }
        return ParsePrimaryExpression();
    }

    // 解析基础表达式（括号或单个词）
    private SearchNode ParsePrimaryExpression()
    {
        if (_currentPos >= _tokens.Count)
            throw new InvalidOperationException("输入格式错误：意外的结束");

        var token = _tokens[_currentPos];
        if (token.Type == TokenType.LParen)
        {
            _currentPos++;
            var expr = ParseOrExpression();
            if (_currentPos >= _tokens.Count || _tokens[_currentPos].Type != TokenType.RParen)
                throw new InvalidOperationException("输入格式错误：未闭合的括号");
            _currentPos++;
            return expr;
        }
        else if (token.Type == TokenType.Term)
        {
            _currentPos++;
            return new TermNode(token.Value);
        }
        throw new InvalidOperationException($"输入格式错误：意外的标记 {token.Value}");
    }
}

2. 编写EF查询实现高级搜索 #

将AST转换为EF Core可识别的表达式树，直接用于Where子句执行查询：

public List<Item> SearchItems(string searchQuery)
{
    using var context = new YourDbContext();

    // 1. 分词
    var tokens = Tokenize(searchQuery);
    // 2. 解析为AST
    var parser = new SearchQueryParser();
    var searchNode = parser.Parse(tokens);
    // 3. 转换为EF表达式
    var filterExpression = searchNode.ToExpression();
    // 4. 执行查询
    return context.Items.Where(filterExpression).ToList();
}

关键说明 #

• 大小写不敏感：通过EF.Functions.ILike调用PostgreSQL原生的大小写不敏感匹配函数，无需额外处理。
• 数据库端执行：EF Core会将表达式树转换为对应的SQL语句，所有过滤逻辑在数据库端完成，保证性能。
• 错误处理：解析过程中会抛出格式异常，需在Web界面捕获并提示用户输入正确格式（如(car OR auto) AND NOT crashed）。

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