locked
LINQ problem with queries containing null values for nullable types in a where clause

    Question

  • The following query makes comparisons to the CLR String type which can be null:

     

    var q = from l in db.Locations

    where (l.Address == address) && (l.City == city) && (l.Country == country) && (l.Lat == lat) && (l.Lon == lon) && (l.State == state) && (l.Type == type.ToString()) && (l.Zip == zip)

    select l.Id;

     

    (address, city, state, zip, and country are strings, and their corresponding columns in the Location table allow NULL values)

     

    In VS 2008 Beta 2, LINQ seems to incorrectly generate the following query:

     

    SELECT [t0].[Id]
    FROM [dbo].[Location] AS [t0]
    WHERE ([t0].[Address] = @p0) AND ([t0].[City] = @p1) AND ([t0].[Country] = @p2) AND ([t0].[Lat] = @p3) AND ([t0].[Lon] = @p4) AND ([t0].[State] = @p5) AND ([t0].[Type] = @p6) AND ([t0].[Zip] = @p7)
    -------------------------------
    @p0 [String]: '6100 Iliff Rd Nw'
    @p1 [String]: 'Albuquerque'
    @p2 [String]: ''
    @p3 [Double]: 35.103263
    @p4 [Double]: -106.707807
    @p5 [String]: 'NM'
    @p6 [String]: 'Organization'
    @p7 [String]: ''

     

    I would expect LINQ to instead generate a query with "is null" if the CLR variable values happen to be null at runtime, whereas in the generated SQL above, a comparison is made to the empty string value, which is not the same result.

     

    In the meantime, I imagine many developers will run into this issue, have to debug with the sql query visualizer add-on as I did, figure out what's going on, and build a query builder helper function for these types of queries -- but is there a reason it couldn't instead be built into the framework?

     

    This issue was also raised before, but seems to have been marked as 'answered' without a good answer.

    Monday, October 15, 2007 6:28 AM

Answers

  • The solution is to use object.Equals instead of the equality operator:

    string address = null;

    var q = from l in db.Locations

    where (object.Equals (l.Address, address) && ...)

    select ...



    Regards


    Joe


    Monday, October 15, 2007 8:11 AM

All replies

  • The solution is to use object.Equals instead of the equality operator:

    string address = null;

    var q = from l in db.Locations

    where (object.Equals (l.Address, address) && ...)

    select ...



    Regards


    Joe


    Monday, October 15, 2007 8:11 AM
  • Thanks Joe!  That certainly seems to do the trick!

     

    For anyone else who runs into this and happens to stumble upon the forums, I just found a little bit more background here as well:

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2115031&SiteID=1

    Monday, October 15, 2007 4:58 PM
  • Perfect!!!!

    Thanks Joe....

    Monday, May 09, 2011 9:06 PM