none
Probable bug with LINQ2SQL with NULL values. RRS feed

  • Question

  • When I call something like this:
    int? someVariable;
    
    // some code where I set someVariable = null...
    
    data = this.DataContext.vwSomeTable.Where(q => q.SomeField == someVariable);
    

    LINQ generates me:

    SELECT [t0].[someField1], [t0].[someField2]
    FROM [dbo].[someTable] AS [t0]
    WHERE [t0].[someField] = @p0
    

    But @p0 == NULL and this SELECT gives me 0 rows.

    If I write this code:

    int? someVariable;
    
    // some code where I set someVariable = null...
    
    if (!someVariable.HasValue)
            data = this.DataContext.vwSomeTable.Where(q => !q.SomeField.HasValue);
          else
            data = this.DataContext.vwSomeTable.Where(q => q.SomeField == someVariable);
    

    In this case LINQ generates mostly adequate code:

    SELECT [t0].[someField1], [t0].[someField2]
    FROM [dbo].[someTable] AS [t0]
    WHERE NOT ([t0].[someField] IS NOT NULL)
    

    And I get my data rows.

    If that will be useful, I'm using MS VS2010 SP1 beta, WCF RIA SP1 beta with Silverlight 4 and SQL Server 2008.

    Monday, January 17, 2011 6:36 PM

Answers

  • Hi Dmitriy,

    I am not trying to be difficult so bear with me...Linq has differed/delayed execution and you have already shown that you were able to get the correct answer by rephrasing the C# statement with the If/then/else statement. Hence differed execution...

    So the excercise here is to get that working result you mentioned into one line right? Why can't you modify my statement to look for what you need say by specify the where to first return a setup of rows where the field/column is null...then from there look up all the values?

    var data = vwSomeTable.Where(q => q.SomeField.HasValue == false) .Where(q => q.SomeField == someVariable);

    What happens if you break out the steps what is returned for the above such as:

    var step1 = vwSomeTable.Where(q => q.SomeField.HasValue == false); // or q.SomeField == null

    and then

    var step2 = vwSomeTable.Where(q => q.SomeField.HasValue == false) .Where(q => q.SomeField == someVariable);

    I believe if you craft your linq query by working with specific Wheres, Anys and possibly Selects you can get the eventual sql you need. See 101 Linq Samples for other ways to craft the Linq.

    HTH


    William Wegerson (www.OmegaCoder.Com)
    • Marked as answer by Dmitriy K. _ Tuesday, January 18, 2011 2:28 PM
    Tuesday, January 18, 2011 2:06 PM
    Moderator

All replies

  • Use the null coalescing operator (??) to provide a value when its null and get proper values. HTH
    William Wegerson (www.OmegaCoder.Com)
    Monday, January 17, 2011 6:53 PM
    Moderator
  • Thank you for quick reply.

    ?? Operator gives me the same results as in my first example (maybe I missunderstand it?).

    The problem is that I have NULL values in column [t0].[someField] and I want LINQ to generate SQL code like this when my variable == null:

    SELECT [t0].[someField1], [t0].[someField2]
    FROM [dbo].[someTable] AS [t0]
    WHERE [t0].[someField] IS NULL
    
    
    Monday, January 17, 2011 7:24 PM
  • var data = vwSomeTable.Where(q => q.SomeField.HasValue == true)
               .Where(q => q.SomeField == someVariable);
    
    

     

    Try that one...select all where the field has a value first, from that return the ones which equal the target value.


    William Wegerson (www.OmegaCoder.Com)
    Monday, January 17, 2011 8:00 PM
    Moderator
  • select all where the field has a value first, from that return the ones which equal the target value.


    Sorry, but it seems to me, you have misunderstood me a little.

    I need rows, where the value of field eguals NULL, when someVariable = null, but L2S generates wrong SQL query (see first query in 1-st post).
    In case when "someVariable" has nonNull value, L2S works perfectly for me and generated query gives all needed rows.

    Tuesday, January 18, 2011 10:48 AM
  • Hi Dmitriy,

    I am not trying to be difficult so bear with me...Linq has differed/delayed execution and you have already shown that you were able to get the correct answer by rephrasing the C# statement with the If/then/else statement. Hence differed execution...

    So the excercise here is to get that working result you mentioned into one line right? Why can't you modify my statement to look for what you need say by specify the where to first return a setup of rows where the field/column is null...then from there look up all the values?

    var data = vwSomeTable.Where(q => q.SomeField.HasValue == false) .Where(q => q.SomeField == someVariable);

    What happens if you break out the steps what is returned for the above such as:

    var step1 = vwSomeTable.Where(q => q.SomeField.HasValue == false); // or q.SomeField == null

    and then

    var step2 = vwSomeTable.Where(q => q.SomeField.HasValue == false) .Where(q => q.SomeField == someVariable);

    I believe if you craft your linq query by working with specific Wheres, Anys and possibly Selects you can get the eventual sql you need. See 101 Linq Samples for other ways to craft the Linq.

    HTH


    William Wegerson (www.OmegaCoder.Com)
    • Marked as answer by Dmitriy K. _ Tuesday, January 18, 2011 2:28 PM
    Tuesday, January 18, 2011 2:06 PM
    Moderator
  • I just wanted to know, why L2S doesn't handle nullable types of variables in lambda expressions, and doesn't generate two different templates of SQL query for those purposes (with "WHERE field = @p0" in case when variable has value and "WHERE field IS NULL" when variable is null).

    Anyways thank you for patience and replying me.

    Tuesday, January 18, 2011 2:28 PM