Dynamic conditions: How to achieve multiple "OR" conditions with LINQ?
A common use for database querying in a page is to allow users to enter a list of keywords for searching. i.e. "Word1 Word2 Word3" along with some settings options where he can pick something like: [All words], [Any word], [Exact Phrase].
I used that approach to write a learning tool with LINQ.
Exact Prase was easily achieved by something like:
fromqincontext.MyView
whereq.MySearchField.Contains(TextBox.Text)
selectq
which translated to
select *fromMyViewwhereMySearchFieldlike'%xxxxxxx%'
IndexOf also worked and translated to CHARINDEX instead of LIKE. I also tested StartsWith() and EndsWith(). They all worked great.
Likewise I was able to perform the [All words] and liked the way LINQ handled that:
IQueryable<MyView> filter = context.MyView;
foreach(string wordin TextBox.Text.Split(' '))
filter = filter.Where(f => f.MySearchField.Contains(word));
Which translated to
select *fromMyView
whereMySearchFieldlike'%Word 1%'
andMySearchFieldlike'%Word 2%'
etc...
However I don't seem to be able to find a way to handle the [Any word] case in an elegant way. I'd like LINQ to generate ORs instead of ANDs.
I could achieve the same final result using Union() but of course that had a terrible performance. It would be nice if could pass a parameter indicating "OR" or "AND" to each .Where() call.
Is there a way to do that?
Thanks
Hi,
I found two possible ways for writing this, however one of them (the one that looks simplier) generates really ugly SQL code, so I don't recomend using it. The wrong way is to use Queryable.Union to merge several queries (one query for every keyword). This approach is similar to using Queryable.Where to merge multiple filters, but the resulting SQL code looks bad:
NorthwindDataContext db = new NorthwindDataContext();
var q1 = Queryable.Where(db.Products, (p) => p.ProductName.Contains("Chef"));
var q2 = Queryable.Where(db.Products, (p) => p.ProductName.Contains("Sir"));
var filter = Queryable.Union(q1, q2);
var q = from prod in filter
select new { prod.ProductName }; Is translated to:
SELECT [t3].[ProductName] FROM (
SELECT [t2].[ProductName] FROM (
SELECT [t0].[ProductName] FROM [Products] AS [t0]
WHERE [t0].[ProductName] LIKE @p0
UNION SELECT [t1].[ProductName] FROM [Products] AS [t1]
WHERE [t1].[ProductName] LIKE @p1) AS [t2]
) AS [t3]
To get better results, you can build whole expression tree used for filtering at runtime (using the method described by Matt Warren in his blog). The code for building your query will look like this:
NorthwindDataContext db = new NorthwindDataContext();
ParameterExpression p = Expression.Parameter(typeof(Product), "p");
// build as many conditions as you need
Expression t1 = Expression.CallVirtual(
typeof(string).GetMethod("Contains"),
Expression.Property(p, typeof(Product).GetProperty("ProductName")),
new Expression[] { Expression.Constant("Chef") });
Expression t2 = Expression.CallVirtual(
typeof(string).GetMethod("Contains"),
Expression.Property(p, typeof(Product).GetProperty("ProductName")),
new Expression[] { Expression.Constant("Sir") });
// merge conditions using Expression.Or
var test = Expression.Lambda<Func<Product, bool>>
(Expression.Or(t1, t2), new ParameterExpression[] { p });
var filter = Queryable.Where(db.Products, test);
var q = from prod in filter
select new { prod.ProductName }; This is a bit more difficult to generate, but the resulting SQL code is what you would expect.
One more thing to mention :-).
I already expermineted with LINQ and I wrote set of classes that enable some other features that are not directly supported by LINQ (for example you can re-use part of query witout copy&paste across multiple queries). This project shows how extensions to LINQ conversion can be implemented.
I had some free time, so I tried to implement what you asked for (ContainsAny and ContainsAll methods that can be translated to SQL), so you can write:
// Select products, that conain one of values from array in their name
var
q1 =from
p in
db.Products.ToExpandable()
where
p.ProductName.ContainsAny("Sir", "Chef")
selectnew
{ p.ProductName }; // Select products, that conain all of values from array in their name
var
q2 =from
p in
db.Products.ToExpandable()
where
p.ProductName.ContainsAll("Gu", "Ca")
selectnew
{ p.ProductName }; Some links:
PS: However, I hope that something like this may be available in future versions of LINQ without any extensions...
The first approach is the one I had already tried and as you said it produces poor SQL.
The second one was great, I had to do some massage to make it work for a list of words but your example showed the concept perfectly.
Thanks Tomas.
Here's how I got passed the multiple Expression.Or calls:
// Several expressions, one for each condition
List<Expression> expList = new List<Expression>();
foreach (string word in wordsWithForOrMoreLetters)
expList.Add
(
Expression.CallVirtual
(
typeof(string).GetMethod("Contains"),
Expression.Property(p, typeof(MyView).GetProperty("MyField")),
new Expression[] { Expression.Constant(word) }
)
);
// Create first expression
Expression<Func<MyView,bool>> condition =Expression.Lambda<Func<MyView,bool>>
(
expList[0],
new ParameterExpression[] { p }
);
// Merge using Expression.Or
for (int i = 1; i < expList.Count; i++)
condition =Expression.Lambda<Func<MyView,bool>>
(
Expression.Or(condition.Body, expList[ i ]),
newParameterExpression[] { p }
);
An ideal solution would exist if lambdas were composable, ie:
Expression <Func <MyView, bool>> predicate = f => f.MySearchField.Contains (word);
and then:
foreach (string wordin TextBox.Text.Split(' '))
predicate = f => predicate (f) || f.MySearchField.Contains (word);
then:
var result = MyView.Where (predicate);
Unfortunately this isn't possible. Here's an alternative that is possible:
var predicate = PredicateBuilder.Get (<MyView>());
foreach (string wordin TextBox.Text.Split(' '))
predicate = predicate.Or (f => f.MySearchField.Contains (word));
var result = MyView.Where (predicate);
-- if PredicateBuilder is defined as follows:
static class PredicateBuilder
{
public static Expression<Func<T, bool>> Get<T> () { return null; }
public static Expression<Func<T, bool>> Get<T> (this Expression<Func<T, bool>> predicate)
{
return predicate;
}
public static Expression<Func<T, bool>> Or<T> (this Expression<Func<T, bool>> expr, Expression<Func<T, bool>> or)
{
if (expr == null) return or;
Replace (or, or.Parameters[0], expr.Parameters[0]);
return Expression.Lambda<Func<T, bool>> (Expression.Or (expr.Body, or.Body), expr.Parameters);
}
public static Expression<Func<T, bool>> And<T> (this Expression<Func<T, bool>> expr, Expression<Func<T, bool>> and)
{
if (expr == null) return and;
Replace (and, and.Parameters[0], expr.Parameters[0]);
return Expression.Lambda<Func<T, bool>> (Expression.And (expr.Body, and.Body), expr.Parameters);
}
static void Replace (object instance, object old, object replacement)
{
for (Type t = instance.GetType (); t != null; t = t.BaseType)
foreach (FieldInfo fi in t.GetFields (BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance))
{
object val = fi.GetValue (instance);
if (val != null && val.GetType ().Assembly == typeof (Expression).Assembly)
if (object.ReferenceEquals (val, old))
fi.SetValue (instance, replacement);
else
Replace (val, old, replacement);
}
}
}
The Replace method is a tad grubby (divert your eyes :-) There's probably a better way of doing this. It does, though, allow queries like this:
var predicate = PredicateBuilder.Get<Customer> (c => c.Name.Contains ("A"));
predicate = predicate.Or (c => c.Name.Contains ("B"));
predicate = predicate.Or (c => c.Name.Contains ("C"));
predicate = predicate.Or (c => c.Name.Contains ("X") && c.Age > 30);
predicate = predicate.Or (c => c.Age > 50 && c.Age < 55);
var query = dataContext.Customers.Where (predicate);
The benefit is that each line can be wrapped around anif statement.
We're working on C# language syntax that will allow you to compose expression trees at runtime. However, Joe's solution looks good to me. :-)
Here's an updated version that uses Expression.Invoke instead of reflection:
static class PredicateBuilder
{
public static Expression<Func<T, bool>> Make<T> () { return null; }
public static Expression<Func<T, bool>> Make<T> (this Expression<Func<T, bool>> predicate)
{
return predicate;
}
public static Expression<Func<T, bool>> Or<T> (this Expression<Func<T, bool>> expr, Expression<Func<T, bool>> or)
{
if (expr == null) return or;
var invokedExpr = Expression.Invoke (or, expr.Parameters.Cast<Expression> ());
return Expression.Lambda<Func<T, bool>> (Expression.Or (expr.Body, invokedExpr), expr.Parameters);
}
public static Expression<Func<T, bool>> And<T> (this Expression<Func<T, bool>> expr, Expression<Func<T, bool>> and)
{
if (expr == null) return and;
var invokedExpr = Expression.Invoke (and, expr.Parameters.Cast<Expression> ());
return Expression.Lambda<Func<T, bool>> (Expression.And (expr.Body, invokedExpr), expr.Parameters);
}
}
var predicate = PredicateBuilder.Make<Customer> (c => c.Name.Contains ("A"));
predicate = predicate.Or (c => c.Name.Contains ("B"));
predicate = predicate.Or (c => c.Name.Contains ("C"));
predicate = predicate.Or (c => c.Name.Contains ("X") && c.Age > 30);
predicate = predicate.Or (c => c.Age > 50 && c.Age < 55);
var query = customers.Where (predicate);
Joe,
Can you modify it to work with anonymous type? With the current implementation, you have to declare the type beforehand. But if you are just creating an anonymous type on the fly, can PredicateBuilder still work with the anonymous type?
 Joe Albahari wrote: |
