字符串表达式查询解决方案

在日常的开发当中，会遇到前端构建用户所需要的查询条件，在后端使用对应的查询条件进行查询。但是前端构建的复杂查询条件往往是以字符串形式传递，后端在使用EF等orm框架时，不是很好操作。本文章就是为了解决这个问题。

主要是对用户的字符串表达式进行解析生成对应的表达式树，然后使用表达式树进行对应的查询

1. 后端对字符串表达式的解析

public class JsonQueryBuilder
{
    /// <summary>
    /// 应用JSON条件到查询
    /// </summary>
    public static ISugarQueryable<LabourRecord, MDMUser, MDMOrganization> ApplyJsonConditions(
        ISugarQueryable<LabourRecord, MDMUser, MDMOrganization> query,
        string userCondition,
        List<MongoLabourDbColumnInfo> dBColumns)
    {
        if (string.IsNullOrWhiteSpace(userCondition) || userCondition.Trim() == "1==1" || userCondition.Trim() == "(1==1)")
        {
            return query;
        }

        var conditions = ParseConditions(userCondition);

        for (int i = 0; i < conditions.Count; i++)
        {
            query = ApplyCondition(query, conditions[i], i, dBColumns);
        }

        return query;
    }

    private static ISugarQueryable<LabourRecord, MDMUser, MDMOrganization> ApplyCondition(
        ISugarQueryable<LabourRecord, MDMUser, MDMOrganization> query,
        JsonQueryCondition condition,
        int conditionIndex,
        List<MongoLabourDbColumnInfo> dBColumns)
    {
        var isNotContains = false;
        string jsonAccess = $"JSON_VALUE(CAST(JsonResult AS NVARCHAR(MAX)), '$.{condition.Field}')";
        if (jsonAccess.Contains("$.!"))
        {
            isNotContains = true;
            jsonAccess = jsonAccess.Replace("$.!", "$.");
        }
        string paramName = $"val{conditionIndex}";  // 唯一参数名
        string filed = condition.Field;
        filed = filed.StartsWith("!") ? filed.Substring(1) : filed;
        var currentColumn = dBColumns.SingleOrDefault(c => c.ColumnCode == filed);

        var whereString = string.Empty;
        var value = condition.Value;
        value = value.Replace("\"", "");
        var op = condition.Field.StartsWith("!") && condition.Operator == "Contains"
        ? "!Contains"
        : condition.Operator;
        if (op == "==")
        {
            op = "=";
        }

        whereString = $"{jsonAccess} {op} @{paramName}";

        object objectParams = null;
        if (currentColumn.DataType.StartsWith("decimal"))
        {
            objectParams = new Dictionary<string, object> { [paramName] = Convert.ToInt32(value) };
        }
        else
        {
            objectParams = new Dictionary<string, string> { [paramName] = value };
        }

        switch (op)
        {
            case "=":
            case "!=":
            case ">":
            case "<":
            case ">=":
            case "<=":
                return query.Where(whereString, objectParams);
            case "Contains":
                return query.Where($"{jsonAccess} LIKE @{paramName}",
                    new Dictionary<string, object> { [paramName] = $"%{value}%" });

            case "!Contains":
                // 处理否定包含条件
                return query.Where($"({jsonAccess} IS NULL OR {jsonAccess} NOT LIKE @{paramName})",
                    new Dictionary<string, object> { [paramName] = $"%{value}%" });

            default:
                throw new ArgumentException($"不支持的运算符: {condition.Operator}");
        }
    }

