LINQ to SQL and Null strings, how do I use Contains?

Question

Here is the query

from a in this._addresses
where a.Street.Contains(street) || a.StreetAdditional.Contains(streetAdditional)
select a).ToList<Address>()

if both properties in the where clause have values this works fine, but if for example, a.StreetAdditional is null (Most of the times), I will get a null reference exception.

Is there a work around this?

Did you get an exception? Or are you speculating than an exception is possible? — Amy B, Jun 10 '09 at 18:07
If you've got a `NullReferenceException` for that, you aren't doing a LINQ to SQL query. — Pavel Minaev, Nov 29 '09 at 05:17
You could also think about not allowing Street or StreetAdditional to be null. If your db supports default values you could default these to an empty string, set the flag to disallow nulls and obviate the need to null check. — Tod, Dec 13 '11 at 18:39
On a side note, the query does work in LinqPad. How is this possible? Why is there such a difference in Linq to SQL behavior between the two tools? — dpant, Nov 13 '15 at 20:14

Yuliy · Answer 1 · 2013-12-14T09:21:10.207

59

I'd use the null-coalescing operator...

(from a in this._addresses
where (a.Street ?? "").Contains(street) || (a.StreetAdditional ?? "").Contains(streetAdditional)
select a).ToList<Address>()

edited Dec 14 '13 at 09:21

answered Jun 10 '09 at 18:02

Yuliy

17,381
6
41
47

4

This is a cleaner approach. Perfect for when dealing with an IQueryable – DanKodi Jul 28 '14 at 04:50

score 51 · Accepted Answer · answered Jun 10 '09 at 17:52

The most obvious one:

from a in this._addresses
where (a.Street != null && a.Street.Contains(street)) || (a.StreetAdditional != null && a.StreetAdditional.Contains(streetAdditional))
select a).ToList<Address>()

Alternatively you could write an extension method for Contains that accepts a null argument without error. Some might say that it is not so pretty to have such a method, because it looks like a normal method call, but is allowed for null values (thereby setting aside normal null-checking practices).

Custom extension methods aren't usable in LINQ to SQL. – Pavel Minaev Nov 29 '09 at 05:16 — Pavel Minaev, Nov 29 '09 at 05:16

score 6 · Answer 3 · answered Jun 10 '09 at 17:54

You must check first if StreetAdditional is null.

Try

where a.Street.Contains(street) || ((a != null) && a.StreetAdditional.Contains(streetAdditional))

This works because && is a shortcut-operator and if a != null yields false, the second expression with the null-value won't be evaluated since the result will be false anyway.

score 4 · Answer 4 · answered Jun 10 '09 at 17:54

4

I would create an extension method to return an empty sequence if null and then call contains method.

public static IEnumerable<T> EmptyIfNull<T>(this IEnumerable<T> pSeq)
{
      return pSeq ?? Enumerable.Empty<T>();
}

from a in this._addresses
where a.Street.Contains(street) || 
      a.StreetAdditional.EmptyIfNull().Contains(streetAdditional)
select a).ToList<Address>()

answered Jun 10 '09 at 17:54

Vasu Balakrishnan

1,751
10
15

Looks somewhat strange because a `null`-objects seems to be able to invoke member functions. – Dario Jun 10 '09 at 17:58
Upvote for the use of the ?? operator. That's what I would have suggested as well. If you feel that the extension method makes the query look strange you could do without the extension method. – Jeroen Huinink Jun 10 '09 at 18:04

score 2 · Answer 5 · answered Jun 10 '09 at 18:04

2

I don't think SqlServer gave you a null exception. If it did, then this code is clearly not running though LinqToSql (as you've tagged the question).

string.Contains would be translated to sql's like, which has no trouble at all with null values.

answered Jun 10 '09 at 18:04

Amy B

108,202
21
135
185

score 1 · Answer 6 · answered Nov 29 '09 at 04:37

You might want to check to make sure the variables street and streetAdditional are not null. I just ran across the same problem and setting them to an empty string seemed to solve my problem.

street = street ?? "";
streetAdditional = streetAdditional ?? "";
from a in this._addresses
where a.Street.Contains(street) || a.StreetAdditional.Contains(streetAdditional)
select a).ToList<Address>()

score 1 · Answer 7 · answered Jun 10 '09 at 17:52

Check to make sure that the properties are not null

from a in this._addresses
where (a.Street != null && a.Street.Contains(street)) || 
(a.StreetAdditional != null && a.StreetAdditional.Contains(streetAdditional))
select a).ToList<Address>()

If the null check is false, then the second clause after the && will not evaluate.

score 1 · Answer 8 · answered Jun 10 '09 at 17:53

1

from a in this._addresses
where a.Street.Contains(street) || (a.StreetAdditional != null && a.StreetAdditional.Contains(streetAdditional)
select a).ToList<Address>()

answered Jun 10 '09 at 17:53

Dimi Takis

4,924
3
29
41

score -1 · Answer 9 · edited Feb 23 '16 at 04:21

-1

One thing to note is that the null should be evaluated first.

where (**a.Street != null** && a.Street.Contains(street)) || (a.StreetAdditional != null && a.StreetAdditional.Contains(streetAdditional))
select a).ToList<Address>

()

edited Feb 23 '16 at 04:21

sujith karivelil

28,671
6
55
88

answered Feb 22 '16 at 18:54

user5636696

71
1
1

LINQ to SQL and Null strings, how do I use Contains?

9 Answers9

Linked