LINQ to SQL Where Clause Optional Criteria
Often times you may allow the users to query your system and you give them the choice of which fields to use in their query. This entry covers how to use optional where criteria within your query and avoid having to write 12 versions of the same query with different where clauses!
Let’s take a real example of what I’m talking about… Suppose you have the following basic LINQ query.
Product Number – Comes from another table that can be joined to ItemsHeaders.
PO Number – a field within the ItemsHeaders table.
Order Number – Similar to PO #, just different column.
Product Status – Apply if the user selected this from a drop down.In order to accomplish this task, you need to create a couple of extension methods that allow you to apply a where filter if a condition is met.
I must give special thanks to Andrew Robinson for assisting me with this code and functionality.
|
1 2 3 4 5 6 |
using (ItemsModelDataContext db = new ItemsModelDataContext()) { var query = from items in db.ItemsHeaders select tags; this.Results = query.ToADOTable(rec => new object[] { query }); } |
IQueryAble
|
1 2 3 4 5 6 7 |
public static IQueryable WhereIf(this IQueryable source, bool condition,Expression> predicate) { if (condition) return source.Where(predicate); else return source; } |
IEnumerable
|
1 2 3 4 5 6 7 |
<pre class="brush:csharp">public static IEnumerable WhereIf(this IEnumerable source, bool condition, Func predicate) { if (condition) return source.Where(predicate); else return source; } |
New Query!
|
1 2 3 4 5 6 7 8 9 |
<pre class="brush:csharp">using (ItemsModelDataContext db = new ItemsModelDataContext()) { var query = db.ItemsHeaders .WhereIf(true, items => items.PONumber == "ABC") .WhereIf(true, items => items.ProductNumber == "ABC123") .WhereIf(true, items => items.OrderNumber == "ORD184") .WhereIf(true, items => items.ProductStatus == "NEW") this.Results = query.ToADOTable(rec => new object[] { query }); } |