Q:

The right way to use lambda expressions in linq

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?

A:

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.

