Working with multiple, optional query parameters in LINQ

Especially when developing interactive reporting solutions, the chances are you need to create LINQ queries with optional query parameters. For instance, say you were working with a database similar to the Northwind database, and needed to list orders based on different selections.

Let's say the user of your application could enter a city, region and country of filtering values for the order list. However, none of the fields is mandatory (in which case, every order would be listed), but on the other hand, all parameters could be entered simultaneously.

In case of a single query parameter, it would be easy to run a LINQ query similar to the following:

NorthwindEntities entities = new NorthwindEntities();
string cityFilter = "London";
string countyFilter = "United Kingdom";
var orders = from o in entities.Orders
             where o.ShipCity == cityFilter &&
             o.ShipCountry == countyFilter
             select o;

However, if you had three possibly optional parameters, you could either try to play with NULL values and somewhat more complex WHERE statement, or you could take the simple but ineffective approach with different If statements like this:

NorthwindEntities entities = new NorthwindEntities();
string cityFilter = ""; // value or null
string regionFilter = "";
string countryFilter = "";
if ((cityFilter != null) && (regionFilter != null) && (countryFilter != null))
{
  var orders = from o in entities.Orders

               where (o.ShipCity == cityFilter) &&
               (o.ShipRegion == regionFilter) &&
               (o.ShipCountry == countryFilter)
               select o;
}
else if ((cityFilter != null) &&
         (regionFilter != null) &&
         (countryFilter == null))
{
  var orders = from o in entities.Orders
               where (o.ShipCity == cityFilter) &&
               (o.ShipRegion == regionFilter)
               select o;
}
else if (...

As you can see, this works okay, but gets very tedious very quickly. For two parameters, this approach might still work, but for three or more, no thank you. Luckily, there is a better solution.

The thing you need to remember about LINQ that it contains a lazy execution model. This means that the results are only fetched from the database when you start to enumerate ("touch") the query results. Thus, you can also combine query operators without needing to worry about that multiple queries are executed against the database.

To do this, you need to switch to the method-based syntax of LINQ, and use the Where method with a proper lambda expression. In this case, your code with three optional parameters, you could write code like this:

NorthwindEntities entities = new NorthwindEntities();
string cityFilter = "London";
string regionFilter = "South";
string countryFilter = "United Kingdom";

var orders = entities.Orders.AsQueryable(); // select all orders
if (cityFilter != null)
{
    orders = orders.Where(o => (o.ShipCity == cityFilter));
}
if (regionFilter != null)
{
    orders = orders.Where(o => (o.ShipRegion == regionFilter));
}
if (countryFilter != null)
{
    orders = orders.Where(o => (o.ShipCountry == countryFilter));
}

// done
return orders;

With this approach, the code is greatly simplified, and you do not need to worry about introducing more optional parameters to the mix. Here is the SQL query that would be executed if only a single country filter is given:

exec sp_executesql N'SELECT 
1 AS [C1], 
[Extent1].[OrderID] AS [OrderID], 
[Extent1].[OrderDate] AS [OrderDate], 
[Extent1].[RequiredDate] AS [RequiredDate], 
[Extent1].[ShippedDate] AS [ShippedDate], 
[Extent1].[Freight] AS [Freight], 
[Extent1].[ShipName] AS [ShipName], 
[Extent1].[ShipAddress] AS [ShipAddress], 
[Extent1].[ShipCity] AS [ShipCity], 
[Extent1].[ShipRegion] AS [ShipRegion], 
[Extent1].[ShipPostalCode] AS [ShipPostalCode], 
[Extent1].[ShipCountry] AS [ShipCountry], 
[Extent1].[CustomerID] AS [CustomerID], 
[Extent1].[EmployeeID] AS [EmployeeID], 
[Extent1].[ShipVia] AS [ShipVia]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[ShipCity] = @p__linq__1',
N'@p__linq__1 nvarchar(6)',@p__linq__1=N'London'

And here is the SQL query if all optional parameters are given:

exec sp_executesql N'SELECT 
1 AS [C1], 
[Extent1].[OrderID] AS [OrderID], 
[Extent1].[OrderDate] AS [OrderDate], 
[Extent1].[RequiredDate] AS [RequiredDate], 
[Extent1].[ShippedDate] AS [ShippedDate], 
[Extent1].[Freight] AS [Freight], 
[Extent1].[ShipName] AS [ShipName], 
[Extent1].[ShipAddress] AS [ShipAddress], 
[Extent1].[ShipCity] AS [ShipCity], 
[Extent1].[ShipRegion] AS [ShipRegion], 
[Extent1].[ShipPostalCode] AS [ShipPostalCode], 
[Extent1].[ShipCountry] AS [ShipCountry], 
[Extent1].[CustomerID] AS [CustomerID], 
[Extent1].[EmployeeID] AS [EmployeeID], 
[Extent1].[ShipVia] AS [ShipVia]
FROM [dbo].[Orders] AS [Extent1]
WHERE ([Extent1].[ShipCity] = @p__linq__1) AND
([Extent1].[ShipRegion] = @p__linq__2) AND
([Extent1].[ShipCountry] = @p__linq__3)',
N'@p__linq__1 nvarchar(6),@p__linq__2 nvarchar(5),
@p__linq__3 nvarchar(14)',@p__linq__1=N'London',
@p__linq__2=N'South',@p__linq__3=N'United Kingdom'

The statement might not be absolutely the most effective one, but is very close, and thus works well in practice.

Happy LINQing!