none
LINQ writing wrong SQL for nulls? (Ansi_Nulls) RRS feed

  • Question

  • Hi I've got a linq query similar to this:

    string nullString = null;  
     
    from o in dbContext.Objects  
    where o.stringColumn == nullString  
    select o 

    I'm getting no results but I think I should be. If I look at the SQL generated by this LINQ, it looks something like this:

    SELECT [t0].[pk_objectID], [t0].[stringColumn],  
    FROM [dbo].[objects] AS [t0]  
    WHERE ([t0].[stringColumn] = @p0) 

    What worries me is that in my example, @p0 is null, therefore ansi_nulls would have to be set to off for this query to return any results. How does LINQ handle this?
    • Moved by Zhi-Xin Ye Thursday, March 12, 2009 7:50 AM not related to BCL (Moved from .NET Base Class Library to LINQ to SQL)
    Friday, March 6, 2009 8:56 PM

All replies

  •  So I've discovered that if I write my LINQ this way, it will generate the correct SQL:


    string nullString = null;  
     
    from o in dbContext.Objects  
    where Object.Equals(o.stringColumn, nullString)  
    select o 

    SELECT [t0].[pk_objectID], [t0].[stringColumn],  
    FROM [dbo].[objects] AS [t0]  
    WHERE ([t0].[stringColumn] IS NULL) 


    To me this seems like a huge shortcoming of the LINQ to SQL provider when it comes to comparing nullable types. Because if I mistakenly use the == operator for a nullable type, which is perfectly acceptable in .NET, and I'm comparing a null value my SQL comes out "= NULL" instead of "IS NULL" which falsely gives me an empty result set! The LINQ to SQL provider really doesn't provide support for this?
    Friday, March 6, 2009 9:32 PM
  • In most cases LINQ to SQL just does a direct translation of the query, which means that SQL null semantics are used. It doesn't try to force the database behavior to match CLR null semantics. There are some exceptions, such as a comparison to a literal null (i.e. a null directly in the query, not a variable) that gets translated into IS NULL.

    The following MSDN topic has more information on how LINQ to SQL handles nulls:

    Null Semantics (LINQ to SQL)
    http://msdn.microsoft.com/en-us/library/bb882669.aspx

    Thanks,
    Sarah


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, March 12, 2009 11:29 PM
    Moderator
  •  fyi - you can also work around it by changing your query to:

    from o in dbcontext.objects
    where (o.stringcolumn == nullstring || (nullstring == null & o.stringcolumn == null))
    select o
    Kristofer - Huagati Systems Co., Ltd. - web: www.huagati.com - blog: blog.huagati.com - twitter: twitter.com/KristoferA
    Friday, March 13, 2009 3:20 AM
    Answerer
  • It just seems like it would've been so simple for the provider to check if your parameter is null and write "IS NULL" instead of "= @pX".
    Friday, March 13, 2009 3:26 PM
  • Did you set property stringColumn Nullable = true ?
    Tuesday, March 17, 2009 6:35 AM
  • Hi,

    Yes, in the database it does allow nulls.
    Tuesday, March 17, 2009 3:46 PM
  • What would happen if the query were compiled and the parameter value subsequently changed?

    [)amien
    Tuesday, April 14, 2009 9:50 PM
    Moderator
  • Hi Damien,
    What do you mean by that? Are you referring to deferred execution? So if I declared my LINQ query, then changed the value of my string before the query is actually executed?
    Wednesday, April 15, 2009 4:22 PM