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);
    }

Try something like: `var splitResults = string.Split(",", searchString)`. Then use `Trim()` on your results or do whatever else to get it in the right format. — EMUEVIL, Jul 20 '18 at 16:24
What happens if someone's name has a comma in it? https://shinesolutions.com/2018/01/08/falsehoods-programmers-believe-about-names-with-examples/ — Neil, Jul 20 '18 at 16:25
@Neil Well certainly he's going to have to do some validation somewhere if you wants to make 1 input box into two distinct search parameters. — EMUEVIL, Jul 20 '18 at 16:27
I haven't really seen a comma in someone's name apostrophe's yes but commas no. However I get the reasoning behind the question it "could" happen — , Jul 20 '18 at 16:28
@EMUEVIL then best practices would probably dictate it being two separate textboxes then? Just asking not trying to be argumentative — , Jul 20 '18 at 16:29
Have you met my friend "James Bond, III" ? https://www.imdb.com/name/nm0093875/ — Neil, Jul 20 '18 at 16:31
@Max Your guess is as good as mine regarding using two boxes or not. I'm not trying to discourage you from using a single search box, but consider the option if you think it'll simplify what you're trying to accomplish or be easier for the user to understand. — EMUEVIL, Jul 20 '18 at 16:38
Obligatory: https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/ — Dai, Jul 20 '18 at 16:39

Dai · Accepted Answer · 2018-07-20T19:37:20.693

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:

Given String input = "O'Brien firstname:John";:
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)
Split into terms: [ any: "OBrien", firstname: "John" ] (a simple regular-expression could be used for this)
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();

score 0 · Answer 2 · answered Jul 20 '18 at 16:49

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

[HttpPost]
public ActionResult Index(string searchString)
{
    var owners = from o in _db.Owners select o;
    var lastName = searchString.Split(',')[0];
    var firstName = searchString.Split(',')[1].TrimStart();

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

    return View(owners);
}

Search with a comma in the value

2 Answers2