Linq

Linq Utilities #1: Expandable Queries

First of all, we will need an extension that I wrote some time ago, which allows us to “call functions in LINQ queries” as demonstrated in the following example:

 1: // Lambda expression that calculates the price
 2: Expression<Func<Nwind.Product, decimal?>> calcPrice = 
 3:   (p) => p.UnitPrice * 1.19m;
 4: 
 5: // Query that selects products 
 6: var q = 
 7:   from p in db.Products.ToExpandable()  
 8:   where calcPrice.Expand(p) > 30.0m
 9:   select new { 
10:     p.ProductName, 
11:     OriginalPrice = p.UnitPrice,
12:     ShopPrice = calcPrice.Expand(p) };

Dynamic Queries #1: Selecting Customers

All right, let’s finally look how we can build a LINQ query dynamically at runtime! In the first example we will implement one feature that may be quite common in a lot of applications. We will build a query for selecting customers from the Northwind database and we will let user to choose what property of the Customer type he wants to enter. For simplicity we will give a choice between CompanyName, Country and a ContactName. Once the user selects what property he wants to query, we will ask for the value and run a query that will return all customers whose property selected by the user contains the entered value (as a substring). To implement this we first create a data context and then we write a function that will build a LINQ query once it gets a value entered by the user and a lambda expression representing a function that reads the selected property from of the customer. It may sound a bit confusing, but I’ll explain everything soon:

1: NorthwindDataContext db = new NorthwindDataContext();
2: 
3: var queryBuilder = Linq.Func
4:   ((Expression<Func<Customer, string>> selector, string val) =>
5:       from c in db.Customers.ToExpandable()
6:       where selector.Expand(c).IndexOf(val) != -1
7:       select c);

Before digging deeper, let’s look how the queryBuilder function can be used:

1: var q = queryBuilder(c => c.CompanyName, "ab");
2: foreach (var cust in q)
3:   Console.WriteLine(cust);

So what exactly happens here? We built a function (or maybe parameterized query would be better name) called queryBuilder that has two arguments. The second argument is a value that the user entered and the first argument is a lambda expression (named selector) that specifies what property of the Customer type we want to look for – when we give it an anonymous method that returns company name of a customer as an argument it will build a query that returns all customers with the specified company name.

