Search multiple column values with one search string

Question

I have this query where I want to return results if the search string provided yields data from the 'FirstName' and 'LastName' column of my database. They each work individually as I can get results for Firstname = 'john' and LastName = 'doe'. I also want to be able to pass in a search string of 'John doe' and get results. How would I implement this using .net/linq

snippet: var query = _context.Players.Where(p => p.Firstname.Contains(searchString.ToLower().Trim()) || p.Lastname.Contains(searchString.ToLower().Trim()));

Answer 1

use Split function like the following:

var parts = searchString.Split();
snippet: var query = _context.Players.Where(
     p => p.Firstname.Contains(parts[0].ToLower().Trim())
       || p.Lastname.Contains(parts[1].ToLower().Trim()));

Extracted from the official docs:

If the separator parameter is null or contains no characters, white-space characters are assumed to be the delimiters.

Answer 2

Separating the input data is also convenient

  var parts = searchString.Split();
    var partOne = parts[0].ToLower().Trim();
    var partTwo = parts[1].ToLower().Trim()
    
    var query = _context.Players.Where(
         p => p.Firstname.Contains(partOne)
           || p.Lastname.Contains(partTwo));

Answer 3

Created an extension method class to isolate the functional parts of search algo. This is based on pattern matching algo. You can try this one once.

public class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

static class Extensions
{
    public static void Sanitize(this string item)
    {
        Regex rgx = new Regex("[^a-AA-Z0-9 -]");
        item = rgx.Replace(item, " ");
    }
    
    public static string GetPipedString(this string item) 
    {
        StringBuilder builder = new StringBuilder();
        item.Split(' ').ToList().ForEach(x => builder.Append('|').Append(x));
        builder.Remove(0, 1);
        return builder.ToString();
    }
    
    public static IEnumerable<Person> FindPlayers(this IEnumerable<Person> persons, string searchKey)
    {
        searchKey.Sanitize();
        string pattern = string.Format(@"^?:{0}\w*$", searchKey.GetPipedString());
        return persons.Where(x => Regex.IsMatch(
                              string.Join(string.Empty, 
                              new List<string>() { x.FirstName, x.LastName }),
                              pattern,
                              RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace));
    }
}

class Program
{
    static void Main(string[] args)
    {
        /* Assuming peoples is the IEnumerable<Person>.
           Anyways there is an explicit sanitization of the string to remove the non alphanum characters*/
        var items = peoples.FindPlayers("ANY DATA SPACE SEPARATED").ToList();
    }
}