woggles
woggles

Reputation: 7444

Linq why does concatenating strings result in a null?

I thought that "bill" + "john" + null == billjohn, but in this example of mine it seems to be evaluating to null:

 var clients = from client in taxPortalService.Client()
                          select new ClientViewModel
                              {                               
                                  ResidentialAddressLine1 = client.RESADDRESSLINE1,
                                  ResidentialAddressLine2 = client.RESADDRESSLINE2,
                                  ResidentialAddressLine3 = client.RESADDRESSLINE3,
                                  ResidentialAddressLine4 = client.RESADDRESSLINE4,
                                  ResidentialPostalCode = client.RESPOSTCODE,
                                  ResidentialCountry = client.RESCOUNTRY,
                                  IAResidentialAddress = client.RESADDRESSLINE1 + ", " + client.RESADDRESSLINE2 + ", " + client.RESADDRESSLINE3 + ", " + client.RESADDRESSLINE4 + ", " + client.RESPOSTCODE + ", " + client.RESCOUNTRY                               
                              };

Am I missing something obvious here?

enter image description here

Upvotes: 5

Views: 1082

Answers (3)

user743382
user743382

Reputation:

In C#, or rather in .NET, you're right, "bill" + "john" + null gives you "billjohn".

In SQL, 'bill' + 'john' + null gives you null.

Using LINQ to Entities translates your C# to SQL, and subtle differences such as this aren't always preserved.

You can use the more verbose

(client.RESADDRESSLINE1 ?? "") + ", " + (client.RESADDRESSLINE2 ?? "") + ", " + ...

to make sure you only concatenate non-null strings, which won't have this problem.

Upvotes: 7

Marc Gravell
Marc Gravell

Reputation: 1062865

I'm guessing this is using LINQ-to-SQL or EF as a backend, and it is generating SQL. Well, in TSQL a null concatenated with anything is (by default): null. Perhaps try:

(row.Foo ?? "") + ", " + (row.Bar ?? "") + ...

or easier: get the data as values into memory first, then do compositions.

Upvotes: 8

Tim Schmelter
Tim Schmelter

Reputation: 460138

Assuming SQL-Server as rdbms, a quick test reveals:

select 'A' + NULL;  // NULL

Demo

MSDN

The + (String Concatenation) operator behaves differently when it works with an empty, zero-length string than when it works with NULL, or unknown values. A zero-length character string can be specified as two single quotation marks without any characters inside the quotation marks. A zero-length binary string can be specified as 0x without any byte values specified in the hexadecimal constant. Concatenating a zero-length string always concatenates the two specified strings. When you work with strings with a null value, the result of the concatenation depends on the session settings. Just like arithmetic operations that are performed on null values, when a null value is added to a known value the result is typically an unknown value, a string concatenation operation that is performed with a null value should also produce a null result. However, you can change this behavior by changing the setting of CONCAT_NULL_YIELDS_NULL for the current session. For more information, see SET CONCAT_NULL_YIELDS_NULL (Transact-SQL). If the result of the concatenation of strings exceeds the limit of 8,000 bytes, the result is truncated. However, if at least one of the strings concatenated is a large value type, truncation does not occur.

Upvotes: 3

Related Questions