CODEWITHSUNDEEP
c#linqlinq-to-sql
Question
Question

Reputation: 170

Linq skip query if string is Null

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

Upvotes: 0

Views: 2398

Answers (2)

Matthew Haugen
Matthew Haugen

Reputation: 13286

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.

Upvotes: 1

Jonesopolis
Jonesopolis

Reputation: 25370

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.

Upvotes: 0

Related Questions