locked
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.

    Tuesday, September 04, 2007 1:09 PM

Answers

  • 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.
    Saturday, September 08, 2007 8:20 PM
  • 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. 

     

    Monday, September 10, 2007 3:36 PM

All replies

  • 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.
    Friday, September 07, 2007 12:19 PM
  • 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

    Friday, September 07, 2007 7:21 PM
  • 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.

     

    Friday, September 07, 2007 8:47 PM
  •  

    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?

     

    Friday, September 07, 2007 10:43 PM
  • 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
    Saturday, September 08, 2007 6:12 AM
  • 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.
    Saturday, September 08, 2007 8:20 PM
  • 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
    Sunday, September 09, 2007 1:30 AM
  • 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.

    Monday, September 10, 2007 8:37 AM
  • 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. 

     

    Monday, September 10, 2007 3:36 PM
  • 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.

    • Proposed as answer by Ming L Wednesday, October 03, 2012 6:25 AM
    Thursday, September 13, 2007 2:25 AM
  •  

    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.
    Thursday, September 13, 2007 9:53 AM
  • 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.

     

    Monday, September 08, 2008 5:44 PM
  • 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.

     

    Tuesday, August 31, 2010 3:18 PM
  • 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);
    

    Tuesday, September 20, 2011 11:51 AM
  • 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.

    Tuesday, April 03, 2012 5:04 PM