The following query makes comparisons to the CLR String type which can be null:
var q = from l in db.Locationswhere (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:
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
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:Monday, October 15, 2007 4:58 PM