Search for entire name based on three columns with LINQ

Question

I am trying to make a search method using LINQ in Visual Studio. In my database, I have the three fields "Firstname", "LastNamePrefix" and "Lastname". My searchstring is Searchtext. How can I make a simple LINQ query which searches all the fields?

What I have at the moment is this:

query = query.Where(x => x.FirstName.Contains(input.SearchText) || x.LastNamePrefix.Contains(input.SearchText) || x.LastName.Contains(input.SearchText));

With this I come really close to the solution but when I try to combine Firstname with LastNamePrefix and Lastname, I don't get any result.

For example with the name Jan van Lauw:

I search Jan. It works
I search van. It works
I search Lauw. It works
I search Jan van Lauw. No results. 

I tried to make a column which combines the three columns to a fullname with the following function:

public static string CreateFullname(string firstName, string lastNamePrefix, string lastName)
{
    return $"{firstName} {lastNamePrefix} {lastName}".Replace("  ", " ").Trim();
}

If I try to use this column like the code below, I get the following error:

The specified type member 'FullName' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

query = query.Where(x => x.FullName.Contains(input.SearchText));

How can search in multiple columns?

Answer 1

You cannot use custom (not mapped) properties, like FullName, in queries, but you can combine your properties inline:

query.Where(x => (x.FirstName.Trim() + " " + x.LastNamePrefix.Trim() + " " + x.LastName.Trim()).Trim().Contains(input.SearchText));