LINQ to SQL Where Clause Optional Criteria
Solution 1
You can code your original query:
var query = from tags in db.TagsHeaders
where tags.CST.Equals(this.SelectedCust.CustCode.ToUpper())
&& Utility.GetDate(DateTime.Parse(this.txtOrderDateFrom.Text)) <= tags.ORDDTE
&& Utility.GetDate(DateTime.Parse(this.txtOrderDateTo.Text)) >= tags.ORDDTE
select tags;
And then based on a condition, add additional where constraints.
if(condition)
query = query.Where(i => i.PONumber == "ABC");
I am not sure how to code this with the query syntax but id does work with a lambda. Also works with query syntax for the initial query and a lambda for the secondary filter.
You can also include an extension method (below) that I coded up a while back to include conditional where statements. (Doesn't work well with the query syntax):
var query = db.TagsHeaders
.Where(tags => tags.CST.Equals(this.SelectedCust.CustCode.ToUpper()))
.Where(tags => Utility.GetDate(DateTime.Parse(this.txtOrderDateFrom.Text)) <= tags.ORDDTE)
.Where(tags => Utility.GetDate(DateTime.Parse(this.txtOrderDateTo.Text)) >= tags.ORDDTE)
.WhereIf(condition1, tags => tags.PONumber == "ABC")
.WhereIf(condition2, tags => tags.XYZ > 123);
The extension method:
public static IQueryable<TSource> WhereIf<TSource>(
this IQueryable<TSource> source, bool condition,
Expression<Func<TSource, bool>> predicate)
{
if (condition)
return source.Where(predicate);
else
return source;
}
Here is the same extension method for IEnumerables:
public static IEnumerable<TSource> WhereIf<TSource>(
this IEnumerable<TSource> source, bool condition,
Func<TSource, bool> predicate)
{
if (condition)
return source.Where(predicate);
else
return source;
}
Solution 2
Just need to use a conditional checking for the parameter's existence. For instance:
where (string.IsNullOrEmpty(ProductNumber) || ProductNumber == tags.productNumber)
That way if the product number isn't entered that expression will return true in all cases, but if it is entered it will only return true when matching.
Comments
-
RSolberg almost 2 years
I am working with a LINQ to SQL query and have run into an issue where I have 4 optional fields to filter the data result on. By optional, I mean has the choice to enter a value or not. Specifically, a few text boxes that could have a value or have an empty string and a few drop down lists that could have had a value selected or maybe not...
For example:
using (TagsModelDataContext db = new TagsModelDataContext()) { var query = from tags in db.TagsHeaders where tags.CST.Equals(this.SelectedCust.CustCode.ToUpper()) && Utility.GetDate(DateTime.Parse(this.txtOrderDateFrom.Text)) <= tags.ORDDTE && Utility.GetDate(DateTime.Parse(this.txtOrderDateTo.Text)) >= tags.ORDDTE select tags; this.Results = query.ToADOTable(rec => new object[] { query }); }
Now I need to add the following fields/filters, but only if they are supplied by the user.
- Product Number - Comes from another table that can be joined to TagsHeaders.
- PO Number - a field within the TagsHeaders table.
- Order Number - Similar to PO #, just different column.
- Product Status - If the user selected this from a drop down, need to apply selected value here.
The query I already have is working great, but to complete the function, need to be able to add these 4 other items in the where clause, just don't know how!
-
jlembke about 15 years+1 That's nice. I was trying to think of something similar earlier
-
RSolberg about 15 years+10 Thanks so much for the answer. Just what I was looking. I really appreciate you taking the time to do this.
-
andleer about 15 yearsNo prob. I love stuff like this.
-
andleer about 14 yearsGlad I was able to share such lasting joy!
-
andleer about 14 yearsRyan, still working at SJH / PeaceHealth. Loving my job. Great people. Are you on FB? We really should carry on this conversation elsewhere. arobinson / gmail.
-
DevMania over 12 years@andleer, man you are awesome, you dont know how many lost souls you have saved :)
-
andleer over 12 years@Ryan, glad to help out on this.
-
AidaM almost 9 years+1 I was trying to find an elegant solution to 'conditional where' in my case I had multiple joins and result returning records from a joined table. Worked like a charm!
-
cahit beyaz about 7 yearsGreat solution :)
-
Kacey Ezerioha about 6 years10 years on and still relevant!