Bit More on Dynamic Queries

I took this a little bit further and ended up with;

namespace MyExtensions
{
  public enum Operand
  {
    Equal,
    NotEqual,
    LessThan,
    LessThanEqual,
    GreaterThan,
    GreaterThanEqual
  }
  public static class Extensions
  {

    public static IQueryable<T> AddClause<T, V>(this IQueryable<T> queryable,
      string propertyName, Operand operand, V propertyValue) 
    {
      IQueryable<T> query = queryable.Where<T>(
        MakeExpression<T, V>(propertyName, operand, propertyValue));

      return (query);
    }
    private static Expression<Func<T,bool>> MakeExpression<T, V>(string propertyName, Operand operand,
      V propertyValue)
    {
      ParameterExpression pe = Expression.Parameter(typeof(T), "p");

      Func<Expression, Expression, bool, MethodInfo, BinaryExpression>
        fn = GetFuncForOperand(operand);

      Expression<Func<T,bool>> e =
        Expression.Lambda<Func<T, bool>>(
          fn(Expression.Property(
            pe,
            typeof(T).GetProperty(propertyName)),
            Expression.Constant(propertyValue, typeof(V)),
            false,
            null),
        new ParameterExpression[] { pe });

      return (e);
    }
    private static Func<Expression, Expression, bool, MethodInfo, BinaryExpression> GetFuncForOperand(Operand operand)
    {
      Func<Expression, Expression, bool, MethodInfo, BinaryExpression> func = null;

      switch (operand)
      {
        case Operand.Equal:
          func = Expression.Equal;
          break;
        case Operand.NotEqual:
          func = Expression.NotEqual;
          break;
        case Operand.LessThan:
          func = Expression.LessThan;
          break;
        case Operand.LessThanEqual:
          func = Expression.LessThanOrEqual;
          break;
        case Operand.GreaterThan:
          func = Expression.GreaterThan;
          break;
        case Operand.GreaterThanEqual:
          func = Expression.GreaterThanOrEqual;
          break;
        default:
          break;
      }
      return (func);
    }
  }
}

 

and then I can use it as;

   NorthwindDataContext ctx = new NorthwindDataContext("server=.;database=northwind");

      var query = from p in ctx.Products 
                  select p;

      short? val = 39;
      int? sup = 2;

      query = query.AddClause("UnitsInStock", Operand.LessThan, val);
      query = query.AddClause("SupplierID", Operand.Equal, sup);

      foreach (Product v in query)
      {
        Console.WriteLine(v.ProductID);
      }

 

all usual caveats around this being “not too clever” apply 🙂

So, now we can’t just do equality, we can also do other operators and we can do them dynamically. A couple of things about this;

  1. I’m not so keen on having to set up nullable types as the 3rd parameter. I’ve tried a few ways around that but haven’t got to one that’s nice yet.
  2. There’s no “Or” clause that you can add.
  3. This is only affecting the where clause, not sorting, grouping and so on.

I also thought a bit about how the 3 parameters that I’m passing could be encapsulated into a Lambda of sorts for situations where you know the operator up front but you don’t know the property. I worked bit on this to get to a point something like;

query = query.AddClause(p => p.Column(“CustomerID”) == “AROUT”);

but I haven’t actually got that working at this point – essentially, it’d just mean a translation from the “==” to the right operator and (I think) deciphering the call to the Column method (my invention) at runtime to generate the right call to my method MakeExpression above.

( I’ve a sneaking feeling that someone is going to come along and say “You do realise that this is already all in the framework, don’t you?” and I’m missing something :-)).