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

c# entity-framework-core

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 :)

1
1
9/4/2019 9:36:42 AM

Popular Answer

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.

0
9/4/2019 9:42:25 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow