EF Core: where clause to check if at least one string column contains all values in an array

Question

To implement a basic search function on an entity, I'd like to check that at least one of multiple fields contain all the provided search terms.

To illustrate, let's imagine my entity is a Furniture with two string fields named Name and Description, and I have the following entries in my database:

    ID | Name           |  Description
    ---|----------------|------------
    1  | Black chair 1  | Black chair
->  2  | Red chair 1    | Crimson chair
->  3  | Red chair 2    | Dark red chair
->  4  | Black chair 2  | Black straight back chair, red cushion
    5  | Blue sofa      | Blue sofa
    6  | Red sofa       | Red sofa

and that I want to retrieve all the entities where any of the fields contain the two words red and chair (so here, 2, 3, and 4).

I can write this:

var search = new[] { "red", "chair" };
var filtered = _db.Furnitures.AsNoTracking().Where(f => {
       search.All(s => f.Name.ToLower().Contains(s)) 
    || search.All(s => f.Description.ToLower().Contains(s))
});

but EF Core warns me that this can't be translated to SQL, and that it will be evaluated locally.

Is there any way can I write this query so that it's evaluated in SQL?

Note: this is a simplified example of the issue I'm facing, and the model is obviously ridiculous, so please don't suggest a change in my entities :)

Generally, when a C# function doesn't have corresponding SQL translation, this warning message occurs. I tested in `Entity Framework 6`, this works, but not sure why doesn't work in EF Core. Perhaps EF Core cannot translate `.All` function with corresponding database function. — Moshi, Sep 04 '19 at 09:59

score 1 · Accepted Answer · answered Jun 01 '21 at 20:05

You can create a handful of criteria as expressions outside of your LINQ statement, and then apply them all to your query individually:

    var search = new[] { "red", "chair" };
    var criteria = search.Select(s => (Expression<Func<Furniture, bool>>)(f => f.Name.ToLower().Contains(s) || f.Description.ToLower().Contains(s)))
        .ToList();
    var query = criteria.Aggregate(
        _db.Furnitures.AsNoTracking().AsQueryable(),
        (query, criterion) => query.Where(criterion));

For more advanced situations (e.g. OR instead of AND), you may need to do some expression tree manipulation, as I describe here.

    var search = new[] { "red", "chair" };
    var criteria = search.Select(s => (Expression<Func<Furniture, bool>>)(f => f.Name.ToLower().Contains(s) || f.Description.ToLower().Contains(s)))
        .ToList();
    var query = _db.Furnitures.AsNoTracking().
        .Where(Join(Expression.And, criteria));

score 0 · Answer 2 · answered Sep 04 '19 at 09:42

0

Is there any way can I write this query so that it's evaluated in SQL?

The best option would be to use a raw query that you write yourself.

There is nothing faster or more flexible than using raw queries to select data from a SQL database. It gives you exactly what you want and avoids the overhead of processing and translating expressions at runtime.

answered Sep 04 '19 at 09:42

mm8

163,881
10
57
88

1

I'd like to avoid writing SQL in C# if possible, but I might write a stored procedure. Thanks for pointing this out. – Métoule Sep 04 '19 at 11:05

EF Core: where clause to check if at least one string column contains all values in an array

2 Answers2