Query with nested Any() translation error

Question

I'm trying to write a semi-advanced LINQ to SQL query to search through my entities in a .NET 6 project. The filtering in my LINQ statement looks something like this:

List<string> _searchList = new() {"%a%", "%b%"};

 var _query = (from tblHeader in _DbContext.batches
               where tblHeader.isDeleted != true
               select tblHeader)

_query = _query.Where(x => 
    _searchList.All(y =>
        EF.Functions.Like(x.Name, y)
    )
);

var _results = await _query.ToListAsync();

The Error Looks like:

The LINQ expression 'y => __Functions_1
    .Like(
        matchExpression: EntityShaperExpression: 
            FFM.DataAccessModels.App.batches
            ValueBufferExpression: 
                ProjectionBindingExpression: EmptyProjectionMember
            IsNullable: False
        .Name, 
        pattern: y)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Is it possible to use the LINQ All() (or even the linq Any()) within a Where()? Or is there a better way to write this query?

For this query you can just loop over search list and do Where multiple times (it will join your where conditions with and). — Evk, Oct 20 '21 at 20:20
LINQ to Entities only supports `Contains` when using local collections. Use [this my solution](https://stackoverflow.com/a/67666993/10646316), it will create desired SQL. — Svyatoslav Danyliv, Oct 20 '21 at 21:24
@Evk This is my current solution, however I'm trying to incorporate being able to do OR searching (i.e. search for "user input A" OR "alernative user input B"), matching each word in search 1 OR each word in search 2. With this, my desired query also contains another nested .All() operator, but in my question I simplified it to where if first starts to break. — wittabeast, Oct 21 '21 at 12:55
For this unfortunately there is no "built-in" support. You need to create expressions manually, but you can use some third party libraries like LinqKit — Evk, Oct 21 '21 at 13:03
@Evk Is there any way to incorporate this OR capability with a loop as you first suggested? — wittabeast, Oct 21 '21 at 14:42
Yes but not built-in as I said (as far as I know). For example with third party library: https://github.com/scottksmith95/LINQKit#predicatebuilder — Evk, Oct 21 '21 at 14:46
@wittabeast https://stackoverflow.com/questions/457316/combining-two-expressions-expressionfunct-bool — Servy, Oct 21 '21 at 16:07

score 0 · Answer 1 · answered Oct 21 '21 at 13:57

0

Because the solution you want is All items in _searchList to match the query can be re-written using a Where clause for each item in _searchList using a loop and the result will still be a single query.

List<string> _searchList = new() {"%a%", "%b%"};

var _query = _DbContext.batches.Where(x => !x.isDeleted);

foreach(var searchItem in _searchList)
    _query = _query.Where(x => EF.Functions.Like(x.Name, searchItem);


var _results = await _query.ToListAsync();

answered Oct 21 '21 at 13:57

Jeffrey Parks

554
4
14

Problem here - HOW to make OR. – Svyatoslav Danyliv Oct 21 '21 at 15:53
You would have to use expression trees like you described in your answer to do an OR, however I was answering the question that was asked. – Jeffrey Parks Oct 21 '21 at 16:52

Svyatoslav Danyliv · Answer 2 · 2021-10-21T20:16:41.257

0

Use this my answer for extension method FilterByItems. Then you can do the following:

List<string> _searchList = new() {"%a%", "%b%"};

var _query = 
    from tblHeader in _DbContext.batches
    where tblHeader.isDeleted != true
    select tblHeader;

_query = _query
    .FilterByItems(_searchList, (x, y) => EF.Functions.Like(x.Name, y), true);

var _results = await _query.ToListAsync();

edited Oct 21 '21 at 20:16

answered Oct 21 '21 at 15:58

Svyatoslav Danyliv

21,911
3
16
32

@Svyatslav Danyliv This solution works well. Now let's say I have a variable list of phrases that I want to search for instead of just singular words (i.e. search for "user input A" OR "alernative user input B" OR "Third Input Search", instead of "a" OR "b"). I would want to match each word in any one of the phrases. Can this be extended to handle this scenario? – wittabeast Oct 21 '21 at 18:24
In this case you have to work with `PredicateBuilder` you can extract it from [LINQKit](https://github.com/scottksmith95/LINQKit) or just add NuGet package. If you want a sample, create another question. – Svyatoslav Danyliv Oct 21 '21 at 20:12
A sample would be great! I posted a new question [here](https://stackoverflow.com/questions/69677073/multi-phrase-search-in-c-sharp-linq-cannot-be-translated-to-sql) – wittabeast Oct 22 '21 at 12:43

MestreDosMagros · Answer 3 · 2021-10-20T20:26:28.940

Can't you do this?

 var result = _DbContext.batches
     .Where(tblHeader => tblHeader.isDeleted != true)
     .Where(x => _searchList.All(y => EF.Functions.Like(x.Name, y))
     .ToListAsync();

Edit:

The like query supports wildcards, i suppose you need to match all the results that contains 'a' and 'b', so tou can use: "[ab]%".

 var result = _DbContext.batches
     .Where(tblHeader => tblHeader.isDeleted != true)
     .Where(x => EF.Functions.Like(x.Name, "[ab]%"))
     .ToListAsync();

Query with nested Any() translation error

3 Answers3

Linked