Entity framework LINQ with value OR null

Question

I have the following EF linq query to get all the employess or specific employee, in case if empId has value.

But the EF is not generating the expected query and it ignores OR condition always

from employee 
where employee.DepartmentId == depId && ((employee.Id == empId) || (employee.Id == null))
.ToList()

Expected Query

SELECT * FROM Employee
WHERE DepartmentId = @DepId AND (Id=@empId OR Id IS NULL)

Generated Query by EF when value is passed

SELECT * FROM Employee
WHERE DepartmentId = @DepId AND (Id=@empId)

Generated Query by EF when value is null

SELECT * FROM Employee
WHERE DepartmentId = @DepId AND (Id IS NULL)

Note: No Employee record has Id value NULL and it returns 0 instead of all employees

How to write linq for this Id=@empId OR Id IS NULL ?

Your expected query returns only matching ID's or those ID's which are NULL(which is impossible according to what you say in your Note). So what you actually want? I guess you want to bypass the check when the value is null but that is something else than what your sql query does. — Tim Schmelter
– Tim Schmelter, Commented Jan 17, 2018 at 11:35
It seems odd to me that your Employee table would have any rows where Id is null. Isn't this the primary key for that table? If not, you may want to consider renaming it. — DavidG
– DavidG, Commented Jan 17, 2018 at 11:37
Id is an Id so it cannot be null and I'm quite sure you model has it not nullable public int Id {get; set; }, that's why EF generate that query — Gianpiero
– Gianpiero, Commented Jan 17, 2018 at 11:37
@DavidG no values with null. I usually write SQL to filter specific data or all data using (Column=@value OR Column IS NULL). I tried with EF for same and didnt work for me — Developer
– Developer, Commented Jan 17, 2018 at 12:00

Tim Schmelter · Accepted Answer · 2018-01-17 11:45:22Z

6

You say this is your expected query:

SELECT * FROM Employee
WHERE DepartmentId = @DepId AND (Id=@empId OR Id IS NULL)

but i'm pretty sure that it is not, because the Id is never null (what you also say in your Note) because it is the primary key of that table. You actually mean this query:

SELECT * FROM Employee
WHERE DepartmentId = @DepId AND (Id=@empId OR @empId IS NULL)

so you want to bypass the check and return all records if the parameter is NULL. Note that this is not the best way performance wise. You should use a query without filter when you don't want to filter by Id. I'm afraid this will not produce the most efficient query plan. I'd use this:

IQueryable<Employee> allDepartmentEmployees = 
    from employee in ...
    where employee.DepartmentId == depId;
    select employee;

if(empId.HasValue) // presuming it's a nullable type
{
    allDepartmentEmployees = allDepartmentEmployees.Where(e => e.Id == empId.Value);     
}

List<Employee> employeeList = allDepartmentEmployees.ToList();

So only filter if the parameter is given. Then this will only return one record.

answered Jan 17, 2018 at 11:45

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Developer Over a year ago

Thanks and It looks nice and I am trying now. Actually I have simplified my question by asking with an example with one table. In Actual, I join many table and in where condition I need to apply this additional filter. Let me try your code and see how the EF applies where condition

Kissaki · Accepted Answer · 2021-12-08 09:29:18Z

The LINQ to SQL translation simplified your query logic. They are functionally equivalent.

You should not try to write LINQ in a way that it generates a specific SQL you expect. You are writing LINQ to do a specific operation. If the resulting query does not match your expectation, try to understand why.

When your comparison is (Id=@empId OR Id IS NULL)

If @empId is always NULL, then (Id IS NULL) is a functionally equivalent simplification
If @empId is a value or may be null then (Id=@empId) is a functionally equivalent simplification

You did not say which DB / DB driver you use with Entity Framework, but you can verify that comparing against NULL is always false:

SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END

MS SQL Server returns 0 for this query. So NULL is not equal to NULL.

Database Engines typically interpret and handle NULL a bit differently than C# does null. NULL is unknown rather than unset. In MS SQL Server IS NULL is used to compare against NULL rather than =, != or <>.

For MS SQL Server see NULL and UNKNOWN

Collectives™ on Stack Overflow

Entity framework LINQ with value OR null

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related