How to use whereif in LINQ

Question

Hi can someone help me how best we can use whereif in LINQ

IQueryable<Employee> empQuery;
     if (empId  == "")
     {
         empQuery = dbContext.Emps
           .Include(x => x.Name)
           .Include(x => x.Code)
           .Where(x => x.Id == empId);
     }
     else
     {
        empQuery = dbContext.Emps
           .Include(x => x.Name)
           .Include(x => x.Code);
     }

I think we can make this query very simple by using whereif right? Can someone help me how to make this query simple by using whereif? Instead of check if (empid == "") ?

Is it possible?

Solution 1

I believe you want filtering by empId if it is not empty. Simple OR operator will do the job:

IQueryable<Employee> empQuery = dbContext.Emps
           .Include(x => x.Name)
           .Include(x => x.Code)
           .Where(x => empId == "" || x.Id == empId);

Also you can build query dynamically:

IQueryable<Employee> empQuery = dbContext.Emps
           .Include(x => x.Name)
           .Include(x => x.Code);

if (empId != "")
    empQuery = empQuery.Where(x => x.Id == empId);

Solution 2

I assume "whereif" is supposed to be this extension method. You can't use that, because it operates on an IEnumerable<T> and not on a IQueryable<T>. The result would be that you would request your complete employees table from the database and perform the filtering in memory in your application. That's not what you want. You can, however, use the conditional operator to achieve this:

var empQuery = dbContext.Emps
                        .Include(x => x.Name)
                        .Include(x => x.Code)
                        .Where(x => empId == "" ? true : x.Id == empId);

Please note that this assumes that you actually meant if(empId != "") in your sample code. If you didn't mean this, switch the second and third operand around:

.Where(x => empId == "" ? x.Id == empId : true);

Having said this, you certainly can create the same extension method for IQueryable<T>. It looks nearly the same, just has IEnumerable<T> replaced with IQueryable<T> and the predicate changed to an expression:

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

Comments

[Daniel Hilgarth]

I guess you mean if (empId != ""). Otherwise it doesn't make a whole lot of sense.

[Kaf]

Your first query does the opposite of the requirement. isn't it?

[Daniel Hilgarth]

@Kaf: Indeed, because the requirement doesn't seem to make. I assumed it was a simple typo. Please also see my comment on the question itself. However, I updated my answer to be more explicit about it.

[Joachim Isaksson]

I'm not sure if EF has become better (probably) but it used to generate rather nasty SQL for constant expressions (ie empId == "" where empId is known). I'd go for the second option, which is basically what the WhereIf extension does anyway.

[Sergey Berezovskiy]

@JoachimIsaksson agree, second approach is better in terms of generated SQL. Btw I wonder why empId parameter passed two times if we use condition in where predicate WHERE @p__linq__0 = N'''' OR [Extent1].[Id] = @p__linq__1'

[Joachim Isaksson]

If I had to venture a guess, it'd be too much work and not enough effect to eliminate the common expressions between expression tree branches. I just wish it'd at lest collapse the branches that are constant without passing them to the database.