Chain together multiple complex WHERE clauses in LINQ to SQL

Question

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 simple example to outline the problem because I'm looking to solve a range of domain problems with this chaining-.Where()s together.

Answer 1

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

Answer 2

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.