Building dynamic query in a loop using Expression trees

Question

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 (The column in the Sale table they apply to)

MinValue (Minimum value the reference column must be)

MaxValue (Maximum value the reference column must be)

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: 'Price', MinValue: '10', MaxValue: '20'

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

ReferenceColumn: 'Age', MinValue: '2', MaxValue: '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.

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();
}

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());
}

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.

Dose your original code work? This should not work and why are you using 1 = 1 and 1 = 0? — Filip Cordas
– Filip Cordas, Commented Sep 7, 2017 at 22:20
Yes it works if the collection is part of a DbContext using SQL Server. 1=1 and 1=0 are there so I can always append 'AND'/'OR' to the query string without having to deal with the first iteration special case, etc. — Valuator
– Valuator, Commented Sep 7, 2017 at 22:34
Try using LINQKit (albahari.com/nutshell/linqkit.aspx), it makes it much easier. The page says: With LINQKit, you can: ... Dynamically build predicates — user3188639
– user3188639, Commented Sep 7, 2017 at 22:47
I might take another look at LINQKit. I remember looking at it when I first approached this problem but opted to go with the SQL query to avoid bringing in another third party library. — Valuator
– Valuator, Commented Sep 7, 2017 at 22:52
Well, if you can use LINQ with your database, then you should be fine using LINQKit. I used it many times to build dynamic queries (for database and SharePoint), and I would never even try without it :) — user3188639
– user3188639, Commented Sep 7, 2017 at 23:07

Filip Cordas · Accepted Answer · 2017-09-07 23:35:23Z

I think this will work for you

 public class Sale
            {
                public int A { get; set; }

                public int B { get; set; }

                public int C { get; set; }
            }

            //I used a similar condition structure but my guess is you simplified the code to show in example anyway
            public class Condition
            {
                public string ColumnName { get; set; }

                public ConditionType Type { get; set; }

                public object[] Values { get; set; }

                public enum ConditionType
                {
                    Range
                }

                //This method creates the expression for the query
                public static Expression<Func<T, bool>> CreateExpression<T>(IEnumerable<Condition> query)
                {
                    var groups = query.GroupBy(c => c.ColumnName);

                    Expression exp = null;
                    //This is the parametar that will be used in you lambda function
                    var param = Expression.Parameter(typeof(T));

                    foreach (var group in groups)
                    {
                        // I start from a null expression so you don't use the silly 1 = 1 if this is a requirement for some reason you can make the 1 = 1 expression instead of null
                        Expression groupExp = null;

                        foreach (var condition in group)
                        {
                            Expression con;
                            //Just a simple type selector and remember switch is evil so you can do it another way
                            switch (condition.Type)
                            {
//this creates the between NOTE if data types are not the same this can throw exceptions
                                case ConditionType.Range:
                                    con = Expression.AndAlso(
                                        Expression.GreaterThanOrEqual(Expression.Property(param, condition.ColumnName), Expression.Constant(condition.Values[0])),
                                        Expression.LessThanOrEqual(Expression.Property(param, condition.ColumnName), Expression.Constant(condition.Values[1])));
                                    break;
                                default:
                                    con = Expression.Constant(true);
                                    break;
                            }
                            // Builds an or if you need one so you dont use the 1 = 1
                            groupExp = groupExp == null ? con : Expression.OrElse(groupExp, con);
                        }

                        exp = exp == null ? groupExp : Expression.AndAlso(groupExp, exp);
                    }

                    return Expression.Lambda<Func<T, bool>>(exp,param);
                }
            }

            static void Main(string[] args)
            {
                //Simple test data as an IQueriable same as EF or any ORM that supports linq.
                var sales = new[] 
                {
                    new Sale{ A = 1,  B = 2 , C = 1 },
                    new Sale{ A = 4,  B = 2 , C = 1 },
                    new Sale{ A = 8,  B = 4 , C = 1 },
                    new Sale{ A = 16, B = 4 , C = 1 },
                    new Sale{ A = 32, B = 2 , C = 1 },
                    new Sale{ A = 64, B = 2 , C = 1 },
                }.AsQueryable();

                var conditions = new[]
                {
                    new Condition { ColumnName = "A", Type = Condition.ConditionType.Range, Values= new object[]{ 0, 2 } },
                    new Condition { ColumnName = "A", Type = Condition.ConditionType.Range, Values= new object[]{ 5, 60 } },
                    new Condition { ColumnName = "B", Type = Condition.ConditionType.Range, Values= new object[]{ 1, 3 } },
                    new Condition { ColumnName = "C", Type = Condition.ConditionType.Range, Values= new object[]{ 0, 3 } },
                };

                var exp = Condition.CreateExpression<Sale>(conditions);
                //Under no circumstances compile the expression if you do you start using the IEnumerable and they are not converted to SQL but done in memory
                var items = sales.Where(exp).ToArray();

                foreach (var sale in items)
                {
                    Console.WriteLine($"new Sale{{ A = {sale.A},  B =  {sale.B} , C =  {sale.C} }}");
                }

                Console.ReadLine();
            }

That worked great. Interested that you included a condition Type of Range. I have the exact same thing but omitted it to keep the example shorter.
@Valuator I thought so that is why I added it. That's why I though your SQL was not working it was missing a ( . I build something like this so many times that it was obvious.

Collectives™ on Stack Overflow

Building dynamic query in a loop using Expression trees

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related