Why does a null string concatenated to a non null string evaluate to null?

Question

 var users=db.Users.Where(u => u.Name.StartsWith(term) || u.Email.StartsWith(term) || u.FirstName.StartsWith(term)).ToArray();
 var jsos=users.Select(u => new { label = u.FirstName  +" "+  u.Name+ " (" + u.Email+")", value = u.Id });

Works as expected. But, without the ToArray(), I get what appears to be strange behaviour: a null firstName results in the label being evaluated as null. With ToArray() I get the expected behaviour. (null is treated as an empty string and concatenated to the other non empty strings). Why?

500 - Internal Server Error · Accepted Answer · 2012-06-08 00:20:35Z

7

In the SQL world, by definition, any expression that has NULL as part of it becomes NULL. This is because NULL means an indeterminate value - an indeterminate value + anything else is still an indeterminate value, i.e. NULL.

Again, in SQL you could use something like COALESCE to convert NULLs into, say, a blank string - I don't recall off hand what the linq equivalent is.

answered Jun 8, 2012 at 0:20

500 - Internal Server Error

29k8 gold badges64 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kirk Woll Over a year ago

The null-coalescing-operator, ??, would be the C#/Linq equivalent.

Dominic P · Accepted Answer · 2012-06-08 01:11:14Z

With .ToArray(), you are using Linq against a .NET type, which is evaluated in code and uses .NET type handling. Here, the String concatenation methods work the way you are used to.

Without .ToArray, your query is executed in the DBMS and uses whatever that system is configured to use for NULL values.

In this case the DBMS is configured to handle NULLs according to the ANSI standard, which is very different from .NET in places. You should look up ANSI NULLs to find out the details. It isn't complicated, but can be confusing if you aren't expecting it.

Two addenda 1) First, I believe the concatenation rules for NULLs are laid out in the ANSI standard, but actually I realize I could be wrong. I came from a SQL Server background, where ANSI_NULLS is a setting controlling NULL equality, whereas concatenation is controlled by another one. If I am wrongly linking the latter to the former, I apologize.

2) Despite my talking about configurability, and the fact that SQL Server at least does allow changing that configuration, it is absolutely the case that the behavior you are seeing is standard, default, and expected. You should get used to it if you will be working with databases much at all.

Collectives™ on Stack Overflow

Why does a null string concatenated to a non null string evaluate to null?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related