Search with a comma in the value

Question

I have a search that the user inputs a string into. For example "Smith, George" to search a name in a database. This name is formatted in the database as "LastName" and "FirstName" so the records come out like this: "Smith" "George".

In the model I have concatenated the Last and First Name fields so that they will appear in the view like this "Smith, George"

What I need to do is parse out the comma and search the last name and first name fields separately.

This is the controller code that I have. It doesn't complete the search when both fields are entered just one or the other.

        [HttpGet]
    public ActionResult Index()
    {
        return View(_db.Owners.ToList());
    }

    [HttpPost]
    public ActionResult Index(string searchString)
    {
        var owners = from o in _db.Owners select o;

        if (!String.IsNullOrEmpty(searchString))
        {
            owners = owners.Where(o => o.LastName.Contains(searchString) || o.FirstName.Contains(searchString));
        }

        return View(owners);
    }

Answer 1

I strongly recommend you read this classic article: https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/

Your concatenated "{Last}, {First}" string is a view-level concern and should not be performed inside your database code. Your database and internal model object should store a person's name in some canonical format (such as possibly having separate fields (but all fields should be optional!) for Title, First, Middle, Last, Suffix, and any other appropriate cultural conventions for your locality or business domain needs.

Then use a single search box that supports some kind of field-based search syntax, to allow users to find people by free-text search but also by exact field if they know that they're looking for someone with "John" as their given-name instead of "Johnson" as their family name.

Don't forget to split for terms too. I recommend something like this:

1. Given String input = "O'Brien firstname:John";:
2. Filter out non-word characters (punctuation, etc). Normalize values (e.g. O'Brien -> OBrien), perhaps normalize to SOUNDEX too (SQL Server has SOUNDEX support built-in: https://learn.microsoft.com/en-us/sql/t-sql/functions/soundex-transact-sql?view=sql-server-2017)
3. Split into terms: [ any: "OBrien", firstname: "John" ] (a simple regular-expression could be used for this)
4. Build a Linq query for each term (i.e. adding new .Where expressions, note that concatenating .Where is equivalent to the AND operator. If you want to use multiple .Where as an OR operation you'll need PredicateBuilder, see here: How to dynamically add OR operator to WHERE clause in LINQ

Something like:

class Term {
    String Field;
    String Value;
}

String input = "O'Brien firstname:John";
Term[] terms = GetTerms( input ); // returns the array from step 3

IQueryable<Owner> query = db.Owners;
foreach( Term term in terms ) {

    switch( term.Field.ToUpperInvariant() ) {
        case null:
        case "ANY":
            query = query.Where( o => o.LastName.Contains( term.Value ) || o.FirstName.Contains( term.Value ) || o.Title.Contains( term.Value ) || o.Suffix.Contains( term.Value ) );
            break;
        case "FIRST":
            query = query.Where( o => o.FirstName.Cotains( term.Value ) );
            break;
        case "LAST":
            query = query.Where( o => o.LastName.Contains( term.Value ) );
            break;
        case "TITLE":
            // et cetera...
    }
}

List<Owner> results = query.ToList();