Adam Herscher

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.



Re: LINQ Project General LINQ problem with queries containing null values for nullable types in a where clause

Joe Albahari

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







Re: LINQ Project General LINQ problem with queries containing null values for nullable types in a where clause

adamjh

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