what LINQ query will return results based on partial matches against arbitrary number of an entity's fields?

Question

consider the following code, which represents an attempt to implement partial matching. the intended result is a row for any 1 or more fields that match between the query entity and the data store.

so if you supply person.email we want a match against that, if you supply person.email and person.FirstName we should filter the results further, and so on.

        var results = from p in db.Persons
                      where p.CrookBookUserName.Trim().Contains(person.CrookBookUserName.Trim()) ||
                      p.email.Trim().Contains(person.email.Trim()) ||
                      p.FirstName.Trim().Contains(person.FirstName.Trim()) ||
                      p.LastName.Trim().Contains(person.LastName.Trim()) ||
                      p.phone.Trim().Contains(person.phone.Trim())
                      select p;

        return results;

unfortunately, this code always returns all rows in the db. why, and what should be the fix?

thanks in advance.

Answer 1

Does person have all fields populated? A p.email.Trim().Contains("") from an empty email address will return true for everything.

An extra (!String.IsNullOrEmpty(..)) && before each parameter, while verbose, will fix it if this is the problem.

Edit re comments:

Yeah, a helper method or something could help here... something like

public static bool ContainsIfNotEmptyTrimmed(this string source, string param)
{
    return !String.IsNullOrEmpty(param.Trim()) && source.Trim().Contains(param.Trim());
}

where p.CrookBookUserName.ContainsIfNotEmptyTrimmed(person.CrookBookUserName) || ...

Answer 2

You need to check if the user is providing empty strings.

var query = db.Persons;
if (!string.IsNullOrEmpty(person.CrookBookUserName.Trim()) {
    query = query.Where(p => p.CrookBookUserName.Trim().Contains(person.CrookBookUserName.Trim()));
}
if (!string.IsNullOrEmpty(person.email.Trim()) {
    query = query.Where(p => p.email.Trim().Contains(person.email.Trim()));
}
// etc...

return query;