Complicated Linq expression builder

Question

I need to build a configurable query builder that allows the user to compound any number of possible conditions. For example, lets say we have the classic case of Customer, Order, OrderItem and Product. The user wants to be able to draw a report on all orders that have more than 3 items. Or maybe orders that have exactly one item. Should be basically the same query, except that the comparator will be different.

The user might also want to tack on a condition that the customer lives in area code 10024. Or that the order includes at least/most/exactly 2 items of Product ID 14. Each condition will obviously need to be hard coded in its own method, but the basic signature for each condition will be:

private static Expression<Func<Order, bool>> SomePredicate(object someParameters)

So far, here's what I've got for the last condition I described:

private static Expression<Func<Order, bool>> CountProductItems(int productID, int count, Comparator comparator) {
  Expression<Func<Order, int>> productOrderItems = 
    order => order.Items.Where(i => i.ProductID == productID)
                  .Sum(i => i.Quantity);
  switch (comparator) {
    case Comparator.Equals:
      // return... uh... now what?
    case Comparator.GreaterThan:
      // you get the picture....
  }
}

I'm just missing that last syntactical step: how to use that expression productOrderItems and return orders where that expression equals/is less than/greater than/etc. the supplied value count.

How do you do it?

`Each condition will obviously need to be hard coded in its own method`. That's not true. Using Dynamic LINQ as explained by @ken, you can dynamically filter based on a string containing a column name and a value. If you store the criteria, you can build up complicated expressions directly and inject them into a `.Where` clause. — mellamokb, Jan 08 '13 at 16:42
@ken - no I didn't consider it, and I don't really want to if I can help it. I like strong-typed Linq queries, especially since my organization tends to request schema changes on a scarily regular basis, and I want to see a compiler error if one of my query conditions gets broken. — Shaul Behr, Jan 08 '13 at 16:43
@mellamokb - as mentioned in above comment, I'd prefer to avoid using dynamic Linq if I can. — Shaul Behr, Jan 08 '13 at 16:44

score 1 · Accepted Answer · answered Jan 08 '13 at 16:53

Instead of working with Expression objects inside the method, use Func:

Func<Order, int> productOrderItems =
    (Order o) => o.Items.Where(i => i.ProductID == productID)
                        .Sum(i => i.Quantity);

Then inside your switch, you can compose another Lambda expression on top of productOrderItems as your return value. It will automatically be converted to an Expression, as Expression and Func are technically interchangeable:

switch (comparator) {
    case Comparator.Equals:
        return (Order o) => productOrderItems(o) == count;
        break;
    // etc.
}

+1 Perfect answer, thank you! (One of these days I need to come to grips with the difference between `Expression` and `Func`) — Shaul Behr, Jan 08 '13 at 17:10

score 0 · Answer 2 · answered Jan 08 '13 at 16:51

0

I've used with success the Dynamic LINQ Query by ScottGu.

In our case, we need to define in runtime our Where clause, and it worked like a charm.

It's also referred here with added information.

With this you can do stuff like:

var query = Northwind.Products.Where("CategoryID=2 And UnitPrice>3");

answered Jan 08 '13 at 16:51

Andre Albuquerque

1,881
1
18
15

Thanks but I went for the strong-typed answer above. I like strong-typed Linq queries, especially since my organization tends to request schema changes on a scarily regular basis, and I want to see a compiler error if one of my query conditions gets broken. – Shaul Behr Jan 08 '13 at 17:23

Complicated Linq expression builder

2 Answers2