LINQ and Dynamic Queries

After quite a few LINQ talks, people have asked me about dynamic queries and I always feel a bit of a headache coming on as it’s an order of magnitude harder than what I’ve been talking about.

Now, when people ask for dynamic queries they’re not talking about;

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

      string variableCountry = "";

      var query = from c in ctx.Customers
                  where c.Country == variableCountry
                  select c;

      Console.WriteLine("Which country?");
      variableCountry = Console.ReadLine();

      foreach (Customer c in query)
      {
        Console.WriteLine(c.CustomerID);
      }

 

Note that I deliberately chose to alter the variable after the query definition there.

Now, generally speaking, they’re not talking about something like this (which is pretty interesting in itself);

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

      string variableCountry = "";

      var query = from c in ctx.Customers
                  where c.Country == variableCountry
                  select c;

      Console.WriteLine("Which country?");
      variableCountry = Console.ReadLine();

      Console.WriteLine("Want to add a city too [y/n]");

      if (string.Compare(Console.ReadLine(), "y", true) == 0)
      {
        Console.WriteLine("Which city?");
        string city = Console.ReadLine();

        query = from c in query
                where c.City == city
                select c;
      }
      foreach (Customer c in query)
      {
        Console.WriteLine(c.CustomerID);
      }

But, quite often, they’re actually talking about situations where you do not know the name of the additional column(s) or the additional values that you might want to filter on. Furthermore, you might not even know the operators that you want to apply.

There’s a tantalising post over here which makes it look pretty easy but, as far as I can tell, that class QueryExpression is gone from the Beta 1 builds. There’s then the LINQ Samples which has a dynamic queries sample in it which is pretty amazing but is also pretty complicated.

So, I had a look at doing this taking a simplistic approach of applying an additional Where clause to an existing query using System.Linq.Expressions. Now I’ll admit that I don’t find this stuff immediately obvious and so I spent quite a while reading this series of posts;

http://community.bartdesmet.net/blogs/bart/archive/2007/04/05/the-iqueryable-tales-linq-to-ldap-part-0.aspx

which is really great stuff but it’s also a bit complicated so I ended up constructing what I wanted “simply” by using Reflector on existing LINQ queries to see what kinds of Expressions were being generated.

So, here’s what I ended up with;

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

      var query = from c in ctx.Customers
                  where c.Country == "UK"
                  select c;

      Console.WriteLine("Which column?");
      string column = Console.ReadLine();

      Console.WriteLine("What value?");
      string value = Console.ReadLine();

      ParameterExpression pe = Expression.Parameter(typeof(Customer), "c");

      IQueryable<Customer> extendedQuery = query.Where<Customer>(
        Expression.Lambda<Func<Customer, bool>>(
          Expression.Equal(Expression.Property(
            pe,
            typeof(Customer).GetProperty(column)),
            Expression.Constant(value, typeof(string)),
            false,
            typeof(string).GetMethod("op_Equality")),
        new ParameterExpression[] { pe }));

      foreach (Customer c in extendedQuery)
      {
        Console.WriteLine(c.CustomerID);
      }

 

And I have a little piece of code which goes ahead and takes a pre-formed LINQ to SQL query and adds a little bit more onto it in terms of adding an additional Where clause that’s prepared to accept both a property name (or a column name if you like) and a value for that property (as long as it’s a string!). I tidied this up a little bit and turned it into an extension method;

namespace MyExtensions
{
  public static class Extensions
  {
    public static IQueryable<T> AddEqualityCondition<T, V>(this IQueryable<T> queryable,
      string propertyName, V propertyValue)
    {
      ParameterExpression pe = Expression.Parameter(typeof(T), "p");

      IQueryable<T> x = queryable.Where<T>(
        Expression.Lambda<Func<T, bool>>(
          Expression.Equal(Expression.Property(
            pe,
            typeof(T).GetProperty(propertyName)),
            Expression.Constant(propertyValue, typeof(V)),
            false,
            typeof(T).GetMethod("op_Equality")),
        new ParameterExpression[] { pe }));

      return (x);
    }
  }
}

And then I can use it with something like;

static void Main(string[] args)
    {
      NorthwindDataContext ctx = new NorthwindDataContext("server=.;database=northwind");

      var query = from o in ctx.Orders
                  where o.Customer.Country == "UK"
                  select o;

      int? empId = 6;

      query = query.AddEqualityCondition("CustomerID", "AROUT");
      query = query.AddEqualityCondition("EmployeeID", empId);

      foreach (Order o in query)
      {
        Console.WriteLine(o.OrderID);
      }
    }

Now, with all of that said, I’m pretty sure that I’m still glossing over a lot of complexity if you really wanted to make AddEqualityCondition a general purpose method but at least you get the idea that this kind of thing can be done even if I don’t perhaps have the perfect way of doing it.