LINQ to SQL Where Clause Optional Criteria

by

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

More Related Contents:

  1. How to make LINQ execute a (SQL) LIKE range search
  2. Will using LINQ to SQL help prevent SQL injection
  3. How to display words that appear more than 'x' times in a text? [closed]
  4. Returning IEnumerable vs. IQueryable
  5. Return anonymous type results?
  6. Is there a good LINQ way to do a cartesian product?
  7. How can I conditionally apply a Linq operator?
  8. LINQ to SQL – Left Outer Join with multiple join conditions
  9. How to do a subquery in LINQ?
  10. Understanding .AsEnumerable() in LINQ to SQL
  11. TransactionScope vs Transaction in LINQ to SQL
  12. LINQ: combining join and group by
  13. How to write Asynchronous LINQ query?
  14. Am I misunderstanding LINQ to SQL .AsEnumerable()?
  15. How do you perform a CROSS JOIN with LINQ to SQL?
  16. The result of a query cannot be enumerated more than once
  17. Operation could destabilize the runtime?
  18. How to select only the records with the highest date in LINQ
  19. “The LINQ expression node type ‘Invoke’ is not supported in LINQ to Entities” – stumped!
  20. Cannot convert lambda expression to type ‘string’ because it is not a delegate type [duplicate]
  21. Find all child controls of specific type using Enumerable.OfType() or LINQ
  22. Custom Method in LINQ to SQL query
  23. LINQ Expression to return Property value?
  24. Max or Default?
  25. How do I do a left outer join with Dynamic Linq?
  26. Get sum of two columns in one LINQ query
  27. Select multiple fields group by and sum
  28. Explicit construction of entity type [MyClass] in query is not allowed
  29. Linq where column == (null reference) not the same as column == null
  30. Is Injection Possible through Dynamic LINQ?

Leave a Comment