LINQ: adding where clause only when a value is not null

Question

I know a typical way is like this:

IQueryable query = from staff in dataContext.Staffs;
if(name1 != null)
{
     query = from staff in query where (staff.name == name1);
}

However, from a program we took over from other developers, we saw code like this:

IQueryable query = from staff in dataContext.Staffs;
query = from staff in query where (name1 == null || staff.name == name1);

If this is a normal SQL statement, I would definitely say that the 2nd one is a bad practice. Because it adds a meaningless where clause to the query when name1 is null.

But I am new to LINQ, so I am not sure if LINQ is different?

Underage

You could use the SQL Server Profiler to see what the query boils down to. – Offspring

In SQL, it doesn't really matter because "name1" is a known value at runtime and the query optimizer will optimize the WHERE clause away. As a matter of fact, you see this style in SQL all the time. The benefits of doing this in LINQ is for cleaner code. It may not change the SQL statement being generated by LINQ-to-SQL (it may already eliminate it), and it definitely will not affect performance for the SQL statement even if it stays there. – Heterogenous

So I was wrong at the very beginning...... Understood, thank you~~! – Underage

@StephenChung, I believed the same thing but I have found queries that take twice as long with (@p__linq__2 IS NULL OR [Extent3].[ZZZ] = @p__linq__3) than with [Extent3].[ZZZ] = @p__linq__3 when querying against a complex view – Mckamey

You may want to copy the two queries to SQL Mgmt Studio and look at the query plan generated. The second one may be able to use an index, which makes it much faster; although you can say that the first query should also be able to use the same index... It would be interesting to see what differences are there in the two query plans. – Heterogenous

But look at @Carson63000's answer: "if you have an index on the name column, don't expect it to be used." So in the first instance, the query optimizer just planly gives up and thinks it is a complex expression, to be evaluated using a full table scan, instead of being smart about the fact that one of the sub-expressions evaluates to a constant value at runtime and the other is an expression that can use an index, which is not hindered by the other sub-expression since that one already evaluates to a constant! It reflects on the quality of the optimizer. – Heterogenous

Check this out for the easiest and cleanest way to do this: https://mcmap.net/q/442121/-linq-adding-where-clause-only-when-a-value-is-not-null – Exhaustless

Entity Framework core removes the where clause from the generated SQL query if name1 == null. – Overbuild

Answer 1

you can write it like

IQueryable query = from staff in dataContext.Staffs;
query = from staff in query where (name1 != null && staff.name == name1);

This way second part of your condition will not be evaluated if your first condition evaluates to false

Update:
if you write

IQueryable query = from staff in dataContext.Staffs;
    query = from staff in query where (name1 == null || staff.name == name1);

and name1 is null second part of your condition will not be evaluated since or condition only requires one condition to return true

plz see this link for further detail

Coastward

If name1 is null, he wants all Staffs records returned: your rewriting will return nothing in that case. – Galla

Answer 2

Often this sort of thing feels smoother to write using the fluent syntax, rather than the query syntax.

e.g.

IQueryable query = dataContext.Staffs;
if(name1 != null)
{
     query = query.Where(x => x.name == name1);
}

So if name1 is null, you just don't do any Where() call. If you have multiple different filters, all of which may or may not be required, and perhaps various different sort orders, I find this becomes a lot more manageable.

Edit for alex: OK, I was answering the question about adding a where clause only when a value is not null. In response to the other part of the question, I tried this out with Entity Framework 4 to see what SQL that LINQ produced. You do this by casting query to an ObjectQuery and calling .ToTraceString(). The results were that the WHERE clause came out as follows:

WHERE @p__linq__0 IS NULL OR [Extent1].[name] = @p__linq__1

So, yes, it's classic bad SQL, if you have an index on the name column, don't expect it to be used.

Edit #2: Tried this again using LINQ to SQL rather than Entity Framework, with rather different results. This time, trying the query with name1 being null results in no WHERE clause at all, as you'd hope; trying it with name1 being "a" resulted in a simple WHERE [t0].[name] = @p0 and @p0 sent as "a". Entity Framework does not seem to optimize thus. That's a bit worrying.

Galla

This doesn't really seem to answer the question. – Offspring

I don't see how your answer is relevant at all. – Mutation

