How to complete the dynamic construction of complex queries?

Sometimes, you need to dynamically build a more complex query condition and pass it into the database for query. The condition itself may come from a front-end request or a configuration file. Then at this time, the expression tree can help you. In this article, we'll use a few short examples to see how to do this.

Microsoft MVP lab researcher

You may also have received these requests:

Query from model

Configuration based query

Today we'll look at how the expression tree implements these requirements.

Fixed conditions can be passed in Where

The following is a simple unit test case. Next, we change this test case beyond recognition.

[Test]public void Normal(){    var re = Enumerable.Range(0, 10).AsQueryable() // 0-9        .Where(x => x >= 1 && x < 5).ToList(); // 1 2 3 4    var expectation = Enumerable.Range(1, 4); // 1 2 3 4    re.Should().BeEquivalentTo(expectation);}

Where in Queryable is an expression tree

Because it is Queryable, Where is actually an expression. Let's define it separately. By the way, check the length of the article.

[Test]
public void Expression00()
{
    Expression<Func<int, bool>> filter = x => x >= 1 && x < 5;
    var re = Enumerable.Range(0, 10).AsQueryable()
        .Where(filter).ToList();
    var expectation = Enumerable.Range(1, 4);
    re.Should().BeEquivalentTo(expectation);
}

Expressions can be implicitly converted by Lambda

To the right of Expression is a Lambda, so you can capture variables in the context.

So you can define minValue and maxValue separately.

So you can get minValue and maxValue from other places to change the filter.

[Test]public void Expression01(){    var minValue = 1;    var maxValue = 5;    Expression<Func<int, bool>> filter = x => x >= minValue && x < maxValue;    var re = Enumerable.Range(0, 10).AsQueryable()        .Where(filter).ToList();    var expectation = Enumerable.Range(1, 4);    re.Should().BeEquivalentTo(expectation);}

You can use methods to create expressions

In that case, we can also use a method to create Expression.

This method can actually be regarded as the factory method of this Expression.

[Test]
public void Expression02()
{
    var filter = CreateFilter(1, 5);
    var re = Enumerable.Range(0, 10).AsQueryable()
        .Where(filter).ToList();
    var expectation = Enumerable.Range(1, 4);
    re.Should().BeEquivalentTo(expectation);

    Expression<Func<int, bool>> CreateFilter(int minValue, int maxValue)
    {
        return x => x >= minValue && x < maxValue;
    }
}

Func allows more flexible combination conditions

You can use minValue and maxValue as parameters to make factory methods, so you can also use delegates.

Therefore, we can define the left and right as two Func S respectively, so that the specific comparison methods of the left and right can be determined by the outside.

[Test]
public void Expression03()
{
    var filter = CreateFilter(x => x >= 1, x => x < 5);
    var re = Enumerable.Range(0, 10).AsQueryable()
        .Where(filter).ToList();
    var expectation = Enumerable.Range(1, 4);
    re.Should().BeEquivalentTo(expectation);

    Expression<Func<int, bool>> CreateFilter(Func<int, bool> leftFunc, Func<int, bool> rightFunc)
    {
        return x => leftFunc.Invoke(x) && rightFunc.Invoke(x);
    }
}

You can also build expressions manually

In fact, the left and right are not only two Func S, but also two expressions directly.

However, a little different is that the combination of expressions needs to be created with the relevant methods in the Expression type.

We can find that there is no change in the place called this time, because Lambda can be implicitly converted to Func or Expression.

The meaning of each method can be seen from the comments.

[Test]
public void Expression04()
{
    var filter = CreateFilter(x => x >= 1, x => x < 5);
    var re = Enumerable.Range(0, 10).AsQueryable()
        .Where(filter).ToList();
    var expectation = Enumerable.Range(1, 4);
    re.Should().BeEquivalentTo(expectation);

    Expression<Func<int, bool>> CreateFilter(Expression<Func<int, bool>> leftFunc,
        Expression<Func<int, bool>> rightFunc)
    {
        // x
        var pExp = Expression.Parameter(typeof(int), "x");
        // (a => leftFunc(a))(x)
        var leftExp = Expression.Invoke(leftFunc, pExp);
        // (a => rightFunc(a))(x)
        var rightExp = Expression.Invoke(rightFunc, pExp);
        // (a => leftFunc(a))(x) && (a => rightFunc(a))(x)
        var bodyExp = Expression.AndAlso(leftExp, rightExp);
        // x => (a => leftFunc(a))(x) && (a => rightFunc(a))(x)
        var resultExp = Expression.Lambda<Func<int, bool>>(bodyExp, pExp);
        return resultExp;
    }
}

The deconstruction of expression is introduced to make it simpler

However, the above method can be optimized. Avoid direct calls to left and right expressions.

Using a method called Unwrap, you can solve Lambda Expression into an expression that contains only the Body part.

This is a custom extension method. You can introduce this method through ObjectVisitor.

Due to space constraints, we cannot talk about the implementation of Unwrap here. We just need to focus on the differences from the comments in the previous example.

ObjectVisitor: https://github.com/newbe36524/Newbe.ObjectVisitor

[Test]
public void Expression05()
{
    var filter = CreateFilter(x => x >= 1, x => x < 5);
    var re = Enumerable.Range(0, 10).AsQueryable()
        .Where(filter).ToList();
    var expectation = Enumerable.Range(1, 4);
    re.Should().BeEquivalentTo(expectation);

    Expression<Func<int, bool>> CreateFilter(Expression<Func<int, bool>> leftFunc,
        Expression<Func<int, bool>> rightFunc)
    {
        // x
        var pExp = Expression.Parameter(typeof(int), "x");
        // leftFunc(x)
        var leftExp = leftFunc.Unwrap(pExp);
        // rightFunc(x)
        var rightExp = rightFunc.Unwrap(pExp);
        // leftFunc(x) && rightFunc(x)
        var bodyExp = Expression.AndAlso(leftExp, rightExp);
        // x => leftFunc(x) && rightFunc(x)
        var resultExp = Expression.Lambda<Func<int, bool>>(bodyExp, pExp);
        return resultExp;
    }
}

You can splice more expressions

We can further optimize the following and extend the CreateFilter method to support the connection of multiple subexpressions and self defining subexpressions.

So we can get a JoinSubFilters method.

[Test]
public void Expression06()
{
    var filter = JoinSubFilters(Expression.AndAlso, x => x >= 1, x => x < 5);
    var re = Enumerable.Range(0, 10).AsQueryable()
        .Where(filter).ToList();
    var expectation = Enumerable.Range(1, 4);
    re.Should().BeEquivalentTo(expectation);

    Expression<Func<int, bool>> JoinSubFilters(Func<Expression, Expression, Expression> expJoiner,
        params Expression<Func<int, bool>>[] subFilters)
    {
        // x
        var pExp = Expression.Parameter(typeof(int), "x");
        var result = subFilters[0];
        foreach (var sub in subFilters[1..])
        {
            var leftExp = result.Unwrap(pExp);
            var rightExp = sub.Unwrap(pExp);
            var bodyExp = expJoiner(leftExp, rightExp);

            result = Expression.Lambda<Func<int, bool>>(bodyExp, pExp);
        }

        return result;
    }
}

Use factory methods instead of fixed subexpressions

With the previous experience, we know. In fact, the expression x = > x > = 1 can be built through a factory method.

Therefore, we use a CreateMinValueFilter to create this expression.

[Test]
public void Expression07()
{
    var filter = JoinSubFilters(Expression.AndAlso,
        CreateMinValueFilter(1),
        x => x < 5);
    var re = Enumerable.Range(0, 10).AsQueryable()
        .Where(filter).ToList();
    var expectation = Enumerable.Range(1, 4);
    re.Should().BeEquivalentTo(expectation);

    Expression<Func<int, bool>> CreateMinValueFilter(int minValue)
    {
        return x => x >= minValue;
    }

    Expression<Func<int, bool>> JoinSubFilters(Func<Expression, Expression, Expression> expJoiner,
        params Expression<Func<int, bool>>[] subFilters)
    {
        // x
        var pExp = Expression.Parameter(typeof(int), "x");
        var result = subFilters[0];
        foreach (var sub in subFilters[1..])
        {
            var leftExp = result.Unwrap(pExp);
            var rightExp = sub.Unwrap(pExp);
            var bodyExp = expJoiner(leftExp, rightExp);

            result = Expression.Lambda<Func<int, bool>>(bodyExp, pExp);
        }

        return result;
    }
}

Factory methods can also be created manually using Expression

Of course, you can use only the Expression related methods to create x = > x > = 1.

[Test]
public void Expression08()
{
    var filter = JoinSubFilters(Expression.AndAlso,
        CreateMinValueFilter(1),
        x => x < 5);
    var re = Enumerable.Range(0, 10).AsQueryable()
        .Where(filter).ToList();
    var expectation = Enumerable.Range(1, 4);
    re.Should().BeEquivalentTo(expectation);

    Expression<Func<int, bool>> CreateMinValueFilter(int minValue)
    {
        // x
        var pExp = Expression.Parameter(typeof(int), "x");
        // minValue
        var rightExp = Expression.Constant(minValue);
        // x >= minValue
        var bodyExp = Expression.GreaterThanOrEqual(pExp, rightExp);
        var result = Expression.Lambda<Func<int, bool>>(bodyExp, pExp);
        return result;
    }

    Expression<Func<int, bool>> JoinSubFilters(Func<Expression, Expression, Expression> expJoiner,
        params Expression<Func<int, bool>>[] subFilters)
    {
        // x
        var pExp = Expression.Parameter(typeof(int), "x");
        var result = subFilters[0];
        foreach (var sub in subFilters[1..])
        {
            var leftExp = result.Unwrap(pExp);
            var rightExp = sub.Unwrap(pExp);
            var bodyExp = expJoiner(leftExp, rightExp);

            result = Expression.Lambda<Func<int, bool>>(bodyExp, pExp);
        }

        return result;
    }
}

Similarly, sub expressions can be created in this way

Now that you have used Expression to create sub expressions, you can simply make a little improvement and get x = > x < 5 from the factory method.

[Test]
public void Expression09()
{
    var filter = JoinSubFilters(Expression.AndAlso,
        CreateValueCompareFilter(Expression.GreaterThanOrEqual, 1),
        CreateValueCompareFilter(Expression.LessThan, 5));
    var re = Enumerable.Range(0, 10).AsQueryable()
        .Where(filter).ToList();
    var expectation = Enumerable.Range(1, 4);
    re.Should().BeEquivalentTo(expectation);

    Expression<Func<int, bool>> CreateValueCompareFilter(Func<Expression, Expression, Expression> comparerFunc,
        int rightValue)
    {
        var pExp = Expression.Parameter(typeof(int), "x");
        var rightExp = Expression.Constant(rightValue);
        var bodyExp = comparerFunc(pExp, rightExp);
        var result = Expression.Lambda<Func<int, bool>>(bodyExp, pExp);
        return result;
    }

    Expression<Func<int, bool>> JoinSubFilters(Func<Expression, Expression, Expression> expJoiner,
        params Expression<Func<int, bool>>[] subFilters)
    {
        // x
        var pExp = Expression.Parameter(typeof(int), "x");
        var result = subFilters[0];
        foreach (var sub in subFilters[1..])
        {
            var leftExp = result.Unwrap(pExp);
            var rightExp = sub.Unwrap(pExp);
            var bodyExp = expJoiner(leftExp, rightExp);

            result = Expression.Lambda<Func<int, bool>>(bodyExp, pExp);
        }

        return result;
    }
}

Add a little configuration and you're done

Finally, we are creating the subexpression with a little skill. Determined by external parameters. The dynamic construction of a multi And value comparison query condition is basically completed.

[Test]
public void Expression10()
{
    var config = new Dictionary<string, int>
    {
        { ">=", 1 },
        { "<", 5 }
    };
    var subFilters = config.Select(x => CreateValueCompareFilter(MapConfig(x.Key), x.Value)).ToArray();
    var filter = JoinSubFilters(Expression.AndAlso, subFilters);
    var re = Enumerable.Range(0, 10).AsQueryable()
        .Where(filter).ToList();
    var expectation = Enumerable.Range(1, 4);
    re.Should().BeEquivalentTo(expectation);

    Func<Expression, Expression, Expression> MapConfig(string op)
    {
        return op switch
        {
            ">=" => Expression.GreaterThanOrEqual,
            "<" => Expression.LessThan,
            _ => throw new ArgumentOutOfRangeException(nameof(op))
        };
    }

    Expression<Func<int, bool>> CreateValueCompareFilter(Func<Expression, Expression, Expression> comparerFunc,
        int rightValue)
    {
        var pExp = Expression.Parameter(typeof(int), "x");
        var rightExp = Expression.Constant(rightValue);
        var bodyExp = comparerFunc(pExp, rightExp);
        var result = Expression.Lambda<Func<int, bool>>(bodyExp, pExp);
        return result;
    }

    Expression<Func<int, bool>> JoinSubFilters(Func<Expression, Expression, Expression> expJoiner,
        params Expression<Func<int, bool>>[] subFilters)
    {
        // x
        var pExp = Expression.Parameter(typeof(int), "x");
        var result = subFilters[0];
        foreach (var sub in subFilters[1..])
        {
            var leftExp = result.Unwrap(pExp);
            var rightExp = sub.Unwrap(pExp);
            var bodyExp = expJoiner(leftExp, rightExp);

            result = Expression.Lambda<Func<int, bool>>(bodyExp, pExp);
        }

        return result;
    }
}

summary

What if the logical relationship is more complex, there are multiple layers of nesting, like a tree, and there are many different comparison methods, even including methods?

Refer to the following examples:

https://github.com/newbe36524/Newbe.Demo/tree/main/src/BlogDemos/Newbe.ExpressionsTests/Newbe.ExpressionsTests/FilterFactory

If you are interested in this content, you can also browse the videos I recorded earlier to learn more:

1. Playwrights share C# expression tree, season 1

https://www.bilibili.com/video/BV15y4y1r7pK

2. Playwrights share C# expression tree, season 2

https://www.bilibili.com/video/BV1Mi4y1L7oR

You can also refer to the previous introduction:

In just ten steps, you can apply an expression tree to optimize dynamic calls

https://www.newbe.pro/Newbe.Claptrap/Using-Expression-Tree-To-Build-Delegate/index.html

Or look at MSDN documents. I think you can also gain something:

https://docs.microsoft.com/dotnet/csharp/programming-guide/concepts/expression-trees/?WT.mc_id=DX-MVP-5003606

This related code can be obtained at the following address:

https://github.com/newbe36524/Newbe.Demo/blob/main/src/BlogDemos/Newbe.ExpressionsTests/Newbe.ExpressionsTests/Examples/Z01SingleWhereTest.cs

If you think this article is good, remember to collect, like, comment and forward. Tell me what else you want to know!

Microsoft MVP

Microsoft's most valuable expert is a global award awarded by Microsoft to third-party technology professionals. Over the past 28 years, technology community leaders around the world have won this award for sharing expertise and experience in their online and offline technology communities.

MVP is a strictly selected expert team. They represent the most skilled and intelligent people. They are experts who are enthusiastic and helpful to the community. MVP is committed to helping others through lectures, forum Q & A, creating websites, writing blogs, sharing videos, open source projects, organizing meetings, and helping users in the Microsoft technology community use Microsoft technology to the greatest extent.
For more details, please visit the official website:
https://mvp.microsoft.com/zh-cn

Tags: C# .NET

Posted on Wed, 10 Nov 2021 07:25:45 -0500 by jotate

Programmer Help