Writing LINQ to SQL select queries with optional where arguments

Firstly, I should say that there are ‘probably’ more elegant ways to do this (i.e. expression trees – which I can’t get my head around) and System.Linq.Dynamic (which I have downloaded and compiled but due to being up against an impending deadline, I’ve not had a chance to research).

Ok, caveat over.

I have a scenario where I need to write a LINQ to SQL select query where I have many search parameters but I’m not sure which ones will be supplied. This is a derivation of an old SQL pattern which I used in vanilla SQL many times to solve a similar problem.

Let’s say we are searching for a list of customers and we can filter (where clause) these results based on City, Type and Rep (just as an exercise). So a user can have the following parameter combinations:

• None supplied – returns all customers
• City supplied – returns all customers in a given City
• Type supplied – returns all customers of a certain Type
• Rep supplied – returns all customers assigned to a certain Sales Representative
• City and Type supplied – returns all customers in a given City of a certain Type
• City and Rep supplied – returns all customers in a given City assigned to a certain Sales Representative
• Type and Rep supplied – returns all customers of a certain Type assigned to a certain Sales Representative
• City and Type and Rep – returns all customers in a given City of a certain Type assigned to a certain Sales Representative

Ok, a bit long winded, but I wanted to be sure we were all clear on the scenario.

In pseudo code we can define the solution as:

SELECT all the rows
WHERE City is in some state
AND Type is in some state
AND Rep is in some state 

… but we only want to define a WHERE argument if the parameter is of some use to us (i.e. defined). The secret it to substitute any parameter not being used with the expression true. So in pseudo code when only City is defined our code looks like …

SELECT all the rows
WHERE City equals some value
AND true
AND true

.. where the ‘AND true’ elements substitute for missing values. Ok let’s see the code to make more sense of this. For the sake of the argument I am getting these values from three textboxes called txtCity, txtType and txtRep respectively.

MyDataContext db = new MyDataContext();
IQueryable<Customer> customers = from c in db.Customers
          where (txtCity.Text.Length != 0) ? c.City == txtCity.Text : true
          && (txtType.Text.Length != 0) ? c.Type == txtType.Text : true
          && (txtRep.Text.Length != 0) ? c.Rep == txtRep.Text : true
          select c;

We use the ternary operator in each line of the where clause to test for a useable value. If we find one we add a WHERE ‘portion’ for that parameter. If not we substitute ‘true’.

So there you go. I’ve not looked at the code this spits out yet (SQL). But then as I said before I’m up against a deadline.

Enjoy.

Advertisement