使用表达式树循环构建动态查询

我有一个系统，可以将与Sales相关的不同条件存储在数据库中。加载条件后，它们将用于构建查询并返回所有适用的Sales。条件对象如下所示：

I have a system that allows different criteria pertaining to Sales to be stored in the database. When the criteria are loaded, they are used to build a query and return all applicable Sales. The criteria objects look like this:

ReferenceColumn（它们适用于Sale表中的列）

ReferenceColumn (The column in the Sale table they apply to)

MinValue （引用列必须为最小值）

MinValue (Minimum value the reference column must be)

MaxValue（引用列必须为最大值）

MaxValue (Maximum value the reference column must be)

A搜索销售是使用上述条件的集合完成的。相同类型的ReferenceColumns进行或运算，不同类型的ReferenceColumns进行AND运算。因此，例如，如果我有三个条件：

A search for Sales is done using a collection of the aforementioned criteria. ReferenceColumns of the same type are OR'd together, and ReferenceColumns of different types are AND'd together. So for example if I had three criteria:

ReferenceColumn：价格，MinValue： 10，MaxValue： 20

ReferenceColumn: 'Price', MinValue: '10', MaxValue: '20'

参考栏：'价格'，最小值：'80'，最大值：'100'

ReferenceColumn: 'Price', MinValue: '80', MaxValue: '100'

参考栏：'年龄'，最小值：'2'， MaxValue：'3'

ReferenceColumn: 'Age', MinValue: '2', MaxValue: '3'

查询应返回价格介于10-20或80-100之间的所有Sales，但前提是这些Sales的年龄介于2和3岁。

The query should return all Sales where the price was between 10-20 or between 80-100, but only if those Sales's Age is between 2 and 3 years old.

我使用SQL查询字符串并使用.FromSql来实现它：

I have it implemented using a SQL query string and executing using .FromSql:

public IEnumerable<Sale> GetByCriteria(ICollection<SaleCriteria> criteria)
{
StringBuilder sb = new StringBuilder("SELECT * FROM Sale");

var referenceFields = criteria.GroupBy(c => c.ReferenceColumn);

// Adding this at the start so we can always append " AND..." to each outer iteration
if (referenceFields.Count() > 0)
{
    sb.Append(" WHERE 1 = 1");
}

// AND all iterations here together
foreach (IGrouping<string, SaleCriteria> criteriaGrouping in referenceFields)
{
    // So we can always use " OR..."
    sb.Append(" AND (1 = 0");

    // OR all iterations here together
    foreach (SaleCriteria sc in criteriaGrouping)
    {
        sb.Append($" OR {sc.ReferenceColumn} BETWEEN '{sc.MinValue}' AND '{sc.MaxValue}'");
    }

    sb.Append(")");
}

return _context.Sale.FromSql(sb.ToString();
}

这对于我们的数据库来说确实可以正常工作，但是在其他集合中效果不佳，尤其是我们用于UnitTesting的InMemory数据库，因此我尝试使用到目前为止，我从未使用过表达式树。

And this is fact works just fine with our database, but it doesn't play nice with other collections, particulary the InMemory database we use for UnitTesting, so I'm trying to rewrite it using Expression trees, which I've never used before. So far I've gotten this:

public IEnumerable<Sale> GetByCriteria(ICollection<SaleCriteria> criteria)
{
var referenceFields = criteria.GroupBy(c => c.ReferenceColumn);

Expression masterExpression = Expression.Equal(Expression.Constant(1), Expression.Constant(1));
List<ParameterExpression> parameters = new List<ParameterExpression>();

// AND these...
foreach (IGrouping<string, SaleCriteria> criteriaGrouping in referenceFields)
{
    Expression innerExpression = Expression.Equal(Expression.Constant(1), Expression.Constant(0));
    ParameterExpression referenceColumn = Expression.Parameter(typeof(Decimal), criteriaGrouping.Key);
    parameters.Add(referenceColumn);

    // OR these...
    foreach (SaleCriteria sc in criteriaGrouping)
    {
        Expression low = Expression.Constant(Decimal.Parse(sc.MinValue));
        Expression high = Expression.Constant(Decimal.Parse(sc.MaxValue));
        Expression rangeExpression = Expression.GreaterThanOrEqual(referenceColumn, low);
        rangeExpression = Expression.AndAlso(rangeExpression, Expression.LessThanOrEqual(referenceColumn, high));
        innerExpression = Expression.OrElse(masterExpression, rangeExpression);
    }

    masterExpression = Expression.AndAlso(masterExpression, innerExpression);
}

var lamda = Expression.Lambda<Func<Sale, bool>>(masterExpression, parameters);

return _context.Sale.Where(lamda.Compile());
}

当我调用Expression.Lamda时，当前抛出ArgumentException。小数不能在此处使用，它表示要输入销售，但我不知道要在其中放置什么以进行销售，而且我不确定这里的位置是否正确。我还担心我的masterExpression每次都在自我复制，而不是像对字符串生成器那样进行附加，但这也许仍然可以工作。

It's currently throwing an ArgumentException when I call Expression.Lamda. Decimal cannot be used there and it says it wants type Sale, but I don't know what to put there for Sales, and I'm not sure I'm even on the right track here. I'm also concerned that my masterExpression is duplicating with itself each time instead of appending like I did with the string builder, but maybe that will work anyway.

我是寻找有关如何将此动态查询转换为表达式树的帮助，如果我不在这里，我愿意采用一种完全不同的方法。

I'm looking for help on how to convert this dynamic query to an Expression tree, and I'm open to an entirely different approach if I'm off base here.