Chain together multiple complex WHERE clauses in LINQ to SQL
This is the pseudo-SQL I want to generate:
SELECT * FROM Table WHERE Column1 = @Value1 OR Column2 = @Value2
The problem is, sometimes the second one should not be included. I was hoping to chain together .Where()
clauses like so:
var query = context.TableName;
query = query.Where(t => t.Column1 == value1);
if (NeedsToBeIncluded(value2))
query = query.Where(t => t.Column2 == value2);
Unfortunately, this doesn't work. .Where()
will emit an AND
if you chain them together by default. Is there a way to get it to emit an OR
?
I'm looking for something along the lines of:
var query = context.TableName;
query = query.Where(t => t.Column1 == value1);
if (NeedsToBeIncluded(value2))
query = query.OrWhere(t => t.Column2 == value2);
UPDATE
Ok, so my example listed above is too simple. It was merely supposed to be an example that outlines the problem space. Out "in the wild" so to speak, Column1 and Column2 could actually be "CarType" and "OwnerName", maybe there's more, maybe there's less. I just used a si开发者_JAVA技巧mple example to outline the problem because I'm looking to solve a range of domain problems with this chaining-.Where()
s together.
One way is to use LINQKit's PredicateBuilder.
Another way is to use a list:
var values = new List<string> { value1 };
if (NeedsToBeIncluded(value2)) values.Add(value2);
query = context.TableName.Where(t => values.Contains(t));
PB is more flexible, but the list will solve the problem in your question. Note that you need EF 4 for Contains
.
I gave an example how to dynamically build a condition yesterday - see here. For your case it would be something like that.
var parameter = Expression.Parameter(typeof(TableName), "t");
Expression condition = Expression.Equal(
Expression.Property(parameter, "Column1"),
Expression.Constant(value1)));
if (NeedsToBeIncluded(value2))
{
condition = Expression.OrElse(
condition,
Expression.Equal(
Expression.Property(parameter, "Column2"),
Expression.Constant(value2)));
}
var expression = Expression.Lambda<Func<TableName, Boolean>>(condition, parameter);
var query = context.TableName.Where(expression);
精彩评论