Constructing dynamic lambda

Question

Using EF6 + SQL Server 2008 R2 + .NET Fx 4.5.

I need to search a particular string in multiple fields of my entity and its related entities and return the records where the search string is found in ANY of the fields. Here's the relevant code:

Dim Query = MyDB.items.AsQueryable()

Query = Query.Where(Function(r) _
                              r.stock_no.Contains(searchString) OrElse _
                              r.serial_number.Contains(searchString) OrElse _
                              r.catalog_item.description.Contains(searchString) OrElse _                                 r.catalog_item.category.Contains(searchString) OrElse _
                              r.item_status.name.Contains(searchString) OrElse _
                              r.notes.Contains(searchString))

If r.issued_to_company_id.HasValue Then 'This is a nullable field (0-1 relation)
    Query = Query.OR_FUNCTION(Function(r) r.issued_to_company.name.Contains(searchString))
End If

The problem is that there is no OR_FUNCTION in LINQ. You can simulate an optional AND_FUNCTION by calling another Where on the Query object, but I need to do OR here.

I have tried to use If and IIf inside the predicate too, but seemingly can't do that without introducing a compile- or run-time error. What's the correct way of doing this?

score 2 · Answer 1 · edited May 23 '17 at 11:45

2

First Way

The tricky way would be to use the logic rule of A OR B equals NOT(NOT A AND NOT B):

' Assuming QueryAll holds all values
Query = QueryAll.Where(Function(r) _ 
                              Not r.stock_no.Contains(searchString) Andalso _
                              Not r.serial_number.Contains(searchString) Andalso _
                              Not r.catalog_item.description.Contains(searchString) Andalso _                                  Not r.catalog_item.category.Contains(searchString) Andalso _
                              Not r.item_status.name.Contains(searchString) Andalso _
                              Not r.notes.Contains(searchString))

' Query will hold all rows which DO NOT contain searchString.
Query = Query.Where(Function(r) Not r.issued_to_company.name.Contains(searchString))
' Here you should choose all elements in QueryAll which are not in Query. 
' The result would be all rows containing searchString.

Second Way

What about building a predicate using PredicateBuilder and than use it in your Where clause?

Dim pred = PredicateBuilder.True(Of MyClass)()    
pred = pred.And(Function(m As MyClass) m.SomeProperty = someValue)
pred = pred.Or(Function(m As MyClass) m.SomeProperty = someValue)

Code taken from here.

Third Way

Another possible way is by using Expression Trees to Build Dynamic Queries

edited May 23 '17 at 11:45

Community

1
1

answered Jun 16 '14 at 12:30

ilans

2,537
1
28
29

I'm trying to avoid any 3rd-party libraries. This is pretty basic problem and should be solvable within the bounds of framework. Thanks for the tip anyway. – dotNET Jun 16 '14 at 13:15
I think this might be what you are looking for (Use Expression Trees to Build Dynamic Queries): http://msdn.microsoft.com/en-us/library/vstudio/bb882637.aspx – ilans Jun 16 '14 at 13:36
Another way is to make some logical tricks. Like: NOT(NOT(A) AND NOT(B)) equals (A OR B). So just make your first statement with Not() and in the () put all elements with Not x.Contains("some string"). See the edited answer. – ilans Jun 16 '14 at 14:05
These are really good suggestions. I figured out another, somewhat simpler way of doing this. See my answer below. +1 for the great tricks. – dotNET Jun 17 '14 at 04:33
@dotNet, about your answer: I think the `True` in the `IF` statement should be `False`, shouldn't it? And another thing: If your goal is to make extra different calls to `Query.Where` - can it be done your way? – ilans Jun 17 '14 at 09:37
Correct. Thanks for pointing out. And yes to your second question. The whole `If` clause acts as a boolean expression, so you could append as many of them as you like using `OrElse`. – dotNET Jun 17 '14 at 10:37
In **ANOTHER** `Where' ?? – ilans Jun 17 '14 at 10:58

score 1 · Answer 2 · answered Jun 17 '14 at 04:32

Found it. You can actually use the new inline If syntax to include optional Or into the above query. Just like this:

Query = Query.Where(Function(r) _
           r.stock_no.Contains(searchString) OrElse _
           r.serial_number.Contains(searchString) OrElse _
           r.catalog_item.description.Contains(searchString) OrElse _
           r.catalog_item.category.Contains(searchString) OrElse _
           r.item_status.name.Contains(searchString) OrElse _
           r.notes.Contains(searchString) OrElse _
           If(r.catalog_item.manufacturer_id.HasValue, r.catalog_item.manufacturer_company.name.Contains(searchString), True))

Note that this works with Option Strict on, i.e. If can infer the type of return value as boolean. @ilanS's answers are good too, but this one seems simpler and more natural.

Constructing dynamic lambda

2 Answers2