Searching for Any Word & All Words in Linq to SQL ( C# & VB)

Yep this is old and not new, but is useful for non large DB's
So let’s say you have a text box with a search button, and some options (Search exact phrase, Search for any work & Search for all words). The first option will be direct and easy to implement but the other two, mmm, a lot of solutions can be found, what I liked is as follow:

   1:  public static class QueryExtensions

   2:  {

   3:      public static IQueryable<TEntity> LikeAny<TEntity>(

   4:          this IQueryable<TEntity> query,

   5:          Expression<Func<TEntity, string>> selector,

   6:          IEnumerable<string> values)

   7:      {

   8:          if (selector == null)

   9:          {

  10:              throw new ArgumentNullException("selector");

  11:          }

  12:          if (values == null)

  13:          {

  14:              throw new ArgumentNullException("values");

  15:          }

  16:          if (!values.Any())

  17:          {

  18:              return query;

  19:          }

  20:          var p = selector.Parameters.Single();

  21:          var conditions = values.Select(v =>

  22:              (Expression)Expression.Call(typeof(SqlMethods), "Like", null,

  23:                  selector.Body, Expression.Constant("%" + v + "%")));

  24:          var body = conditions.Aggregate((acc, c) => Expression.Or(acc, c));

  25:          return query.Where(Expression.Lambda<Func<TEntity, bool>>(body, p));

  26:      }

  27:      public static IQueryable<TEntity> LikeAll<TEntity>(

  28:          this IQueryable<TEntity> query,

  29:          Expression<Func<TEntity, string>> selector,

  30:          IEnumerable<string> values)

  31:      {

  32:          if (selector == null)

  33:          {

  34:              throw new ArgumentNullException("selector");

  35:          }

  36:          if (values == null)

  37:          {

  38:              throw new ArgumentNullException("values");

  39:          }

  40:          if (!values.Any())

  41:          {

  42:              return query;

  43:          }

  44:          var p = selector.Parameters.Single();

  45:          var conditions = values.Select(v =>

  46:              (Expression)Expression.Call(typeof(SqlMethods), "Like", null,

  47:                  selector.Body, Expression.Constant("%" + v + "%")));

  48:          var body = conditions.Aggregate((acc, c) => Expression.Or(acc, c));

  49:          return query.Where(Expression.Lambda<Func<TEntity, bool>>(body, p));

  50:      }

  51:  }

With ref. to:
http://stackoverflow.com/questions/2010639/linq-to-sql-any-keyword-search-query

And because the project I was working on was in VB, I thought this will help too:

   1:  Imports System.Linq.Expressions

   2:  Imports System.Data.Linq.SqlClient

   3:   

   4:  Public NotInheritable Class QueryExtensions

   5:      Private Sub New()

   6:      End Sub

   7:      '<System.Runtime.CompilerServices.Extension()> _

   8:      Public Shared Function LikeAny(Of TEntity)(ByVal query As IQueryable(Of TEntity), ByVal selector As Expression(Of Func(Of TEntity, String)), ByVal values As IEnumerable(Of String)) As IQueryable(Of TEntity)

   9:          If selector Is Nothing Then

  10:              Throw New ArgumentNullException("selector")

  11:          End If

  12:          If values Is Nothing Then

  13:              Throw New ArgumentNullException("values")

  14:          End If

  15:          If Not values.Any() Then

  16:              Return query

  17:          End If

  18:          Dim p = selector.Parameters.[Single]()

  19:          Dim conditions = values.[Select](Function(v) DirectCast(Expression.[Call](GetType(SqlMethods), "Like", Nothing, selector.Body, Expression.Constant("%" & Convert.ToString(v) & "%")), Expression))

  20:          Dim body = conditions.Aggregate(Function(acc, c) Expression.[Or](acc, c))

  21:          Return query.Where(Expression.Lambda(Of Func(Of TEntity, Boolean))(body, p))

  22:      End Function

  23:      Public Shared Function LikeAll(Of TEntity)(ByVal query As IQueryable(Of TEntity), ByVal selector As Expression(Of Func(Of TEntity, String)), ByVal values As IEnumerable(Of String)) As IQueryable(Of TEntity)

  24:          If selector Is Nothing Then

  25:              Throw New ArgumentNullException("selector")

  26:          End If

  27:          If values Is Nothing Then

  28:              Throw New ArgumentNullException("values")

  29:          End If

  30:          If Not values.Any() Then

  31:              Return query

  32:          End If

  33:          Dim p = selector.Parameters.[Single]()

  34:          Dim conditions = values.[Select](Function(v) DirectCast(Expression.[Call](GetType(SqlMethods), "Like", Nothing, selector.Body, Expression.Constant("%" & Convert.ToString(v) & "%")), Expression))

  35:          Dim body = conditions.Aggregate(Function(acc, c) Expression.And(acc, c))

  36:          Return query.Where(Expression.Lambda(Of Func(Of TEntity, Boolean))(body, p))

  37:      End Function

  38:  End Class

Note: Dont forget to include the references
and the using it will be somthing like this

   1:  string[] Words= new string[] { "one", "two", "three" };

   2:  var AnyWordResults = Query.LikeAny(s => s.Subject, Words);

   3:  var AllWordResults = Query.LikeAll(s => s.Subject, Words);