The right way to use lambda expressions in linq

Question

Problem: I want to construct a dynamic where condition on my linq query based on inputs to my method.

Let's say my method accepts a first name, a last name and a zipcode. Users can choose to pass in one or more of this in my method.

My linq query looks like:

var query = (from employee in EmployeeDb.Employees
             select employee)
//Adding firstname to where. Similarly I add other conditions.
if (request.FirstName != string.Empty)
     query = query.Where(c => c.FirstName == request.FirstName);

And when I expect my SQL query where condition to be something like:

WHERE [Extent6].[LastName] = @p__linq__1 AND [Extent6].[FirstName] = @p__linq__0

What I actually see is:

 WHERE (([Extent6].[LastName] = @p__linq__0) OR (([Extent6].[LastName] IS NULL) AND (@p__linq__0 IS NULL))) AND (([Extent6].[FirstName] = @p__linq__1) OR (([Extent6].[FirstName] IS NULL) AND (@p__linq__1 IS NULL)))

And thats obviously causing a lot of performance issues. What am I doing wrong?

@ErenErsönmez Great point. They are NULLs. I can change the names to not nulls, but some other parameters can be NULLs. So if a db column can be NULLable that means Linq will add these checks automatically? There is no way to disable it? — user3726933, Nov 03 '14 at 16:55
Looks like there is a `DbContextConfiguration.UseDatabaseNullSemantics` property in EF 6.0+. — Eren Ersönmez, Nov 03 '14 at 17:02
Here is very interesting idea: http://stackoverflow.com/questions/5315744/linq-where-clause-with-lambda-expression-having-or-clauses-and-null-values-retur - Adam Robinson's solution ;) — Maciej Los, Jan 02 '15 at 19:19

score 2 · Accepted Answer · answered Mar 15 '15 at 04:33

The problem exists because of the difference in which c# and most databases handle null values. In C#, null==null is true, while in most databases, null==null would return false (or unknown). LINQ is trying to write a SQL query that matches C#'s ideas of nulls. Since your fields are nullable, and you could ask for all records where Firstname is null, this makes sense. If you don't want Firstname to be nullable, then you should make it a NOT NULL field and LINQ will generate a simpler query for you.

Alternatively, you can set UseDatabaseNullSemantics property of your context object to true and it will simplify your query, but you will not able to ask for records where Firstname is null.

The right way to use lambda expressions in linq

1 Answers1