| Here's an updated version that uses Expression.Invoke instead of reflection: static class PredicateBuilder
{
public static Expression<Func<T, bool>> Make<T> () { return null; } public static Expression<Func<T, bool>> Make<T> (this Expression<Func<T, bool>> predicate)
{
return predicate;
} public static Expression<Func<T, bool>> Or<T> (this Expression<Func<T, bool>> expr, Expression<Func<T, bool>> or)
{
if (expr == null) return or;
var invokedExpr = Expression.Invoke (or, expr.Parameters.Cast<Expression> ());
return Expression.Lambda<Func<T, bool>> (Expression.Or (expr.Body, invokedExpr), expr.Parameters);
} public static Expression<Func<T, bool>> And<T> (this Expression<Func<T, bool>> expr, Expression<Func<T, bool>> and)
{
if (expr == null) return and;
var invokedExpr = Expression.Invoke (and, expr.Parameters.Cast<Expression> ());
return Expression.Lambda<Func<T, bool>> (Expression.And (expr.Body, invokedExpr), expr.Parameters);
}
} var predicate = PredicateBuilder.Make<Customer> (c => c.Name.Contains ("A"));
predicate = predicate.Or (c => c.Name.Contains ("B"));
predicate = predicate.Or (c => c.Name.Contains ("C"));
predicate = predicate.Or (c => c.Name.Contains ("X") && c.Age > 30);
predicate = predicate.Or (c => c.Age > 50 && c.Age < 55); var query = customers.Where (predicate); |
|
| doughboy wrote: |
| Can you modify it to work with anonymous type? |
|
Sure: add this method to PredicateBuilder:
public static Expression<Func<T, bool>> MakePredicate<T> (this IEnumerable<T> source)
{
return null;
}
Use it as follows:
var query =
from c in dataContext.Customers
select new { Name = c.Name };
var predicate = query.MakePredicate ();
predicate = predicate.Or (c => c.Name == "Tom");
predicate = predicate.Or (c => c.Name == "Harry");
query = query.Where (predicate);
Cheers
Joe
Thanks Joe.
A couple of questions...
1. why IEnumerable<T>? I would have thought IQueryable<T> instead but I don't really know what I'm talking about :)
2. I don't know if this is the limitation of LINQ, well at least for now, but what if I wanted to build the where clause in a separate method? What would the method signature look like? Given:
var q = from c in ctx.Contact select new { ContactId = c.ContactId, FirstName = c.FirstName, LastName = c.LastName }; // is this possible? q = BuildWhere(q, criteria); // criteria is some object containing filter information ... private IQueryable<T> BuildWhere(IQueryable<T> source, FilterCriteria criteria) { var pred = source.MakePredicate(); // using your new method to work with anonymous type pred = pred.And(c => c.FirstName.StartsWith(criteria.FirstName)); // compile time error here... ... source = source.Where(pred); return source; } I am basically stuck because inside BuildWhere method, for example, the compiler complains that FirstName (from c.FirstName) is not defined.
I hope it's clear what I am trying to accomplish but I am not sure whether it's even possible.
| Joe Albahari wrote: |
| doughboy wrote: | | Can you modify it to work with anonymous type? | | Sure: add this method to PredicateBuilder: public static Expression<Func<T, bool>> MakePredicate<T> (this IEnumerable<T> source)
{
return null;
} Use it as follows: var query =
from c in dataContext.Customers
select new { Name = c.Name }; var predicate = query.MakePredicate (); predicate = predicate.Or (c => c.Name == "Tom");
predicate = predicate.Or (c => c.Name == "Harry");
query = query.Where (predicate); Cheers Joe
|
|
In answer to your first question, it doesn't matter whether it's IEnumerable<T> or IQueryable<T> - that method's sole interest is the
T. IEnumerable<T> makes it slightly more versatile.
On your second question: instead of projecting and then filtering, go the other way round - filter first then project. That will allow you to change the "BuildWhere" method so it will accept an IQueryable<Customer> instead of IQuerable<T>. Then it will compile.
Joe
| doughboy wrote: |
| Joe, Can you modify it to work with anonymous type? With the current implementation, you have to declare the type beforehand. But if you are just creating an anonymous type on the fly, can PredicateBuilder still work with the anonymous type? | Joe Albahari wrote: | | Here's an updated version that uses Expression.Invoke instead of reflection: static class PredicateBuilder
{
public static Expression<Func<T, bool>> Make<T> () { return null; } public static Expression<Func<T, bool>> Make<T> (this Expression<Func<T, bool>> predicate)
{
return predicate;
} public static Expression<Func<T, bool>> Or<T> (this Expression<Func<T, bool>> expr, Expression<Func<T, bool>> or)
{
if (expr == null) return or;
var invokedExpr = Expression.Invoke (or, expr.Parameters.Cast<Expression> ());
return Expression.Lambda<Func<T, bool>> (Expression.Or (expr.Body, invokedExpr), expr.Parameters);
} public static Expression<Func<T, bool>> And<T> (this Expression<Func<T, bool>> expr, Expression<Func<T, bool>> and)
{
if (expr == null) return and;
var invokedExpr = Expression.Invoke (and, expr.Parameters.Cast<Expression> ());
return Expression.Lambda<Func<T, bool>> (Expression.And (expr.Body, invokedExpr), expr.Parameters);
}
} var predicate = PredicateBuilder.Make<Customer> (c => c.Name.Contains ("A"));
predicate = predicate.Or (c => c.Name.Contains ("B"));
predicate = predicate.Or (c => c.Name.Contains ("C"));
predicate = predicate.Or (c => c.Name.Contains ("X") && c.Age > 30);
predicate = predicate.Or (c => c.Age > 50 && c.Age < 55); var query = customers.Where (predicate); | | |
|
but i guess the ToString(); will have "invoke" inside, which is not the perfect solution, myself i went through revisiting all the expressions to replace the parameters of the second lambda with ones of the first. and i suggest that you change the visitor class to public cause it can be quite usefull...
Hi Matt,
For me this is the only thing really missing in Dlinq so if it's added that would be great! I would hate to go back to direct sql or use the to complicated Lamba stuff for all search forms. I could not find this in the March CTP examples ;( Is there a place with examples for "simple expression tree building"?
Tnxs,
Roger
Joe,
I've been studying how LINQ might replace the stringbuilder-based method of building a dynamic SQL statement. I've inherited an app that enables users to select multiple values from multiple lists and combine them using any combination of AND/OR/NOT. These conditions are stored in a table from which the WHERE clause is constructed on demand. Literally dozens of criteria can be selected, producing a WHERE clause only a T-SQL aficionado could love.
Your PredicateBuilder class appears to handle most needs I foresee, but a few remain, though perhaps only because I don't understand LINQ well enough. The first problem is the classic need to control precedence:
A or (B and C) vs. (A or B) and C.
SELECT ID
FROM MyTable
WHERE (Color IN ('Red','Blue') OR Style = 'Modern') AND Price < 10000
vs.
SELECT ID
FROM MyTable
WHERE Color IN ('Red','Blue') OR (Style = 'Modern' AND Price < 10000)
The second problem is employing set operators. How to I add UNION, INTERSECT, and EXCEPT to this mix dynamically (predicate.except...)? It is common for a user to ask 'Give me all items in set A, except those in set B', etc.
One final comment for the LINQ team folks: As you battle for the hearts and minds of C# and database developers (who craft those dreaded sprocs for which too many C# guys seem to have contempt), you have done yourselves a screaming disservice by generating SQL that reads 'SELECT * FROM MyTable...' when projection is not used. Every DB guy older than the age of 5 knows that this is a cardinal sin. While you can simply say, well then use projection to carve out only the data you need, introducing this bad practice into the midst of folks too many of which know much less about writing good T-SQL than they think they do is dangerous. Please update LINQ to write out all of the field names in the table if projection is not used. You also need to enable users to 'rip' stored procedures into C#/LINQ as a way to 1) make DBs comfortable that the right questions are being asked (are those sales figures accurate?) 2) enable DB developers to better assist C# guys, and 3) give both a better understanding of how to build real-world LINQ queries. It appears LINQ has some great features that are difficult to achieve in T-SQL unless you are very good at it, but job 1 should be insuring that current results are accurately replicated (yes, I know you can call a sproc in LINQ, but every C# guy on the planet thinks he knows better and will try to take the training wheels off day 1). Sorry for the rant.
Finally, thanks for your work Joe.
using Joe's predicate builder, the following code used to work in the 2006 May CTP
var products = db.Products;
var predicate = products.MakePredicate();
foreach(int productId in productIds)
{
predicate = predicate.Or(p => p.Id == productId);
}
foreach(Product prod in products.Where(predicate))
{
\\... do something ...
}
However in Orcas beta 2, the code needs to be modified to:
foreach(int productId in productIds)
{
int tmp = productId;
predicate = predicate.Or(p => p.Id == tmp);
}
without the local scope tmp variable, only the value of the last element in the productIds collection will appear in the predicate. not sure if this is a bug or a feature of the beta 2.
-Q at 2007-10-10 >
