The Grubbsian

There's been a lot of buzz lately around LINQ to Entities (aka Entity Framework).  Some of the buzz has been positive, a lot has been negative, but it does appear that Microsoft will invest a lot in LINQ to Entities moving forward.  On a recent side project I had occasion to use EF to connect to a SQLite database running locally within a web application.  It's an interesting little architecture and despite it's obvious scaling issues, it's working really well for the problem space.

Anyway, one of the features I needed was search…and not just any search, a very dynamic search.  The application is an inventory tracking system and it allows a user to set up different kinds of items and create a unique list of properties that each of those items can have.  For example you might create an item of type 'Widget' with two properties 'Size' and 'Color'.  Another type might be 'Bobble' and it has properties 'Width', 'Volume', and 'Friction Coefficient'.  So in order to search for these types I'm forced to dynamically construct a search screen.

This dynamic search screen is where the issue comes in.  Because I'm searching an indiscriminate number of properties and I don't know at compile time what those properties are, I need to execute some type of dynamic query.  Those of you who are familiar with LINQ will know that this is easier said than done.  It's not like I can just construct a SQL statement at runtime and then pass it to the database for execution.  Enter the System.Linq.Dynamic namespace.

I found this blog post by ScottGu on the exact subject.  (The post is labeled Part 1 but I haven't been able to find a Part 2 anywhere…if you find it let me know.)  His post will point you to where you can download the source for the System.Linq.Dynamic assembly which you'll need to build and include in your project.  I did see mentioned that this is part of .NET 3.5 SP1, however I was unable to find it on my system.

Once you have the library referenced it becomes quite a bit simpler to generate a query at runtime.  The library parses an expression syntax that's pretty simple.  The primary use for generating queries at runtime is going to be setting up WHERE conditions; you can however create an entire query dynamically if you need to.  Here's an example of a LINQ query as it would be written at design time:

var items = (from InventoryItem item in db.InventoryItem
             where item.Category.ID = 7
             select item).ToList<InventoryItem>();

This is a pretty innocuous query and doesn't really warrent being turned into a dynamic query but for example's sake, here's how you'd write it:

var items = db.InventoryItem.Where("Category.ID = @0", 7).ToList<InventoryItem>();

It's really that simple.  After you include System.Linq.Dynamic in your using statements a new overload of the Where method is available.  This new version of Where takes a string predicate (the where statement) and an array of objects that are parameters for the query string.

Because you don't have the benefit of type safety within the query string, you'll have to be careful with your syntax.  For example, when using a property of a property (item.Category.ID in the above example) you have to remember to use the names you've assigned to entities in your model not the names in the underlying data source.

There are other ways to create a query dynamically including using ObjectQuery<T>, or building up predicates in a pseudo-recursive way as in this post from the C# 3.0 in a Nutshell guys.  However, I find the dynamic query method here to be easy to understand and very powerful.