0

I am using the bin bin predicatebuilder and have a query working but it is very slow when using .contains, i need it to do exact match instead of contains as it produces faster query when i test both directly on sql server. Ideally i want to be able to join 2 predicates.

This is what i had before.

  Expression<Func<MyModel, bool>> filter = null;
    var predicate = PredicateBuilder.True<MyModel>();

then i check an array of possible filter values like so:

if(!.string.IsNullOrEmpty(filterVal1))
    predicate = predicate.And(x=>x.someField = filterVal1)
if(!.string.IsNullOrEmpty(filterVal2))
  predicate = predicate.And(x=>x.someOtherField = filterVal2)

etc etc i build it up and then assign it to my filter which passes it through to my search function which retrieves all the data correctly ( I do a filter.Expand()) in there.

All works fine except when i want to check for a number of string values in a particular field the EF generated code is very convuleted and has a lot of nesting which slows it down. This is what i had in my predicate:

if (!string.IsNullOrEmpty(listOfValues))
   predicate = predicate.And(x => listOfValuesArray.Contains(x.someField.ToString()));

But if i hard code this sort of test: predicate.And(x=>x.someField=="listOfValuesArray1" || x.someField== "listOfValuesArray1"); etc the query produces by EF is much better and it all runs fast again.

So i need to be able to make the above hardcoded query dynamic in that i have an array/list f string values that i need to check in a paricular field as AND(field = value1 or field = value2 ...field=valueN)

I have tried to create 2 predicates and join them with AND(predicate1, predicate2) but i then get an Entity Framework error that tells me to use Expand() which I am already using. I tried to Comnpile() the predicates also.

Any help greatly appreciated Thanks

seco
  • 1
  • How large is `listOfValuesArray`? And in the alternative (with `||`) you don't do `ToString()`. That can make a huge difference. Does this predicate builder also have `predicate.Or`? If so, you can generate the or conditions. – Gert Arnold Aug 20 '14 at 20:48
  • @GertArnold listofValues is currently small, only 3 items, but it will graduallly increase to probably 7/9 possible values. I have used .ToString() so far in all my test (code above doesnt have it though). I tried doing Or as well but i want it all to be AND with the last bit to be AND(2ndpredicate) where the 2ndPredicate is an Or containing the possible values for a specific field but when i do that I get EF errors. – seco Aug 22 '14 at 00:45

0 Answers0