Tuesday, July 05, 2011

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



No comments:

Post a Comment