Nullable type in where clause

Question

Hi!

 

How LINQ to SQL handle the nullable type in the where clause? Example:

Code Snippet

private int? MyId { get { return null; }}

 

var categories = from c in _db.ProductCategories
where c.ParentProductCategoryID == MyId
select c;

 

The type of the c.ParentProductCategoryID is the int?. But the generated SQL select looks like:

 

Code Snippet

WHERE [t0].[ParentProductCategoryID] = @p0

 

which of course will not match the null values in the database - it must to be 'IS NULL'.

 

If I type the query directly with null keyword:

 

Code Snippet

var categories = from c in _db.ProductCategories
where c.ParentProductCategoryID == null
select c;

 

the generated select has right WHERE clause:

 

Code Snippet

WHERE [t0].[ParentProductCategoryID] IS NULL

 

So the quetion is: How can I write the LINQ query with test of the nullable type which will generate correct select for null value and also the correct select for the non-null value?

 

Thanks, Petr.

Answer 1

The current .Where expression does not support your problem. You need yourself control value of query parameter.

 

This could be your example:

Code Snippet

private int? MyId { get { return null; }}

 

var categories = from c in _db.ProductCategories
select c;

 

if(MyId.HasValue)

{

categories = categories.Where(c => c.ParentProductCategoryID == MyId);

}

else

{

categories = categories.Where(c => c.ParentProductCategoryID == null);

}

 

//Because Linq query has deffere execution, so it will query database when you do some action as:

 

var list = query.ToList();

 

You can also write your own Where expression which will do the same.

Answer 2

Thanks for your answer, Dariusz! Yes, I made such _workaroud_ for myself, but I cannot believe this is the final solution!? I can everywhere read how LINQ reduce code bloating, simplify and clarify it and how fully supports the nullable types. My example is IMHO elementary and I'm forced to use such ugly code

 

Petr

Answer 3

I understand You. But If .Where extension currently doesn't has automatic tracking of parameters with null value - like in your example, so you need to decide: wait to this functionality or do it in other ways and opimize it in the future.

 

Maybe someone else has info is it done by design or it will be changed in RTM or later.

 

Answer 4

 

Why not do

 

Code Snippet

var categories = from c in _db.ProductCategories
where c.ParentProductCategoryID == MyId || c.ParentProductCategoryID == null
select c;

 

 

 

... if you want both matching and null values returned?

 

Answer 5

But that is want the problem. I would not want to have to write for null differeltly. and if my variable is null then the = clause in SQl can give bad result

Answer 6

Secret cheat code:  Use use object.Equals to get 2-value equals semantics (nulls equal nulls).

 

from c in db.ProductCategories

where object.Equals(c.ParentProductCategoryID, myID)

select c;

 

LINQ to SQL does not use this translation by default due to performance implications and the expected behavior when predicateds are used as join conditions.

Answer 7

This cheat is terrific.

It would be nice if the same "cheat" also worked with the instance version of object.Equals. Then it would be consistent with way equality works throughout the rest of the .NET Framework. For example:

double nan1 = double.NaN;
double nan2 = double.NaN;

Console.WriteLine (nan1 == nan2);                            // False
Console.WriteLine (object.Equals (nan1, nan2));         // True
Console.WriteLine (nan1.Equals (nan2));                   // True

Joe

Answer 8

Matt: Use Equals method was my first attempt to solve this disappointment. I used

 

Code Snippet

where c.ParentProductCategoryID.Equals(MyId)

 

but this is not working, as Joe also mentioned, so I have to use object.Equals instead => next confusion  I also do not understand the reasons (performance and joins). It should not be so hard to generate SQL like 'IS NULL' if the parameter is null value and like '= @pN' if the parameter is non-null vlaue.

 

 

Keith: your example is not working because I do not want to match both values. Your code produce

 

Code Snippet

WHERE ([t0].[ParentProductCategoryID] = @p0) OR ([t0].[ParentProductCategoryID] IS NULL)

 

Which does not return right resultset if the @p0 is non-null value, because in this case I do not want to get the rows where ParentProduct is NULL.

Answer 9

It is possible to do what you are asking.  However, the translated SQL query would then depend on the value of a parameter, making it impossible or inconsistent to produce a compiled version of the query where the parameter values are only known at invocation time. 

 

