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!
Best Answer
You can code your original query:
And then based on a condition, add additional where constraints.
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):
The extension method:
Here is the same extension method for IEnumerables: