52

I'm trying to do a query like so...

query.Where(x => !string.IsNullOrEmpty(x.PropertyName));

but it fails...

so for now I have implemented the following, which works...

query.Where(x => (x.PropertyName ?? string.Empty) != string.Empty);

is there a better (more native?) way that LINQ handles this?

EDIT

apologize! didn't include the provider... This is using LINQ to SQL

Jon Erickson
  • 112,242
  • 44
  • 136
  • 174
  • What LINQ provider? SQL, Entity, Object, ...? – Daniel Brückner Sep 02 '09 at 17:02
  • I assume your LINQ provider does not support String.IsNullOrEmpty() causing a NotSupportedException. – Daniel Brückner Sep 02 '09 at 17:03
  • Can you explain why it fails? What values are getting through that you do not expect to make it. I can't see anything wrong with this particular code (or what would differentiate it from the second example). – JaredPar Sep 02 '09 at 17:01
  • It failed to map string.IsNullOrEmpty to sql. Seemed that it's Microsoft's problem. – Tyler Liu Oct 13 '11 at 08:13
  • 1
    A "possible solution" is to bring the records to C# world with `ToList()` like this... `query.ToList().Where(...)` but this could impact your performance badly, so use it wisely if you need to – Jaider May 31 '13 at 16:33

5 Answers5

50

http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=367077

Problem Statement
It's possible to write LINQ to SQL that gets all rows that have either null or an empty string in a given field, but it's not possible to use string.IsNullOrEmpty to do it, even though many other string methods map to LINQ to SQL. Proposed Solution Allow string.IsNullOrEmpty in a LINQ to SQL where clause so that these two queries have the same result:

var fieldNullOrEmpty =
from item in db.SomeTable
where item.SomeField == null || item.SomeField.Equals(string.Empty)
select item;

var fieldNullOrEmpty2 =
from item in db.SomeTable
where string.IsNullOrEmpty(item.SomeField)
select item;

Other Reading:
1. DevArt
2. Dervalp.com
3. StackOverflow Post

Community
  • 1
  • 1
RSolberg
  • 26,821
  • 23
  • 116
  • 160
  • 2
    A very simple solution is to just use `item.SomeField.Length > 0` - you won't get a null reference in sql. This is analogous to `LEN(SomeField) > 0` which works on null fields too. – Bron Davies Feb 17 '20 at 19:50
  • @BronDavies I don't think the query engine would be able to use existing indexes on that column if you use something that is translated into `LEN(SomeField)` – Magnus Sep 03 '21 at 19:21
18

This won't fail on Linq2Objects, but it will fail for Linq2SQL, so I am assuming that you are talking about the SQL provider or something similar.

The reason has to do with the way that the SQL provider handles your lambda expression. It doesn't take it as a function Func<P,T>, but an expression Expression<Func<P,T>>. It takes that expression tree and translates it so an actual SQL statement, which it sends off to the server.

The translator knows how to handle basic operators, but it doesn't know how to handle methods on objects. It doesn't know that IsNullOrEmpty(x) translates to return x == null || x == string.empty. That has to be done explicitly for the translation to SQL to take place.

Brian Genisio
  • 47,787
  • 16
  • 124
  • 167
2

This will work fine with Linq to Objects. However, some LINQ providers have difficulty running CLR methods as part of the query. This is expecially true of some database providers.

The problem is that the DB providers try to move and compile the LINQ query as a database query, to prevent pulling all of the objects across the wire. This is a good thing, but does occasionally restrict the flexibility in your predicates.

Unfortunately, without checking the provider documentation, it's difficult to always know exactly what will or will not be supported directly in the provider. It looks like your provider allows comparisons, but not the string check. I'd guess that, in your case, this is probably about as good of an approach as you can get. (It's really not that different from the IsNullOrEmpty check, other than creating the "string.Empty" instance for comparison, but that's minor.)

Reed Copsey
  • 554,122
  • 78
  • 1,158
  • 1,373
0

... 12 years ago :) But still, some one may found it helpful:

Often it is good to check white spaces too

query.Where(x => !string.IsNullOrWhiteSpace(x.PropertyName));

it will converted to sql as:

WHERE [x].[PropertyName] IS NOT NULL AND ((LTRIM(RTRIM([x].[PropertyName])) <> N'') OR [x].[PropertyName] IS NULL)

or other way:

query.Where(x => string.Compare(x.PropertyName," ") > 0);

will be converted to sql as:

WHERE [x].[PropertyName] > N' '
parfilko
  • 1,308
  • 11
  • 12
  • LINQ-to-SQL didn't change in 12 years. The first part of your answer isn't true. The second part is, and is a nice addition actually, but the translation looks different. You're using EF core, which is a different beast altogether. – Gert Arnold Sep 03 '21 at 19:40
  • > "The first part of your answer isn't true" - I just copied it from debugger :) – parfilko Sep 08 '21 at 20:50
  • 1
    It's not LINQ-to-SQL. – Gert Arnold Sep 09 '21 at 06:32
0

If you want to go change the type of the collection from nullable type IEnumerable<T?> to non-null type IEnumerable<T> you can use .OfType<T>().

.OfType<T>() will remove null values and return a list of the type T.

Example: If you have a list of nullable strings: List<string?> you can change the type of the list to string by using OfType<string() as in the below example:

List<string?> nullableStrings = new List<string?> { "test1", null, "test2" };

List<string> strings = nullableStrings.OfType<string>().ToList();
// strings now only contains { "test1", "test2" }

This will result in a list of strings only containing test1 and test2.

Jogge
  • 1,654
  • 12
  • 36