IQueryable.Where make it an OR.

Question

IQueryable<Table1> tab1 = ctx.Table1s;

foreach (String m in myListOfStrings)
{
  tab1 = tab1 .Where(a => a.Code.Contains(m));
}
the sql this produces uses ANDs, I would like ors.  Is there a way to accomplish this with ors?
 
 The sql looks like this...
 select *
 from table1
 where code like '%val1%' AND code like '%val2%' ...etc
 
 I want 
 select *
 from table1
 where code like '%val1%' OR code like '%val2%' ...etc
 

Answer 1

Hi,

Use the PredicateBuilder. Here is a Northwind based example:

IQueryable<Customers> tab1 = Customers;
List<string> myListOfStrings = new List<string> {"ALFKI", "ANATR", "ANTON"};
var predicate = PredicateBuilder.False<Customers>(); 

foreach (String m in myListOfStrings)
{
 string n = m;
 predicate = predicate.Or(c => c.CustomerID.Contains(n));
}

tab1 = tab1.Where(predicate);

The  PredicateBuilder's source code is available and can be included in your application.

Using PredicateBuilder:
http://www.albahari.com/nutshell/predicatebuilder.aspx

 

Marcel

Answer 2

See:

http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/fb9e1bb2-cc8d-4c27-af66-10586854af1e#0561cef8-a14a-46ce-b88d-737835b01c87

---

Kristofer - Huagati Systems Co., Ltd. Cool tools for Linq-to-SQL and Entity Framework: huagati.com/dbmltools - add-in with new useful features for the L2S and EF designers in VS2008 and VS2010 huagati.com/L2SProfiler - Query profiler for Linq-to-SQL, Entity Framework v4, and LLBLGen Pro

Answer 3

Hi jbuzz,

You can follow Marcel’s suggestion, use PredicateBuilder to implement what you need.  Here is a complete example based on NorthWind database.

private void button1_Click(object sender, EventArgs e)
{
  using (NorthWindDataContext ctx = new NorthWindDataContext())
  {
    List<string> myListOfStrings = new List<string> { "fish", "Seaweed" };

    var predicate = PredicateBuilder.False<Category>();
    foreach (string keyword in myListOfStrings)
    {
      string temp = keyword;
      predicate = predicate.Or(p => p.Description.Contains(temp));
    }

    IQueryable<Category> query = ctx.Categories.Where(predicate);

    //You can check the Commmand Text here 
    Console.WriteLine(ctx.GetCommand(query).CommandText);

    this.dataGridView1.DataSource = query;
  }
}

Below is the PredicateBuilder class.

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

 

If you have other question, please feel free to let me know.

 

Best regards,

Alex Liang

MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 4

Thanks for the responses.   The predicate builder does not work for me (never see it in my wheres).   However I am sure I am doing something wrong, and it pushes me the right direction.   I will play around with it some more.

 

Thanks Again.

Answer 5

Ok finally had time to take a look at this again, and this time got it to work.   Basically what I was doing wrong was I was adding my predicate to my linq table 

    IQueryable<Category> query = ctx.Categories.Where(predicate);

Then adding where conditions to query.   To get it to work I had to build my andPredicate, my orPredicate and then put them together..

    IQueryable<Category> query = ctx.Categories.Where(andPredicate.And(orPredicate));

Just wanted to add this to the post in case others are struggling with this.

Thanks for all the help.

Answer 6

How did you build your own and or predicate? i just want to know how to put both together cause im having a hard time doing this. 

Answer 7

Daniel,

Theres really nothing mysterious here. If I were to extend Alex's example from above:

List<string> myListOfStrings = new List<string> { "fish", "Seaweed" };
var orPredicate = PredicateBuilder.False<Categories>();
foreach (string keyword in myListOfStrings)
{
  string temp = keyword;
  orPredicate = orPredicate.Or(p => p.Description.Contains(temp));
}
IQueryable<Categories> query = Categories.Where(orPredicate.And(p => p.CategoryName == "Seafood"));
//

this would result in the following SQL:

-- Region Parameters
DECLARE @p0 NVarChar(6) = '%fish%'
DECLARE @p1 NVarChar(9) = '%Seaweed%'
DECLARE @p2 NVarChar(7) = 'Seafood'
-- EndRegion
SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture]
FROM [Categories] AS [t0]
WHERE (([t0].[Description] LIKE @p0) OR ([t0].[Description] LIKE @p1)) AND ([t0].[CategoryName] = @p2)
-- 

So you're basically repeatedly stacking and/or conditions.

Please have a look at PredicateBuilder's documentation:
http://www.albahari.com/nutshell/predicatebuilder.aspx

Marcel