Why Linq Substring() ignore null value

Question

I just realized that when I request this

Orders.Where(y => y.Sub_Item_Id.Substring(0, 10) != y.Sub_Item_Site_Id.Substring(0, 10))

The query ignore all my null value that may exist in y.Sub_Item_Site_Id. So when I have a value in Sub_Item_Id and null in Sub_Item_Site_Id the query does NOT consider this as !=.

Why?

I also tested the same query with SQL

select 
    *
from
    orders as o
where
    LEFT(o.sub_item_id, 10) <> LEFT(o.sub_item_site_id, 10)

And I get the same result. I have all my different value but NOT when I have a value in o.sub_item_id and null in o.sub_item_site_id.

Could you explain how and why SQL and Linq is working like this.

score 1 · Accepted Answer · answered Feb 23 '17 at 06:53

1

Substring() doesn't ignore null, in your query if one of the strings will be null you will get NullPointerException and if one of the string is shorter than 10 symbols, you will get ArgumentOutOfRangeException:

https://msdn.microsoft.com/library/aka44szs(v=vs.110).aspx

Check your data nad your query.

answered Feb 23 '17 at 06:53

Maksim Simkin

9,561
4
36
49

Sorry but if the system generate excpetions and these exception are handle by the system itself the result is the system ignore something. Yeah, ok. So the Linq query ignore these line because you think the system generate exception. So what about the SQL query? Same, the LEFT() generate exception? – Bastien Vandamme Feb 24 '17 at 05:03

score 0 · Answer 2 · edited May 23 '17 at 11:46

0

Relational expressions involving NULL actually yield NULL again. In order to filter null values, you need to use the IS NULL and IS NOT NULL. You can find an answer here.

edited May 23 '17 at 11:46

Community

1
1

answered Feb 23 '17 at 06:19

Asier Azkuenaga

1,199
6
17

score 0 · Answer 3 · answered Feb 23 '17 at 06:58

You may want to add a tag indicating that your LinQ in the background connects to a database to execute SQL there. Without that information, Maksim is correct. In plain Linq-to-Objects C# you would get the appropriate exceptions.

However, as your LinQ is translated and executed as SQL, you just met a surprisingly unintuitive SQL feature: NULL is never equal to anything else. Not even NULL. And NULL is never not equal to anything else. No even a value or another NULL. It just never yields true in a comparison, whatever you compare it to.

You can use the IS syntax to ask if something is NULL. Or you can replace your NULL with a default value before doing your statement. But comparing existing values and NULL will always yield false because NULL is not equal to anything, not even NULL itself.

Why Linq Substring() ignore null value

3 Answers3