none
Building Conditional Linq to SQL query with OR operator RRS feed

  • Question

  • I've been trying to find out how to build a linq statement that works as a conditional OR, something like the following that produces OR instead of AND so that I can build a search from multiple text fields which may or may not be blank. Would appreciate any ideas.

    var results = from i in iRepository.FindAll()
      select i;
      if (a != "")
      results= results.Where(r => r.A.Contains(a));
      if (b != "")
      results= results.Where(r => r.B.Contains(b));
      if (c != "")
      results = results.Where(r => r.C.Contains(c));            
     return View(results);
    Wednesday, August 25, 2010 8:04 PM

Answers

All replies

  • Could you describe your scenario, also read this on using or instead of and

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/c4b4025a-1e95-4728-ba73-f7ae1146e2f4

    Regards

    Wednesday, August 25, 2010 8:17 PM
  • Linq-to-SQL has a nice built-in feature where it eliminates whatever can be eliminated client-side from queries.

    You can write the above 'and' example as:

    var results =
      from i in dc.SomeTable
      where (a == "" || i.A.Contains(a))
        && (b == "" || i.B.Contains(b))
        && (c == "" || i.C.Contains(c))
      select i;

    ...and if you want to switch from 'and' to 'or' between the different predicates, just change it to:

    var results =
      from i in dc.SomeTable
      where (a == "" || i.A.Contains(a))
        || (b == "" || i.B.Contains(b))
        || (c == "" || i.C.Contains(c))
      select i;


       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Class and property (re-)naming, Compare model <=> DB, Sync model <=> DB, Sync SSDL <=> CSDL
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    Thursday, August 26, 2010 3:14 AM
    Answerer
  • This seems to be the answer through reviewing this thread and the information at http://www.albahari.com/nutshell/predicatebuilder.aspx, but I was pulled into another project and not able to look into this right now.
    Friday, August 27, 2010 7:18 PM
  • Friday, August 27, 2010 9:52 PM
  • I was delayed in getting back on this project but wanted to note that PredicateBuilder seems to be the best approach to what I was trying to accomplish.

    I added the following PredicateBuilder class and built the search string using PredicateBuilder in the controller. this allowed me to build a search from multiple textboxes. Thanks so much for the help and I apologize that I was delayed in testing the solution and posting. 

    public ActionResult SearchResults(string txttitle, string txtacronym, string txtdescription)
        {
          var results = 
            from i in iRepository.FindLiveInstruments()
            select i;
          var predicate = PredicateBuilder.False<Instrument>();
    
          predicate = predicate.Or(i => i.Inst_Acronym.Contains(txtacronym));
          predicate = predicate.Or(i => i.Inst_Title.Contains(txttitle));
          predicate = predicate.Or(i => i.Inst_Abstract.Contains(txtdescription));
          results = results.Where(predicate);
    
          return View(results);
    
        }
    
    namespace XXX.Extensions
    {
    
      public static class PredicateBuilder
      {
        public static Expression<Func<T, bool>> True<T>() { return f => true; }
        public static Expression<Func<T, bool>> False<T>() { return f => false; }
        public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
                                  Expression<Func<T, bool>> expr2)
        {
          var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
          return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
        }
    
        public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
                                    Expression<Func<T, bool>> expr2)
        {
          var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
          return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
        }
      }
    }
    

     

    Wednesday, September 8, 2010 6:42 PM
  • To use Lists to do the filtering use the following.

     

    public List GetData(List Numbers, List Letters)
    {
    if (Numbers == null)
    Numbers = new List();
    if (Letters == null)
    Letters = new List();
    var q = from d in database.table
    where (Numbers.Count == 0 || Numbers.Contains(d.Number))
    where (Letters.Count == 0 || Letters.Contains(d.Letter))
    select new Data
    {
    Number = d.Number,
    Letter = d.Letter,
    };
    return q.ToList();
    }

    Monday, May 9, 2011 7:51 PM