Search query using .Contains() for parameters that combine two columns

Question

I've got a table with FirstName and LastName and a query that looks somewhat like this:

var TheQuery = (from c in MyDC.Contacts
                where (c.FirstName.Contains(TheSearchParameter)  ||
                       c.LastName.Contains(TheSearchParameter))
                select c.ColumnID).Distinct().ToList();

If a user searches for John or for Smith, there'll be records returned but if the user searches for John Smith, no records come back. How does Contains() work and what would I need to change in my query for it to work as expected?

Answer 1

Rather than start playing with combinations of and, or, StartsWith, EndsWith etc., let analyze the main issue:

Search query using .Contains() for parameters that combine two columns

So the general answer is:

where Combine(table.Column1, table.Column2).Contains(TheSearchParameter)

The question is though what is the Combine function, and there is no general answer to that.

In your particular case, looks like you want to search for a Name which is combination of FirstName and LastName columns.

Even that combination is not generally defined (different languages have different name rules), but assuming you have in mind the most common name combinator:

Name = "{FirstName} {LastName}"

then the query is simply

var TheQuery = (from c in MyDC.Contacts
                where (c.FirstName + " " + c.LastName).Contains(TheSearchParameter)
                select c.ColumnID).Distinct().ToList();

For instance, if there is a Contact with FirstName: "John", LastName:"Smith", the above query will match "John", "Smith", "John Smith" but not "Smith John".

Answer 2

You might consider adding StartsWith method in your LINQ expression. StartsWith works similar to SQL where Column LIKE 'Something%'. You might add EndsWith also to make your search more wide

var TheQuery = (from c in MyDC.Contacts
                where (c.FirstName.Contains(TheSearchParameter)  ||
                       c.FirstName.StartsWith(TheSearchParameter) ||
                       c.FirstName.EndsWith(TheSearchParameter) ||
                       c.LastName.StartsWith(TheSearchParameter) ||
                       c.LastName.EndsWith(TheSearchParameter) ||
                       c.LastName.Contains(TheSearchParameter))
                select c.ColumnID).Distinct().ToList();