How to simplify repetitive OR condition in Where(e => e.prop1.contains() || e.prop2.contains() || …)

I have a lot of code like this:

IQueryable<MyEntity> query...
query.Where(e => EF.Functions.Like(e.Property1,  pattern)
                || EF.Functions.Like(e.Property2, pattern)
                || EF.Functions.Like(e.Property3, pattern)
                ...);

How can I simplify it to remove the repetitive code to something like this:

query.Where(Search(pattern, e => e.Property1, e => e.Property2, e => e.Property3));  

or

query.Where(e => Search(pattern, e.Property1,  e.Property2,  e.Property3))  

EDIT: The query must be translated to SQL by EF, so the where condition can’t be just any function that return boolean. It must be translatable Expression<Func<TEntity, bool>>

Answer

You can rewrite it to combine all the properties into an array and check that for a match:

query.Where(e =>
    new[]{ e.Property1, e.Property2, e.Property3}
      .Any(p => EF.Functions.Like(p, pattern) )
);

The equivalent SQL is:

WHERE EXISTS (SELECT 1
    FROM (VALUES
      (e.Property1),
      (e.Property2),
      (e.Property3)
    ) v(Prop)
    WHERE v.Prop LIKE @pattern
)

The equivalent &&/AND semantic is slightly different:

query.Where(e =>
    new[]{ e.Property1, e.Property2, e.Property3}
      .All(p => EF.Functions.Like(p, pattern) )
);

The equivalent SQL is a double-negative:

WHERE NOT EXISTS (SELECT 1
    FROM (VALUES
      (e.Property1),
      (e.Property2),
      (e.Property3)
    ) v(Prop)
    WHERE v.Prop NOT LIKE @pattern
)

Leave a Reply

Your email address will not be published. Required fields are marked *