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?

Community · Accepted Answer · 2017-05-23 11:45:13Z

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, 2017 at 11:45

CommunityBot

11 silver badge

answered Jun 16, 2014 at 12:30

ilans

2,7671 gold badge31 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

dotNET Over a year ago

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.

ilans Over a year ago

I think this might be what you are looking for (Use Expression Trees to Build Dynamic Queries): msdn.microsoft.com/en-us/library/vstudio/bb882637.aspx

ilans Over a year ago

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.

dotNET Over a year ago

These are really good suggestions. I figured out another, somewhat simpler way of doing this. See my answer below. +1 for the great tricks.

ilans Over a year ago

@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?

|

dotNET · Accepted Answer · 2014-06-17 04:32:00Z

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.

Collectives™ on Stack Overflow

Constructing dynamic lambda

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related