When you run the code, it executes the queryBuilder function with the lambda expression reading company name and a value “ab”. The queryBuilder then returns a query that calls the lambda expression using the Expand method which I talked about earlier (in Linq Utilities #1). It is also important to note that the query uses the ToExpandable method, because this has to be present anywhere where we want to use Expand to call some lambda expression in a LINQ query.
Let’s now finish the sample and write a code that actually allows user to choose how he wants to query the Customers data table:

 1: var dict = new Dictionary<string, Expression<Func<Customer, string>>> 
 2:   { { "CompanyName", c => c.CompanyName },
 3:     { "Country",     c => c.Country },
 4:     { "ContactName", c => c.ContactName } };
 5: 
 6: // Get the input from the user
 7: Console.Write("Filter - field name (CompanyName, ContactName, Country):\n> ");
 8: string field = Console.ReadLine();
 9: 
10: Console.Write("Filter - value:\n> ");
11: string value = Console.ReadLine();
12: 
13: // Build the query using query builder and run it
14: var q = queryBuilder(dict[field], value);
15: foreach (var cust in q)
16:   Console.WriteLine(cust);

The application (just a simple console program) creates a dictionary that contains string as a key and a lambda expression for reading specific properties of a customer as a value. When user enters a string representing the property, it looks it up in the dictionary and gets a lambda expression that can be given as an argument to our parameterized query declared earlier. And that’s all! You could very easily use the same principle in a Windows Forms applications and let user select the property for example from a drop down list, which is probably the most common scenario for building LINQ queries at runtime.

Dynamic Queries #2: Combining Functions Using “OR” and “AND”

In this example, we will write slightly more complex application that builds LINQ query dynamically. It will allow user to choose if he wants to combine several criteria using AND or OR logical operator and then enter the values to find for every property (again, we use Customer table from a Northwind database). This means that the user we will be able to enter queries like (CompanyName = "Google") OR (Country = "Czech Republic") or (CompanyName = "Microsoft") AND (Country = "USA"). The key aspect in this example is that query is combined from several basic conditions without knowing the number of these conditions in advance, so this principle could be used when you want to allow the user to add any number of conditions he wants.

We will again need a dictionary for mapping string (entered by the user) to a lambda expression returning the property of the Customer object. To make the code a bit more readable then in the previous example we will define an abbreviation for this rather complex type, which can be done thanks to the C# using construct (unfortunately there are a few limitations, so we have to use fully qualified namespace names):

1: using CustomerPropSelector = System.Linq.Expressions
2:   .Expression<System.Func<EeekSoft.LinqDemos.Customer, string>>;

Now we can use the CustomerPropSelector type as a value in a dictionary (it is actually the same dictionary as the one we used in the previous example):

1: NorthwindDataContext db = new NorthwindDataContext();
2: var dict = new Dictionary<string, CustomerPropSelector> 
3:    { { "CompanyName", c => c.CompanyName },
4:      { "Country",     c => c.Country },
5:      { "ContactName", c => c.ContactName } };

The next type abbreviation that we will use in the code will represent a condition telling whether specified customer matches the specified criteria or not. The condition is a lambda expression taking a Customer object as an argument and returning a boolean value:

1: using CustomerCondition = System.Linq.Expressions
2:   .Expression<System.Func<EeekSoft.LinqDemos.Customer, bool>>;

We can now define two primitive conditions that we will need later - to define a condition we use the C# anonymous function syntax, so you can easily write a condition that for example tests whether a customer is from UK or something like that. The two primitive conditions that we need are however really trivial - one of them returns true for every customer and the other false:

1: CustomerCondition trueCond  = (c) => true;
2: CustomerCondition falseCond = (c) => false;

Now, we will need a way for combining two conditions, so we can write a combinator that can be used to produce a condition from two of them. We will use two such combinators later. One will be OR which takes two conditions and returns true when any of them returns true and the other will be AND which will return true when both conditions return true, as you would expect. The interesting is the type of these combinators - it is a function taking two conditions and returning a condition as a result, so we could write them as Func<CustomerCondition, CustomerCondition, CustomerCondition>. Unfortunately, the C# using statement doesn't allow using nested abbreviations, so we have to replace CustomerCondition in the previous type and we'll get somewhat ugly type:

1: using CustomerConditionCombinator = 
2:   System.Func<System.Linq.Expressions.Expression<System.Func<EeekSoft.LinqDemos.Customer, bool>>, 
3:               System.Linq.Expressions.Expression<System.Func<EeekSoft.LinqDemos.Customer, bool>>, 
4:               System.Linq.Expressions.Expression<System.Func<EeekSoft.LinqDemos.Customer, bool>>>;

Despite the fact that C# forces us to write this in a really ugly way, it isn't that difficult to understand what it actually does - it just takes two conditions (which are actually lambda expressions returning a boolean value when given Customer as an argument) and returns a condition that can call these two conditions and produces boolean as a result, very likely by performing some logical operation on the values returned by these two conditions. We can now write two basic combinators for logical OR and AND. The combinator receives two conditions (f and g) as arguments, and returns a new lambda expression as the result. This lambda expression takes a Customer value as an argument, calls the two conditions using the Expand method and performs the logical operation on the result:

1: CustomerConditionCombinator combineOr = 
2:   (f, g) => (c) => f.Expand(c) || g.Expand(c);
3: 
4: CustomerConditionCombinator combineAnd = 
5:   (f, g) => (c) => f.Expand(c) && g.Expand(c);

Now we have everything we need to finally start building the query! Let me first demonstrate how we can build a query using the combineOr combinator from two simple conditions, one testing whether the customer is from UK and the other testing if it's from Seattle:

 1: // Define two simple conditions first
 2: CustomerCondition isUk = (c) => c.Country == "UK";
 3: CustomerCondition isSeattle = (c) => c.City == "Seattle"
 4: 
 5: // Combine the conditions using OR
 6: CustomerCondition expr = combineOr(isUk, isSeattle);
 7: 
 8: // Build a query (using 'Expand' and 'ToExpandable')
 9: var q = from c in db.Customers.ToExpandable()
10:         where expr.Expand(c) select c;

If you run this example, it will indeed return all customers that are either from UK or live in Seattle. Note that we still have to use the Expand to call the condition we built in the LINQ query and it is also important not to forget the ToExpandable call, otherwise the “LINQ to SQL” will give an error message that it doesn't understand the query.
Let's now move to the final example - here we will actually let user construct a query he wants. Let's assume we have a value generateOr saying if we want to build a query by combinating several conditions using OR or using AND. To build a query we will start with one of our primitive conditions and add a condition using the combinator for every property of the customer. This means that when combining conditions using OR we will start with a query representing "false", than we will append first condition and get something like "(false || cond1)", after adding second condition we will get "(false || cond1) || cond2" and so on. You can see that when building a sequence of ORs we need to start with falseCond, so that the condition returns true only when any of the conditions succeeds and when building sequence of ANDs, we have to start with trueCond, so that the condition succeeds when all conditions return true.

The code to build a condition in the way described in the previous paragraph can be written as a foreach loop (working with the KeyValuePair from the dictionary declared above) that modifies the variable holding the current expression (expr) in every iteration:

 1: // Select the combinator and initial condition
 2: CustomerConditionCombinator combinator = 
 3:   generateOr ? combineOr : combineAnd;
 4: CustomerCondition expr = 
 5:   generateOr ? falseCond : trueCond;
 6:   
 7: // Loop over all properties in the dictionary  
 8: foreach (var item in dict)
 9: {
10:   // Read the value from the user
11:   Console.Write("Enter value for '{0}':\n> ", item.Key);
12:   string enteredVal = Console.ReadLine();
13:   CustomerPropSelector propSelector = item.Value;
14:   
15:   // Build a condition testing whether entered value is a substring
16:   CustomerCondition currentCond = (c) => 
17:     propSelector.Expand(c).IndexOf(enteredVal) != -1;
18:     
19:   // Finally, combine expressions using the combinator
20:   expr = combinator(expr, currentCond);
21: }

Since C# 3.0 and LINQ in general use some of the concepts from functional programming, we can actually used one nice functional trick to write the code in a slightly shorter and less error-prone way. We can use a query operator called Fold (it was part of the LINQ in earlier version, but it seems to be missing now, so I added it to my LINQ Extensions [3] project). What does this operation do? It takes some initial value and runs a specified function to combine the value with every element in the sequence step by step. In our case the initial value is a condition (either trueCond or falseCond) and the function to combine values is essentially the body of the foreach loop. This means that function will ask user for the value and return the value newly produced by the combinator:

 1: CustomerConditionCombinator combinator = 
 2:   generateOr ? combineOr : combineAnd;
 3: 
 4: // Build expression using the 'Fold' aggregate operator
 5: var expr = dict.Fold((e, item) => {
 6:     // Read the value from the user
 7:     Console.Write("Enter value for '{0}':\n> ", item.Key);
 8:     string enteredVal = Console.ReadLine();
 9:     CustomerPropSelector propSelector = item.Value;    
10:     
11:     // Build a condition testing whether entered value is a substring
12:     CustomerCondition currentCond = (c) => 
13:       propSelector.Expand(c).IndexOf(enteredVal) != -1;
14:       
15:     // Finally, return the combined expression
16:     return combinator(e, currentCond); 
17:   }, generateOr ? falseCond : trueCond);

Finally, we can use a simple LINQ query to run the generated condition (expr variable) - as mentioned in other places of this article, we can call it using Expand and we shouldn't forgot ToExpandable call which processes our combined expression before calling the “LINQ to SQL” translator:

1: var q = from c in db.Customers.ToExpandable()
2:         where expr.Expand(c) select c;