How to express "is null" sql syntax in linq to sql

Question

How can I express "IS NULL" sql syntax in Linq to Sql?

Where(r => (r.Level1.Equals(l[1] == "" ? null : l[1]))

In the above code linq to sql converts the linq expression into the following sql which is not what I want.

@p1=NULL

I want my linq to be converted into the following sql

@p1 is null

How can I achieve this?

could you show all the sql generated ? Do you really have WHERE @p1=null ? — Raphaël Althaus
– Raphaël Althaus, Commented May 15, 2013 at 10:47

fhelwanger · Accepted Answer · 2013-05-14 17:07:51Z

2

Try this

if (l[1] == "")
    Where(r => (r.Level1 == null));
else
    Where(r => (r.Level1 == l[1]));

If you use the ternary operator the expression evaluator will find that it's a expression returning a string, cause of this it'll use the = operator.

answered May 14, 2013 at 17:07

fhelwanger

4754 silver badges9 bronze badges

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

4 Comments

If I do the following it's the same. Where(r => (r.Level1 == (l[1] == "" ? null : l[1])) Still not producing the desired sql

How is the Level1 property declared? It's int or int??. For nullable database fields, you must use nullable types.

In the dbml designer, the Level1 property is declared as Nullable in the properties windows?

@Neo Your example is NOT the same as fhelwanger's. His example should work, but yours will not.