string concatenation with nulls and sql server


The string concatenation behaviour in SQL server is different from the string concatenation behaviour in C# and causes confusion.

the default in SQL server (and apparently this will not be changeable in the future http://technet.microsoft.com/en-us/library/ms176056.aspx) is that concatenating a value with null results in null. In C#, the same concatenation results in a string.

when a projection is used that pushes this concatenation to the database side, the result is unexpected. perhaps the generated query should coalesce the nullable string before concatenating?
Closed Feb 11 at 4:58 PM by lukew
Verified fixed, Tested multiple ways to pass null to string concat including nullable variables, arrays, null values retrieved from database, null arrays. This addition ends up generating longer sql queries when nullable values are present but this doesn't look like an issue.


RoMiller wrote Jan 30 at 6:26 PM

We have a change going in today that will address this.

moozzyk wrote Jan 31 at 4:22 AM

Fixed as part of workitem #1904.

lukew wrote Feb 8 at 12:20 AM

Looks like a dupe of issue 1933

brebok wrote Jul 3 at 8:02 PM

This is a definite change from the behavior in EF5 and, I must say, not a welcome one, in our case. In the project that I work on, we typically do LEFT OUTER JOINs on foreign keys to lookup tables in our queries and concatenate the code with the lookup value (ex. "A - Description for A here"). In EF5, a null would be returned, which is what we wanted and expected. Now, in EF6, an empty dash ("-") is returned. Is there any way to configure this behavior or a different query patter that will return the null value in EF6?

I completely understand the logic behind the change, but this is a significant change in behavior that does not appear to be well documented. Also, I must point out that the generated SQL now is less efficient in EF6 vs. EF5.