LINQ to SQL Where Clause Optional Criteria

Question

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.

1. Product Number - Comes from another table that can be joined to TagsHeaders.
2. PO Number - a field within the TagsHeaders table.
3. Order Number - Similar to PO #, just different column.
4. 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!

Answer 1

Check here Roscoe, it may have been answered already.. http://stackoverflow.com/questions/11194/conditional-linq-queries

Answer 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.

Answer 3

You have the ability to OR with ||.

Check out this thread, as it might give you some nice pointers: http://stackoverflow.com/questions/403505/c-linq-equivalent-of-a-somewhat-complex-sql-query

Answer 4

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

Answer 5

@Andrew and all interested. Yea, I like this WhereIf() extender too and enjoy it as well but somehow in certain situations it doesn't work. Here is a scenario: My case when I need to apply this method in a query that has join and WhereIf() is present in both outer and in inner parts of the query:

 
var qry = DC.OuterTable
            .WhereIf(condOuter, o => o.someFld == condOuter)
            .Join(
                  DC.InnerTable
                    .WhereIf(condInner, i => i.someFld == condInner)
                 ,o => o.ID
                 ,i => i.refID
                 ,(o,i) => o.ID
                  ).Count();

When I run the query I get a run time exception(provided that both parameters condOuter and condInner are true) Kind of: Member access 'Int32 condInner' of 'tbl.InnerTable' not legal on type 'System.Linq.IQueryable`1[tbl.InnerTable]. Does anybody know the explanation to this behavior?