{Programming} & Life

Another “gotcha” using LINQ to SQL that I’ve found if you wish to test for “null” on a nullable column in SQL Server using the “IS NULL” clause. See below, this C# statement does not return the results you would expect (Assume “ReadMessageTypeFields()” simply returns an IQueryable result):

return messageTypeFieldRepository.ReadMessageTypeFields().Where(x => x.MessageTypeID == messageTypeID).ToList();

Results undesired SQL query result:

[sourcecode language='sql']
SELECT [t2].[ID], [t2].[MessageTypeID], [t2].[Name], [t2].[ID2] AS [Status], [t2].[UpdatedAt], [t2].[value] AS [CreatedAt]
FROM (
SELECT [t0].[ID], [t0].[MessageTypeID], [t0].[Name], [t1].[ID] AS [ID2], [t0].[UpdatedAt], [t0].[CreatedAt] AS [value]
FROM [dbo].[MessageTypeField] AS [t0]
INNER JOIN [dbo].[Status] AS [t1] ON [t1].[ID] = [t0].[StatusID]
) AS [t2] WHERE [t2].[MessageTypeID] = NULL

Notice the "= NULL" test. In SQL Server you cannot return NULL records by testing by "= NULL", this must be "IS NULL". Which is fair enough, I can deal with that; however I assumed that any null test in LINQ would be evaluated to the most obviously desired sql query result, but this is not the case. It seems that if you are testing in .NET on an object that has been "assigned" null, this could be a string, nullable int or anything, this will always output the result as "x = NULL" in a query, which is generally not what you want, as that will return 0 records, but if the expression in linq is tested explicitly FOR "null" this results in "IS NULL" in SQL correctly. See below:

return messageTypeFieldRepository.ReadMessageTypeFields().Where(x => ((!messageTypeID.HasValue && x.MessageTypeID == null) || (messageTypeID.HasValue && x.MessageTypeID == messageTypeID))).ToList();

Results in correct SQL query:

[sourcecode language='sql']
SELECT [t2].[ID], [t2].[MessageTypeID], [t2].[Name], [t2].[ID2] AS [Status], [t2].[UpdatedAt], [t2].[value] AS [CreatedAt]
FROM (
SELECT [t0].[ID], [t0].[MessageTypeID], [t0].[Name], [t1].[ID] AS [ID2], [t0].[UpdatedAt], [t0].[CreatedAt] AS [value]
FROM [dbo].[MessageTypeField] AS [t0]
INNER JOIN [dbo].[Status] AS [t1] ON [t1].[ID] = [t0].[StatusID]
) AS [t2]
WHERE [t2].[MessageTypeID] IS NULL

Notice I test if the ID is null first, if it is, actually test for null, if it is not, actually test for explicit ID. Adds a little convolution to the code and a bit annoying to always cater for second checking, but this is price you pay for LINQ goodness I guess.