Iterable disjunction in LINQ

Question

Let's assume I have an IQueryable collection, and list of some strings.

I can build query this way:

foreach (var somestring in somestrings)
{
     collection = collection.Where(col=>col.Property.Contains(somestring);
}

which will produce following SQL query:

SELECT ...... FROM ..... WHERE 
(Property LIKE '%something1%') AND 
(Property LIKE '%something2%') AND 
(Property LIKE '%something3%')

Note, that WHERE clauses are connected with ANDs.

Is there way, to construct similar query, but connected with ORs ?

The performance of that query would be very bad, whether you use `AND` or `OR`. Instead of using LINQ you should consider using full text search queries and indexes. The current query will scan the entire table to find matches without any benefit from indexes — Panagiotis Kanavos, May 28 '19 at 08:18

score 3 · Answer 1 · answered May 28 '19 at 08:15

3

You can do this in one query without looping using Any:

var result = collection
    .Where(col => somestrings
        .Any(s => col.Property.Contains(s)));

Or the same query with simplified syntax:

var result = collection
    .Where(col => somestrings
        .Any(col.Property.Contains));

answered May 28 '19 at 08:15

DavidG

113,891
12
217
223

Nice! But the performance will be *really* bad. That's one place where LINQ isn't appropriate. Unless it's LINQ to Elastic or another search engine – Panagiotis Kanavos May 28 '19 at 08:20
Not possible in LINQ-to-SQL. – Gert Arnold May 28 '19 at 08:22
Does not fit best my situation - but this is really beatifull solution! – Jakub Szułakiewicz May 28 '19 at 08:57

score 1 · Accepted Answer · answered May 28 '19 at 08:27

You can use a PredicateBuilder like this. See SO for more.

Here you can connect queries with AND or OR.

IQueryable<Product> SearchProducts (params string[] keywords)
{
  var predicate = PredicateBuilder.False<Product>();

  foreach (string keyword in keywords)
  {
    string temp = keyword;
    predicate = predicate.Or (p => p.Description.Contains (temp));
  }
  return dataContext.Products.Where (predicate);
}

Sample taken from C# 7.0 in a Nutshell

I've used this successfully when implementing custom search fields

eg.

[red blue] -> searches for red AND blue

[red, blue] -> searches for red OR blue

Iterable disjunction in LINQ

2 Answers2