Mike Taulty's Blog
Bits and Bytes from Microsoft UK
Bit More on Dynamic Queries
Mike Taulty's Blog

Mike's Badges

Follow on Twitter
View mike's profile on slideshare
Add to Technorati Favorites
CW Blog Awards

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 :-)).


Posted Mon, Jun 18 2007 4:12 AM by mtaulty

Comments

Mike Taulty's Blog wrote More Dynamic Queries
on Mon, Jun 18 2007 5:08 AM
Took this slightly further (which you could definitely describe as making it worse :-)) by adding a limited...
Jason Haley wrote Interesting Finds: June 18, 2007
on Mon, Jun 18 2007 1:31 PM
Mike Taulty's Blog wrote Messing with Dynamic LINQ Queries
on Thu, Sep 11 2008 9:08 AM
Mike was chatting to me about how you'd take something like this ( against LINQ to SQL and the Northwind...
Best price on viagra. wrote Viagra attorneys.
on Sat, Jul 4 2009 6:44 AM

Viagra. Online viagra. Free viagra. Viagra for women. Buy viagra. Try viagra for free. Mexican viagra. Herbal viagra.

(C) Mike Taulty, 2009. All rights reserved. The information in this weblog is provided "AS IS" with no warranties, and confers no rights. This weblog does not represent the thoughts, intentions, plans or strategies of my employer. It is solely my opinion. Inappropriate comments will be deleted at the authors discretion. All code samples are provided "AS IS" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.
Powered by Community Server (Non-Commercial Edition), by Telligent Systems