Linq skip query if string is Null

Question

I am trying to implement a search function, but am running into problems when some of the fields are not filled in by a user.

string country = searchCountry.Text.ToLower();
string state = searchState.Text.ToLower();

var searchLocation= (from h in db.Locations where (!string.IsNullOrWhiteSpace(country) ? h.Country.ToLower().Contains(country):false)
              && (!string.IsNullOrWhiteSpace(state) ? h.State.ToLower().Contains(state) : false)
              select h);

The problem is that when one of the strings is empty the searchLocation returns nothing and only works when both fields are filled in. I Have tried replacing the && with || but then it will get results, even if one of the search terms is not in the db.

Is there a way to do this, besides Filtering out null values in a linq search

What are your objections against Filtering out null values in a linq search? — Gert Arnold
– Gert Arnold, Commented Jan 8, 2016 at 21:41
@Gert I have nothing against filtering out null values, I was just hoping there was a way to do it in one statement and not multiple if statements. — Question
– Question, Commented Jan 8, 2016 at 21:58
The advantage of composing the query is that the SQL statement can be smaller and doesn't contain unnecessary elements. — Gert Arnold
– Gert Arnold, Commented Jan 9, 2016 at 10:08

Matthew Haugen · Accepted Answer · 2016-01-08 21:31:45Z

1

This will return any locations where either the country is empty or it matches, and either the state is empty or it matches.

var searchLocation= (from h in db.Locations
                     where (string.IsNullOrWhiteSpace(country) || h.Country.ToLower().Contains(country))
                           && (string.IsNullOrWhiteSpace(state) || h.State.ToLower().Contains(state))
                     select h);

It would help to have a bit more description of what you'd like to put in and get out, but this seems logical to me.

Either field would be optional, but it would filter results to include anything that matched all (one or two) filled in fields.

Of course, if you run this without any filters, it will return all locations. So keep that in mind if you're making requests to the database. If that's desired behavior, it might make sense to just pull all your data into a list beforehand, rather than querying every time anything is typed.

answered Jan 8, 2016 at 21:31

Matthew Haugen

13.4k5 gold badges41 silver badges55 bronze badges

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

2 Comments

Question Over a year ago

Thanks, works perfectly, I will just have to remember to add the check if all filters are empty.

Matthew Haugen Over a year ago

@user2974900 Yeah, I'd probably just do that outside, as a few people have sort of suggested, in an if block. I don't know if that's what you mean, but you could put it in the query itself, but that would be unnecessary overhead.

Jonesopolis · Accepted Answer · 2016-01-08 21:29:55Z

0

I believe you're overthinking this. Just validate the fields before searching:

string country = searchCountry.Text.ToLower();
string state = searchState.Text.ToLower();

if(string.IsNullOrWhitespace(state) || string.IsNullOrWhitespace(country))
{
   //MessageBox.Show...
   return;
}

var searchLocation= //query with validated fields

It's a very good idea to validate your input before trying to perform actions against it. And it makes your code more readable than combining the two.

answered Jan 8, 2016 at 21:29

Jonesopolis

25.4k12 gold badges72 silver badges115 bronze badges

Collectives™ on Stack Overflow

Linq skip query if string is Null

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related