Devx DataGrid筛选对象解析以生成SQL查询

我整理了一套方案，可将Devx Grid的筛选器转换为一组对象，用于构建SQL查询，分享给有需要的开发者参考。

示例代码 #

// 转换Devx Grid筛选器并生成SQL查询条件对象
public class GridFilterToSqlConverter
{
    public List<SqlFilterItem> Convert(GridView gridView)
    {
        var filterItems = new List<SqlFilterItem>();
        var rootCriteria = gridView.ActiveFilterCriteria;
        
        TraverseCriteria(rootCriteria, filterItems);
        return filterItems;
    }

    private void TraverseCriteria(CriteriaOperator criteria, List<SqlFilterItem> items)
    {
        if (criteria is BinaryOperator binaryOp)
        {
            var propOperand = binaryOp.LeftOperand as OperandProperty;
            var valOperand = binaryOp.RightOperand as OperandValue;
            
            if (propOperand != null && valOperand != null)
            {
                items.Add(new SqlFilterItem
                {
                    Column = propOperand.PropertyName,
                    Op = MapToSqlOp(binaryOp.OperatorType),
                    Value = valOperand.Value
                });
            }

            // 递归处理嵌套的筛选条件
            TraverseCriteria(binaryOp.LeftOperand, items);
            TraverseCriteria(binaryOp.RightOperand, items);
        }
        // 可扩展处理And/Or组合条件、范围筛选等其他场景
    }

    private string MapToSqlOp(OperatorType opType)
    {
        return opType switch
        {
            OperatorType.Equal => "=",
            OperatorType.Greater => ">",
            OperatorType.Less => "<",
            OperatorType.Contains => "LIKE",
            OperatorType.StartsWith => "LIKE",
            OperatorType.EndsWith => "LIKE",
            _ => "="
        };
    }
}

// 用于构建SQL查询的条件对象
public class SqlFilterItem
{
    public string Column { get; set; }
    public string Op { get; set; }
    public object Value { get; set; }
}

这段代码会解析Devx Grid的活跃筛选规则，将其转换成包含列名、SQL操作符和对应值的条件对象列表，你可以基于这些对象拼接出符合需求的SQL WHERE子句。

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