I came across an interesting problem this week regarding writing a LINQ query against a LinqToSql data context which contained a Varchar NULL field. The problem was, I had added this field manually into the data context instead of dragging the tables onto the designer from the server explorer. What I had neglected to do was set this field, which was nullable in the database schema, to nullable within the data context. It might be reasonable to think that this might not matter since you can set a string to null in code anyway without doing anything special to the variable (as opposed to specifically marking an int as nullable, for example).
If you don’t do this, the following can happen:
The specific query I was trying to write involved a simple keyword-matching query across a number of string fields, something like the following:
from player in DataContext.Playerswhere (player.Name + " " + player.Email + " " + player.Nickname) .ToLower().Contains(keywords) select player;
This simply concatenates the fields I want to search together and compares them against my keywords. In this case, ‘Nickname’ is optional and therefore nullable in the database. If the query comes across a null entry, it effectively voids the entire row and returns nothing. So the query is changed to something like this:
from player in DataContext.Players where (player.Name + " " + player.Email + " " + (player.Nickname ?? "")) .ToLower().Contains(keywords) select player;
..which means if Nickname is null it concatenates an empty string instead. That is, if you have the field set as nullable within your LinqToSql data context. If you don’t you’ll find that the resulting SQL will look like the following:
SELECT [t0].[Id], [t0].[Name], [t0].[Email], [t0].[Nickname] FROM [Test] AS [t0]WHERE (((([t0].[Name] + @p0) + [t0].[Email]) + @p1) + ((CASE WHEN 0 = 1 THEN [t0].[Nickname] ELSE CONVERT(NVarChar(50),@p2) END))) LIKE @p3
As you can see, for some reason the above SQL could be generated, which still won’t work due to the obvious flaw in the logic (WHEN 0=1). If you have correctly set up your nullable string properties you should see the following being generated, which is more correct:
SELECT [t0].[Id], [t0].[Name], [t0].[Email], [t0].[Nickname] FROM [Test] AS [t0]WHERE (((([t0].[Name] + @p0) + [t0].[Email]) + @p1) + ((CASE WHEN [t0].[Nickname] IS NOT NULL THEN CONVERT(NVarChar(50),[t0].[Nickname]) ELSE CONVERT(NVarChar(50),@p2) END))) LIKE @p3
On reflection, this is exactly the way that it should work. However, it has caught me out enough times to cause me to write about it, so that I don’t forget it again!