    /// <summary>
    /// 解析条件字符串为条件对象列表
    /// </summary>
    private static List<JsonQueryCondition> ParseConditions(string conditionStr)
    {
        var conditions = new List<JsonQueryCondition>();

        // 分割条件，忽略空条件和1==1
        var andGroups = conditionStr.Split(new[] { "&&" }, StringSplitOptions.RemoveEmptyEntries)
            .ToList()
            .Where(cond => cond.Trim() != "1==1")
            .Where(cond => !string.IsNullOrWhiteSpace(cond))
            .ToList();

        foreach (var group in andGroups)
        {
            var trimmed = group.Trim();
            if (string.IsNullOrWhiteSpace(trimmed)) continue;

            var condition = new JsonQueryCondition();

            // 处理 Contains
            if (trimmed.Contains(".Contains("))
            {
                condition.Field = trimmed.Substring(0, trimmed.IndexOf('.')).Trim();
                condition.Operator = "Contains";
                condition.Value = GetStringBetweenQuotes(trimmed);
            }
            // 处理 ==
            else if (trimmed.Contains("=="))
            {
                var parts = trimmed.Split(new[] { "==" }, StringSplitOptions.RemoveEmptyEntries);
                condition.Field = parts[0].Trim();
                condition.Operator = "==";
                condition.Value = parts[1].Trim().Trim('"');
            }
            // 处理 !=
            else if (trimmed.Contains("!="))
            {
                var parts = trimmed.Split(new[] { "!=" }, StringSplitOptions.RemoveEmptyEntries);
                condition.Field = parts[0].Trim();
                condition.Operator = "!=";
                condition.Value = parts[1].Trim().Trim('"');
            }
            // 处理 >
            else if (trimmed.Contains(">"))
            {
                var parts = trimmed.Split('>');
                condition.Field = parts[0].Trim();
                condition.Operator = ">";
                condition.Value = parts[1].Trim();
            }
            // 处理 <
            else if (trimmed.Contains("<"))
            {
                var parts = trimmed.Split('<');
                condition.Field = parts[0].Trim();
                condition.Operator = "<";
                condition.Value = parts[1].Trim();
            }
            // 处理 >=
            else if (trimmed.Contains(">="))
            {
                var parts = trimmed.Split(new[] { ">=" }, StringSplitOptions.RemoveEmptyEntries);
                condition.Field = parts[0].Trim();
                condition.Operator = ">=";
                condition.Value = parts[1].Trim();
            }
            // 处理 <=
            else if (trimmed.Contains("<="))
            {
                var parts = trimmed.Split(new[] { "<=" }, StringSplitOptions.RemoveEmptyEntries);
                condition.Field = parts[0].Trim();
                condition.Operator = "<=";
                condition.Value = parts[1].Trim();
            }
            else
            {
                throw new ArgumentException($"无法识别的条件格式: {trimmed}");
            }

            conditions.Add(condition);
        }

        return conditions;
    }

    /// <summary>
    /// 获取引号之间的字符串
    /// </summary>
    private static string GetStringBetweenQuotes(string input)
    {
        int firstQuote = input.IndexOf('"');
        if (firstQuote == -1) return string.Empty;

        int secondQuote = input.IndexOf('"', firstQuote + 1);
        if (secondQuote == -1) return string.Empty;

        return input.Substring(firstQuote + 1, secondQuote - firstQuote - 1);
    }

}

2. 具体的查询举例

var selectQuery = this._dbContext.Insurance.Queryable<LabourRecord>()
                    .InnerJoin<MDMUser>((lr, usr) => lr.UserID == usr.ID)
                    .InnerJoin<MDMOrganization>((lr, usr, org) => lr.DeptID == org.ID)
                    .AS($"Labour_Record_{dbCode}")
                    .Where((lr, usr, org) =>
                        lr.Status == 1 && usr.Status == 1 && org.Status == 1
                    );
if (!string.IsNullOrEmpty(search.entity.Filter) && search.entity.Filter != "(1==1)")
            {
                var condation = search.entity.Filter;
                // 去掉引号 可忽略
                condation = string.IsNullOrEmpty(condation) ? "1==1" : condation.Substring(1, condation.Length - 2);
                selectQuery = JsonQueryBuilder.ApplyJsonConditions(selectQuery, condation, dbColumns);
                System.Console.WriteLine(selectQuery.ToSqlString());
                return selectQuery;
            }
            else
            {
                return selectQuery;
            }