Answer 10

Matt,

 

The problem with the current situation is that it's completely inconsistent.

 

This query will always return 0 rows:

 

Code Snippet

 

int? reportId = null;

 

var query = from r in ctx.Reports
            where r.ParentId == reportId
            select r;

 

 

You never get any results from this, because null is not equal to null.  This was a very unwise decision made by the designers of SQL a long time ago, and there's nothing that can be done about it.

 

In this case, LINQ is trying to be compatible with SQL.  I don't agree with the LINQ team's design decision, but I understand the argument.

 

But then look at this query:

 

Code Snippet

 

var query = from r in ctx.Reports
            where r.ParentId == null
            select r;

 

 

This one returns results!  Oops!  What happened to SQL compatibiliy??  Logically, these should give me the exact same results.

 

The answer is that there's "magic code" which sees the "ParentId == null" comparison and translates it into SQL as "ParentId IS NULL".

 

This is a terrible situation.  Here we have a case where sometimes "null equals null", and other times "null doesn't equal null" but also "null doesn't not equal null".  What a mess!  It feels like PHP or Perl, and I don't mean that in a nice way.

 

The way to fix this is to realize that LINQ is part of C#.  It's not part of SQL.  The syntax of LINQ queries should look and act like the syntax of C# or VB.  In fact, that's the whole reason LINQ was created in the first place.  To give us queries directly in .NET languages.

 

Here's how the first query should get translated to SQL to make everything work:

 

Code Snippet

 

SELECT * FROM REPORTS

         WHERE (ParentId IS NULL AND @reportId IS NULL) OR

               (ParentId = @reportId);

 

 

This allows you to generate the SQL query up front, without knowing whether or not the variable reportId will be set to null.  And it allows null values in LINQ queries to have the same semantics they have elsewhere in C#, namely, "null equals null" but it never equals anything else.

Answer 11

 

I second that. The goal of LINQ would be to unify querying objects, both syntactically and semantically. That goal is not achieved if I have to write different LINQ queries for different providers to get the same results.

Answer 12

I have to agree - this is a huge bug.  I think a lot of developers are going to be pretty irritated a few months from now when they have to go back through all the LINQ-to-SQL code they wrote and wrongly assumed would run as advertised.  Having to use object.equals is an ugly hack.  It's a simple solution - if the column that is being compared ever can be null, then LINQ-to-Sql needs to generate SQL that checks is see if the input parameter is null.  It'll be the same every time, so it won't affect execution plans and such.  Now, with the object.equals work-around, the SQL could change every time depending on the value of the parameter, which is less than ideal.

 

Answer 13

It is possible to do what you are asking.  However, the translated SQL query would then depend on the value of a parameter, making it impossible or inconsistent to produce a compiled version of the query where the parameter values are only known at invocation time. 

It could be translated to two compiled queries with the query selection done at runtime, or it could be translated to one of the workarounds mentioned (granted that it gets more complicated if you have multiple nullable parameters).  The other workarounds must also bypass compiled queries.  I don't care about compiled queries anyway.  I care about convenience and getting the expected behavior.  If I cared about performance, then perhaps I could find out that I could get faster queries by using int intead of int? as a parameter.

Three years after this post, I ran into this problem within my first week of using linq.  At least could the linq compiler produce a warning or an error if you use an int? parameter?

Linq's == null behavior is misleading.  It makes you think incorrectly that you don't have to worry about the old "is null vs ==" problem any more.

 

Answer 14

Just parse the original item to Int32, then you can use it in linq without looping lambda after it.

string Vendor = dictionary["Vendor"].ToString();
int PriorityID = Int32.Parse(dictionary["PriorityID"].ToString());
string Program = dictionary["Program"].ToString();

var itemToInsert = (from j in context.Items 
                            where j.Vendor == Vendor &&
                            j.PriorityID == PriorityID &&
                            j.Program == Program
                            select j);

Answer 15

WOW!!  What a nasty bug!  It's 5 years later and still not fixed.  How hard is it to convert the == to what Daryl was suggesting:

SELECT * FROM REPORTS
         WHERE ((ParentId IS NULL AND @reportId IS NULL) OR ParentId = @reportId);

My goodness!  How many bugs I might have in the code because of this behavior.... just baffles the mind.