C# LINQ : Nullable Type in where clause in LINQ Query

In this blog, I will discuss about how LINQ to SQL handle the nullable type in the where clause and in LINQ to Entites, there is no any equivalent key for SQL ‘IS NULL’ key.

Example: C# Function  PersonExistenceCheck -  checks person existence

public bool PersonExistenceCheck(PersonData personData)

        {

            bool personExist = false;

            IEnumerable<Person> persons = _db.Persons.Where(x => x.Name == personData.Name && x.PersonLoc == personData.PersonLoc);

            personExist = persons.Any();

            return personExist

        }

  

As we see above, there is where clause on on PersonLoc field, which is nullable column. 

If there are any person records, which have NULL for PersonLoc, then function will not return expected result and then Question?  Why is it not working correctly?

Explanation: In SQL, both below statement will be treated differently and output will be different.

PersonLoc == null
PersonLos Is NULL.

If PersonLoc is null

                PersonLoc == null – False

    PersonLos Is NULL – True

Solution :  In place of x.PersonLoc == personData. PersonLoc  use

((x.   PersonLoc == null && personData. PersonLoc == null) || (x. PersonLoc == personData. PersonLoc))

Example :

IEnumerable<Person> blocks = _db.Persons.Where(x => x.Name == personData.Name && ((x.   PersonLoc == null && personData. PersonLoc == null) || (x. PersonLoc == personData. PersonLoc)));

In LINQ To Entites,

• if you are using “x == null “ in where clause, It will be converted into x IS NULL in SQL.
• If  string y = null ; you are using  "x == y " in where clause, It will be converted into x = null in SQL.

Thanks for visiting, Please leave your comments, if it helps you