Linq to SQL Where Clause Optional Criteria

LINQ to SQL Where Clause Optional Criteria

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

Adding optional where parameter to query based linq query

Read : Dynamic query with Linq

I suggest make use of PredicateBulder and add predicate to you query , like as below , this will build query dynamically with variable predicate

var predicate = PredicateBuilder.True<Transaction>();  
predicate = predicate.And(t=>t.Id == transactionId);  
if (!string.IsNullOrEmpty(department))  
{  
    predicate = predicate.And(tran => tran.Dept!= "AllDept");  
}
var result = _ctx.Transactions.where(predicate);

Dynamically Composing Expression Predicates

Linq with optional WHERE options

Chain Where clauses with checking for null

var result = context.MyTable
    .Where(t => color == null || color == t.Color)
    .Where(t => size == null || size == t.Size)
    .Where(t => name == null || name == t.Name)
    .ToList();

Alternative approach would be to add conditions only when you need them

var query = context.MyTable;

if (color != null) query = query.Where(t => t.Color == color);
if (size != null) query = query.Where(t => t.Size == size);
if (name != null) query = query.Where(t => t.Name == name);

var result = query.ToList();

how to pass optional parameter in linq where condition

According to the information you have provided

Change you below statement:

var QueryData = query.Where(s => s.MSO == ms && (FromDate != null && (s.TEST_DATE.Value >= FromDate)) && (ToDate!=null && (s.TEST_DATE.Value<=ToDate))).ToList();

To

var QueryData = query.Where(s => s.MSO == ms && (FromDate == null || (s.TEST_DATE.Value >= FromDate)) && (ToDate == null || (s.TEST_DATE.Value<=ToDate))).ToList();

If the FromDate Or ToDate will be equal to NULL, it won't check them against s.TEST_DATE.Value.

LINQ and optional parameters

The parameters of the method could accept null values and the Where restriction could be evaluated for each not null parameter:

IQueryable<Product> q = databaseObject.Products;

if (catId != null)
{
    q = q.Where(p => p.Category == catId);
}
if (brandId != null)
{
    q = q.Where(p => p.Brand == brandId);
}
// etc. the other parameters

var result = q.ToList();

How can I make this LINQ to SQL where clause with conditions faster?

I've never used this type of syntax much so I'm sure if you can do it this way, but you can certainly do it with LINQ and build up your query step by step like so:

var query = _libraryContext.Set<Book>();

if(!string.IsNullOrWhiteSpace(bookFilter.Name))
{
    query = query.Where(x => x.Name.Contains(bookFilter.Name.ToUpper()));
}

if(!string.IsNullOrWhiteSpace(bookFilter.Description))
{
    query = query.Where(x => x.Description.Contains(bookFilter.Description.ToUpper()));
}

if(bookFilter.BookId > 0)
{
    query = query.Where(x => x.BookId == bookFilter.Id);
}

return query.Count();

Note: I have omitted the JOIN here as it seems unnecessary, but you can of course do the join in this syntax too if you need it.

Ignoring null parameter in where clause linq to sql

You can add those conditions to the query. It won't make for the most readable SQL, but assuming you find readable code more important and trust SQL Server's optimizer:

members = db.Members.Where(x =>
    (package == null || x.PackageName == package) &&
    (packageStatus == null || x.PackageStatus == packageStatus) &&
    (branch == null || x.Branch == branch)
).ToList();
        

Alternatively, you could conditionally append Where()s to a variable of type IQueryable<Member>. See for example entity framework: conditional filter.

---

Related Topics