Query with nested Any() translation error-CodePudding

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?

CodePudding user response：

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();

CodePudding user response：

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();

CodePudding user response：

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();