Linq to find Firstname and Lastname

Can anyone suggest how I can write a linq query to find users in a table with a single search field?

Users can enter 'Name' in a single text box in the UI (e.g. 'Joe Bloggs', or 'Will Green Smith'). I want to split that value up and search both Firstname and Lastname columns using 'like' functionality. So something like this:

select * from Users 
where 
Firstname like '%Joe%' or Firstname like '%Bloggs%' 
or Firstname like '%Will%' or Firstname like '%Green%' 
or Firstname like '%Smith%' 
or Lastname like '%Joe%' or Lastname like '%Bloggs%' 
or Lastname like '%Will%' or Lastname like '%Green%' 
or Lastname  like '%Smith%' 

What would be the best way to achieve the same effect in linq?

Thanks

You could build up an expression during runtime. I assume you have the following class:

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

As an input, you might have an array like this:

var searchPattern = new string[] { "Joe", "Bloggs", "Will", "Green", "Smith" };

Then we create two expressions for each element of the searchPattern array. The first expression will compare the string with the FirstName property, the second expression will compare the string with the LastName property:

var parameterExpression = Expression.Parameter(typeof(User));
var expressions = searchPattern.Select(x => Expression.Equal(Expression.Property(parameterExpression, "FirstName"), Expression.Constant(x, typeof(string))))
			.Concat(searchPattern.Select(x => Expression.Equal(Expression.Property(parameterExpression, "LastName"), Expression.Constant(x, typeof(string)))));

All these expressions are then combined with OrElse to a big expression:

var aggregated = expressions.Aggregate((x,y) => Expression.OrElse(x,y));

We have to convert this BinaryExpression to an Expression<Func<User,bool>> if we want to use it with LINQ:

var expression = Expression.Lambda<Func<User,bool>>(aggregated, new ParameterExpression[] { parameterExpression });

In Entity Framework, this can be used directly:

var result = context.Users.Where(expression);

If you use Linq2Objects, you have to create a function first:

var result = users.Where(expression.Compile());

The code would create a SQL query with Firstname = 'Joe' instead Firstname like '%Joe%'. If you want to use like, the expressions will become a bit more complicated. You will have to call the Contains method of the string class. This will lead to the following expressions (which have to be aggregated as above):

var expressions = searchPattern.Select(x => Expression.Equal(Expression.Call(Expression.Property(parameterExpression, "FirstName"), typeof(string).GetMethod("Contains"), new Expression[] { Expression.Constant(x, typeof(string)) }), Expression.Constant(true, typeof(bool))))
			.Concat(searchPattern.Select(x => Expression.Equal(Expression.Call(Expression.Property(parameterExpression, "LastName"), typeof(string).GetMethod("Contains"), new Expression[] { Expression.Constant(x, typeof(string)) }), Expression.Constant(true, typeof(bool)))));

Online-demo: https://dotnetfiddle.net/cM0tz0