dynamically varied number of conditions in the 'where' statement using LINQ

Question

I'm working on my first project using LINQ (in mvc), so there is probably something very simple that I missed. However, a day of searching and experimenting has not turned up anything that works, hence the post.

I'm trying to write a LINQ query (Linq to SQL) that will contain a multiple number of conditions in the where statement separated by an OR or an AND. We don't know how many conditions are going to be in the query until runtime. This is for a search filter control, where the user can select multiple criteria to filter by.

select * from table
where table.col = 1 
    OR table.col = 2
    OR table.col = 7
    .... 'number of other conditions

Before I would just construct the SQL query as a string while looping over all conditions. However, it seems like there should be a nice way of doing this in LINQ. I have tried looking using expression trees, but they seem a bit over my head for the moment. Another idea was to execute a lambda function inside the where statement, like so:

For Each value In values
    matchingRows = matchingRows.Where(Function(row) row.col = value)

However, this only works for AND conditions. How do I do ORs?

Answer 1

AND is easy - you can just call Where in a loop. OR is much trickier. You mention SQL, so I'm assuming this is something like LINQ-to-SQL, in which case one way I've found to do this involves building custom Expression trees at runtime - like so (the example is C#, but let me know if you need help translating it to VB; my VB isn't fantastic any more, so I'll let you try first... you can probably read C# better than I can write VB).

Unfortunately, this won't work with EF in 3.5SP1 (due to the Expression.Invoke), but I believe this is fixed in 4.0.

Answer 2

I would use PredicateBuilder for this. It makes dynamic WHERE clauses very easy.

Answer 3

Something like this should work (forgive my VB):

Expression(Of Func(Of Something, Boolean)) filter = Nothing
ParameterExpression rowParam = Expression.Parameter("row", CType(Something))

For Each value In values
    filterPart = Expression.Equal( _
        Expression.Property(rowParam, "col"), _
        Expression.Constant(value)))
    If filter Is Nothing Then
        filter = filterPart 
    Else
        filter = Expression.OrElse(filter, filterPart)
    End If
Next

If newPredicate IsNot Nothing Then
    matchingRows = matchingRows.Where( _
        Expression.Lambda(Of Func(Of SomeType, Boolean))(filter, rowParam))
End If

No guarantees, however, my VB is a little rusty :-)

But PredicateBuilder might be a better solution if you want to do more complicated stuff than just Ands and Ors.