LINQ to SQL Where Condition Optional

Question

I am working with a LINQ to SQL query and run into a problem when I have 4 optional fields to filter the result of the data. By choice, I mean whether the choice is to enter a value or not. In particular, several text fields that may have a value or have an empty string and several drop-down lists that could have a value, selected or, possibly, 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 provided by the user.

• Product number - comes from another table that can be attached to TagsHeaders devices.
• PO Number - field inside the TagsHeaders table.
• Order number - similar to PO #, only another column.
• Product status. If the user has selected this from the drop-down list, it is necessary to apply the selected value here.

The query that I already have works fine, but to complete the function you need to be able to add these 4 other elements to the where clause, I just don’t know how to do it.

Answer 1

You can program 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 the condition, add additional restrictions.

 if(condition) query = query.Where(i => i.PONumber == "ABC"); 

I'm not sure how to encode this with the query syntax, but id works with lambda. Also works with query syntax for the original query and lambda for the secondary filter.

You can also include an extension method (below), which I encoded a while ago to include a condition that contains statements. (Doesn't work with 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); 

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; } 

Answer 2

You just need to use conditional checking for the existence of the parameter. For example:

 where (string.IsNullOrEmpty(ProductNumber) || ProductNumber == tags.productNumber) 

Thus, if the product number is not entered, the expression will return true in all cases, but if it is entered, it will return true only when matching.

Answer 3

You have the option of OR with ||.

Check out this thread, as it can give you some good pointers: C # LINQ equivalent to a somewhat complex SQL query