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.

Advertisements

2 thoughts on “Writing LINQ to SQL select queries with optional where arguments

  1. MyDataContext db = new MyDataContext();
    IQueryable customers = from c in db.Customers
    where (object!=null) ? c.City == object.city : true
    select c;
    object is a object of somkind. If object not equal to null then only I want ot compare c.city to object.city but I am getting exception.

    Can you tell me the reason ..

  2. _visContenidos = (from c in contexto.visContenidos
    where (v_idTiposContenidos != 0) ? c.idTiposContenidos == v_idTiposContenidos : true
    select c).ToList();

    This return the sql:
    {SELECT [t0].[id], [t0].[nombre], [t0].[idTiposContenidos], [t0].[idPais], …..
    FROM [dbo].[visContenidos] AS [t0]
    WHERE (
    (CASE
    WHEN @p0 = 1 THEN
    (CASE
    WHEN [t0].[idTiposContenidos] = @p1 THEN 1
    WHEN NOT ([t0].[idTiposContenidos] = @p1) THEN 0
    ELSE NULL
    END)
    ELSE @p2
    END)) = 1
    }

    ….To large

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s