Sorry for not being clear. My question is: is the 2nd style a bad pracrice, from performance point of view? So Carson63000's answer seems to be what I want to know: yes, it's a bad practice. – Underage

Thanks for the edit :). Getting back to the question, the strange thing is, looking with LINQPad over the SQL queries that get generated in each case, they seem to be identical (I'm using a LINQ to SQL connection). – Offspring

Is it a coincidence that both @Offspring and I questioned the value of this answer, and then both had downvotes on previous answers within ~12 hours? Just wondering. – Mutation

@Kirk I stopped caring about such stuff a while back; it's not worth it to get pissed off about it. @Carson well, Entity Framework still has a bit of catching up to do compared with L2S in certain areas; it will get there, eventually. – Offspring

@Kirk - would you care to explain how this answer is not relevant? Do you have anything relevant to contribute yourself? – Galla

I can only explain how I read your answer, and highlight that it did not originally answer the question at all. You must've agreed, hence your edits. The question - and there is only one part to the question - is 'I am not sure if LINQ is different? (between the two code snippets)'. Your original answer recommended using 'fluent syntax' (not relevant), continued on to describe how the code will be executed (not answering the 'difference' question), and declared this was more manageble (again, not relevant). My comment was a hint that you might want to expand your answer. – Mutation

@Offspring I don't care, but I do think it's petty. If you can't handle legitimate criticism of your answer then you shouldn't be here. These are hardly harsh comments. – Mutation

It would be good to mention that EF core evaluates the where (name1 == null part before translating. If name1 == null the entire predicate is not added to the SQL query, if name1 != null only the predicate where (staff.name == name1) is added. This takes these query plan objections out of the equation and turns it into nothing but a question of preference. – Overbuild

Answer 3

The best way to do this is to create yourself an extension method that will take in a conditional statement and a where expression. If the condition is true then it will use the where expression else it will not use it. This can dramatically clean up your code, eliminating the need for if statements.

public static class LinqExtensions
{
    public static IQueryable<T> WhereIf<T>(this IQueryable<T> query, bool condition, Expression<Func<T, bool>> whereClause)
    {
        if (condition)
        {
            return query.Where(whereClause);
        }
        return query;
    }
}

Now you can write your code like this:

IQueryable<Staffs> query = dataContext.Staffs.AsQueryable().WhereIf(name1 != null, x => x.Name == name1);

Courageous

It's a REALLY nice idea but I get errors: Additional information: LINQ to Entities does not recognize the method Where..... and this method cannot be translated into a store expression. Sooo, useless for EF – Planimeter

I'm using this exact method with no errors. If you give me a little bit more information I could prob help you out. Make sure that you first turn your DbSet, IEnumerable, ICollection, or List into an IQueryable otherwise c# will not be able to find the WhereIf extension method. (Edit** I realized I forgot to call AsQueryable() in my example. I added it in. Hopefully that helps. Also I changed the name Where to WhereIf, figured it describes the method better.) – Courageous

The problem is that using Linq expressions on an Entity Framework uses a backend Linq to Entities mapper and every linq extension method used can only work if a supporting translation to TSQL (or language for whichever DBMS is in use) exists. Your extension method (which I called WhereIf as well) works fine on any internal type but against EF...no dice. I'v epasted my solution for working against EF as an answer – Planimeter

For sure, I've ran into that issue several times with different queries. However I am using EF6, which has a edmx file with all of the entity models in it. I use this trick in just about all my queries and it is able to translate it into t-sql; I can even print out the sql query it generates. (I also just tested it with Linq to SQL and it works). I doubt it matters but I am using Azure SQL, as opposed to SQL Server. Anyways I hope you figure it out or find a solution that works for you. If I can help let me know. -Cheers – Courageous

I'm also running EF6 but use POCO first. Maybe there's a difference... At any rate EF7 abandons edmx so looking to future unless it becomes easier to extend Linq to Entities it's still not really an option. My answer is posted above using a prebuilt Expression<Func<SomeEfPoco, bool>> predicate – Planimeter

Utterly brilliant, dude! You just helped me cut nearly 200 lines of if statements down to a total of 10 lines! Thank you!! Side note: I'm using it on a List<poco> retrieved from EF7Core, and it works spectacularly! – Erlineerlinna

Answer 4

So I tried the .Where(..., x => ...) extension method listed here as an answer but it doesn't work against Entity Framework as Linq To Entities doesn't know how to translate that into TSQL.

So here's my solution getting my Func on:

Expression<Func<SomeEfPoco, bool>> columnBeingFilteredPredicate = x => true; // Default expression to just say yes
if (!string.IsNullOrWhiteSpace(someColumnBeingFilteredValue))
{
    columnBeingFilteredPredicate = x => x.someColumnBeingFiltered == someColumnBeingFilteredValue;
}

_context.SomeEfPocos.Where(x => ..... &&
            ..... &&
            ..... &&)
.Where(columnBeingFilteredPredicate);

someColumnBeingFilteredValue in my case is a string parameter on the encapsulating method with a default value of NULL.

Planimeter

this is not applicable to this question or a representation of that extension.. both the question and extension operate under the premise that someColumnBeingFilteredValue is being used with a separate predicate. and the premise here is to NOT have a sql translation of a local variable conditional check; thus, not account for such a check or evaluate outside of the application. – Ostracism

Answer 5

LINQ is diffrent in some other causes (not in this causes), LINQ is the way to get data in the "Faster way" with a littel code and clear cod as possible, there a many benefits of LINQ:

1. Makes it easier to transform data into objects. I'm sure you've heard the term "Impedence Mismatch" being used quite often, meaning that LINQ reduces the amount of work you must do to translate between object-oriented code and data paradigms such as hierarchical, flat-file, messages, relational, and more. It doesn't eliminate the "Impedence Mismatch" because you must still reason about your data in its native form, but the bridge from here to there is (IMO) much shorter.

2. A common syntax for all data. Once you learn query syntax, you can use it with any LINQ provider. I think this is a much better development paradigm than the Tower of Babel that has grown over the years with data access technologies. Of course, each LINQ provider has unique nuances that are necessary, but the basic approach and query syntax is the same.

3. Strongly typed code. The C# (or VB.NET) query syntax is part of the language and you code with C# types, which are translated into something a provider understands. This means that you gain the productivity of having your compiler find errors earlier in the development lifecycle than elsewhere. Granted, many errors in stored proc syntax will generate errors when you save, but LINQ is more general than SQL Server. You have to think of all the other types of data sources that generate runtime errors because their queries are formed with strings or some other loosely typed mechanism.

4. Provider integration. Pulling together data sources is very easy. For example, you can use LINQ to Objects, LINQ to SQL, and LINQ to XML together for some very sophisticated scenarios. I think it's very elegant.

5. Reduction in work. Before LINQ, I spent a lot of time building DALs, but now my DataContext is the DAL. I've used OPFs too, but now I have LINQ that ships with multiple providers in the box and many other 3rd party providers, giving me the benefits from my previous points. I can set up a LINQ to SQL DataContext in a minute (as fast as my computer and IDE can keep up).

6. Performance in the general case doesn't become an issue. SQL Server optimizes queries quite well these days, just like stored procs. Of course, there are still cases where stored procs are necessary for performance reasons. For example, I've found it smarter to use a stored proc when I had multiple interactions between tables with additional logic inside of a transaction. The communications overhead of trying to do the same task in code, in addition to getting the DTC involved in a distributed transaction made the choice for a stored proc more compelling. However, for a query that executes in a single statement, LINQ is my preferred choice because even if there was a small performance gain from a stored proc, the benefits in previous points (IMO) carry more weight.

7. Built-in security. One reason I preferred stored procs before LINQ was that they forced the use of parameters, helping to reduce SQL injection attacks. LINQ to SQL already parameterizes input, which is just as secure.

8. LINQ is declarative. A lot of attention is paid to working with LINQ to XML or LINQ to SQL, but LINQ to Objects is incredibly powerful. A typical example of LINQ to Objects is reading items from a string[]. However, that's just a small example. If you think about all of the IEnumerable collections (you can also query IEnumerable) that you work with every day, the opportunities are plentiful. i.e. Searching an ASP.NET ListBox control for selected items, performing set operations (such as Union) on two collections, or iterating through a List and running a lambda in a ForEach of each item. Once you begin to think in LINQ, which is declarative in nature, you can find many of your tasks to be simpler and more intuitive than the imperative techniques you use today.

I could probably go on, but I'd better stop there. Hopefully, this will provide a more positive view of how you could be more productive with LINQ and perhaps see it as a useful technology from a broader perspective.

Dyke

Answer 6

I've seen this pattern in standard SQL, and it seems useful if you have several parameters that may be NULL. For example:

SELECT * FROM People WHERE ( @FirstName IS NULL OR FirstName = @FirstName )
                       AND ( @LastName IS NULL OR LastName = @LastName )

If you see this in LINQ, it's possible they just blindly translated their old SQL-queries.

Easiness

The problem with that pattern is that it is a poor fit for the query optimizer. If, for instance, there is an index on FirstName or LastName, it won't be used, since depending on the parameters passed in, the WHERE clause may or may not involve those columns of the table. – Galla

Answer 7

I like use the Expression e.g.

    Expression<Func<Persons, bool>> expresionFinal = c => c.Active == true;

    if (DateBirth.HasValue)
                {
                    Expression<Func<Persons, bool>> expresionDate = c => (EntityFunctions.TruncateTime(c.DateBirth) == DateBirth);
                    expresionFinal = PredicateBuilder.And(expresionFinal, expresionDate);
                }

IQueryable query = dataContext.Persons;
 query = query.Where(expresionFinal);

Seale

almost an answer.... should explain why you didn't do expresionDate = c => (EntityFunctions.TruncateTime(c.DateBirth) == DateBirth) || DateTime.HasValue here.. – Ostracism

I would not recommend that, it penalizes query performance. If the user does not inform to date does not filter if (DateBirth.HasValue) <-- Is a search parameter – Seale

Answer 8

For EF Core I broke it up like this:

IQueryable<Partners> recs = contextApi.Partners;
if (status != -1)
{
   recs = recs.Where(i => i.Status == status);
}
recs = recs.OrderBy(i => i.Status).ThenBy(i => i.CompanyName);
foreach (var rec in recs)
{
}

I had to be explicit with my typing instead of relying on var.

Potbelly

Answer 9

I like the idea with Extension

public static IQueryable<T> WhereIf<T>(this IQueryable<T> query, bool condition, Expression<Func<T, bool>> whereClause)
        => condition ? query.Where(whereClause) : query;

Bea

Answer 10

No, I am not strongly agree with you. here you just gave a simple logic

if(name1 != null)
// do your stuff

but what will happen if you do something different with the name1 that have null value..!! Ok, now consider this situation. In this example you shows how to handle possible null values in source collections. An object collection such as an IEnumerable<T> can contain elements whose value is null. If a source collection is null or contains an element whose value is null, and your query does not handle null values, a NullReferenceException will be thrown when you execute the query.

Probably this could be a issue...

Spaceless

Answer 11

I use the extension method below. It's less flexible than the WhereIf extension from the other answers, but it's shorter to use.

public static IQueryable<T1> FilterBy<T1, T2>(this IQueryable<T1> query, T2 expectedValue, Expression<Func<T1, T2>> propertyAccessor)
{
    if (propertyAccessor == null) throw new ArgumentNullException(nameof(propertyAccessor));
    if (expectedValue == null) return query;
    var equalExpr = Expression.Equal(propertyAccessor.Body, Expression.Constant(expectedValue, typeof(T2)));
    var lambda = Expression.Lambda<Func<T1, bool>>(equalExpr, propertyAccessor.Parameters);
    return query.Where(lambda);
}

It can be used like:

var query = dataContext.Staffs.FilterBy(name, s => s.Name);

Fidel

Answer 12

I use a ternary operator like this:

.Where(x =>
            data.value.IsNotNull()
                ? x.SomeValue == data.Value
                : true
        )

IsNotNull is a custom ObjectExtension.

 public static class ObjectExtensions
    {
        public static bool IsNotNull(this object obj)
        {
            return !obj.IsNull();
        }
    }

Anting

Retuning a hard-coded boolean value usually indicates that the predicate can be condensed. Which is the case here as well. But also, how does this answer the question? Did you read the entire question? – Overbuild

@GertArnold None of the solutions posted here fit me or worked for my use-case. So can you post your solution, because I don't see it here. – Anting

You are seemingly looking for a solution for something. However, the question is not about solutions, it's about explanations. Your answer doesn't belong here (just as many of the other answers). That said, the predicate can be condensed to data.value == null || x.SomeValue == data.Value (IsNotNull is an unknown custom method) which in some ORMs pollutes the query plan, just as using a ternary operator. – Overbuild