Only include in where condition if value is not null in Linq

2
votes

I have a long sql in which there are multiple where conditions. I want one where condition to only be considered if the value passed is not null

Linq

from b in _context.customer
           join d in _context.Address on b.id equals d.Id 
           join e in _context.units on d.Id equals e.Id
           where e.unitNumber == valueProvided)

select new modelAddress
              {
               address= address
              }

value provided can be null so I want the where condition to not take e.unitNumber into account. Also this is just an example, actual query is really large

4
sqlentity-frameworklinq
Robert's answer is the correct one. You may not like it, but by extracting the conditional application so the check is done before a condition is passed to the SQL ensures an optimum query. The alternative is adding conditions like where e.unitNumber = (valueProvided != null ? valueProvided : e.UnitNumber) for every nullable condition. This passes these conditions to the SQL to work out, slowing down your query. Down-voting answer attempts simply because you don't agree with them is pretty poor form. – Steve Py

4 Answers

0
votes

If I'm understanding well what you want to do, I think that you can validate that property after you do the Linq operation, right?

If not, you can do something like this:

from b in _context.customer
           join d in _context.Address on b.id equals d.Id 
           join e in _context.units on d.Id equals e.Id
           where valueProvided.HasValue && e.unitNumber == valueProvided)

select new modelAddress
{
   address= address
}

Do you need to use that Linq ou can you use Linq to SQL?

Because with link to SQL you can use the following code:

list.Where(w => w.value != null).Where(anotherPredicate);

Or 

list.Where(w => w.value.HasValue && anotherPredicate);

Or

list.FindAll(f => f.HasValue).Where(wherePredicate);
0
votes

Do your base query (this looks like these should really be navigation properties):

var result = from b in _context.customer
       join d in _context.Address on b.id equals d.Id 
       join e in _context.units on d.Id equals e.Id

Add in your criteria filters:

if (valueProvided != null)
    result = result.Where(e=>e.unitNumber == valueProvided);
if (valueProvided2 != null)
    result = result.Where(e=>e.somethingelse = valueProvided2);
...

Add in your pagination (if any):

result = result.Skip(pagesize * (currentPage-1)).Take(pagesize);

Do your projection:

var final = result.Select(r=>new modelAddress
          {
           address= r.address
          });
0
votes

I don't know your all query and why do you need to filter like that. However I advice a method for it.

var filters = new List<Func<Customer, bool>>();
if (valueProvided != null) {
    filters.Add(a => a.unitNumber == valueProvided );
}

var filteredResult = new List<Customer>();
if (filters.Count == 0) {
    filteredResult = _context.customer.Select(s=>new modelAddress{ address=s.address }).ToList();
} else if (filters.Count == 1) {
    filteredResult = _context.customer.Where(filters[0]).Select(s=>new modelAddress{ address=s.address }).ToList();
} else {
    Func<Customer, bool> predicate = filters[0];
    for (int i = 1; i < filters.Count; i++) {
        //I use 'and' here but if you want to use 'or', it is also ok.
        predicate = customer=> predicate(customer) && filters[i](customer);
    }
    filteredResult = _context.customer.Where(predicate).Select(s=>new modelAddress{ address=s.address }).ToList();
}

I convert this code block from my context and work well. I see on sql profiller only what I need.

0
votes

If you go by traditional query approach you will find the limitation in clauses that you can apply depending on the value you received, I would suggest to use dynamic linq expression binding.

You can check this blog for further details . http://tomasp.net/blog/dynamic-linq-queries.aspx/

I would recommend not go with traditional if else or switch case as this will increases complexity as system grows