Linq Query with Contains and Nullable Value

Question

I have a method for searching which looks like this:

public IEnumerable<Result> Search(string searchText)
{

     return _context.Person.Where(x => x.Contains(searchText));
}

I want to be able to call this function with searchText being null/empty and get all of the records back.

I have tried this with no luck:

return _context.Person.Where(x => x.Contains(searchText ?? ""));

is there another way to accomplish this besides breaking it up into two steps and checking searchString in an if statement before applying it to the query?

related (but not your specific problem here): http://stackoverflow.com/questions/682429/how-can-i-query-for-null-values-in-entity-framework — BlueRaja - Danny Pflughoeft, Apr 08 '11 at 14:33
Is `Contains` a method of your `Person` class? Which searches for FirstName and LastName and so on? How did you even get the original version `_context.Person.Where(x => x.Contains(searchText))` working with LINQ to Entities? Can you show this method? — Slauma, Apr 08 '11 at 16:45
@Sla http://weblogs.asp.net/zeeshanhirani/archive/2008/04/18/how-to-do-in-and-like-clause-in-linq-to-sql.aspx — stephen776, Apr 08 '11 at 17:04
I see, but the examples there are quite other types of queries than your case. In the link are queries like `Where(x => x.City.Contains(searchText))` which is the `Contains` method of a `string` (the EF provider can map that to SQL statements) and not a user defined method `Contains` on your `Person` class like you seem to have. (`x` in your code above is a `Person` and not a `string`.) — Slauma, Apr 08 '11 at 17:23
oh im sorry, i changed my code around for a quick example..mine is more like _context.Person.Where(x => x.LastName.Contains(searchText)) — stephen776, Apr 08 '11 at 18:26

score 9 · Answer 1 · answered Apr 08 '11 at 14:30

9

_context.Person.Where(x => string.IsNullOrEmpty(searchText) ? true : x.Contains(searchText));

answered Apr 08 '11 at 14:30

Mark Cidade

98,437
31
224
236

Nice and shortest way. Thanks. – Dilip Langhanoja Apr 11 '16 at 08:50

Diego · Accepted Answer · 2012-03-17T06:41:06.060

5

public IEnumerable<Result> Search(string searchText)
{
    if(string.IsNullOrEmpty(searchText))
        return _context.Person;
    else
        return _context.Person.Where(x => x.Contains(searchText));
}

edited Mar 17 '12 at 06:41

answered Apr 08 '11 at 14:30

Diego

19,494
4
32
46

score 5 · Answer 3 · edited Jun 29 '19 at 06:05

5

I try all solutions and just below solution worked for me

query = query.Where(e =>  e.CategoryType.HasValue && categoryTypes.Contains(e.CategoryType.Value));

Issues

edited Jun 29 '19 at 06:05

Ravi B

1,574
2
14
31

answered Jun 29 '19 at 03:31

mohammad hasani

51
1
1

score 1 · Answer 4 · answered Apr 08 '11 at 14:32

1

You can do it like this:

return _context.Person.Where(x => 
   string.IsNullOrEmpty(searchText) || 
   x.Contains(searchText)
);

This is a pattern I use a lot when I have parameters I want to apply only if they are set.

answered Apr 08 '11 at 14:32

Mikael Östberg

16,982
6
61
79

Bala R · Answer 5 · 2011-04-08T14:39:16.237

0

return _context.Person.Where(x =>string.IsNullOrEmpty(searchText) ? true : x.Contains(searchText));

or

public IEnumerable<Result> Search(string searchText)
    {
        return string.IsNullOrEmpty(searchText) ? _context.Person : _context.Person.Where(x => x.Contains(searchText));
    }

edited Apr 08 '11 at 14:39

answered Apr 08 '11 at 14:30

Bala R

107,317
23
199
210

score 0 · Answer 6 · answered Apr 08 '11 at 14:38

0

And a less efficient way... Not sure if this is semantically correct but you get the idea...

return _context.Person.Where((x, index) => x.Contains(searchText ?? x[index]));

answered Apr 08 '11 at 14:38

gt124

1,238
13
23

score 0 · Answer 7 · answered Apr 08 '11 at 14:55

Assuming that Person is a class Contains seems to be method of this class. An expression like Where(x => x.Contains(searchText)) or Where(x => string.IsNullOrEmpty(searchText) || x.Contains(searchText)) where x is a Person won't work at all with LINQ to Entities, even with a simple class like ...

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }

    public bool Contains(string searchText)
    {
        return Name.Contains(searchText);
    }
}

... it will throw an exception because LINQ to Entities can't translate this method to a storage expression. Where(x => string.IsNullOrEmpty(searchText) || x.Name.Contains(searchText)) would work though.

Linq Query with Contains and Nullable Value

7 Answers7