How to select all Names that contain a string from an array? (EF and Linq)

Question

.Where(i => strArray.Any(s => i.Name.Contains(s)))

I have an array of strings [ "text1", "text2", "text3"] and I want to find all Names that have a substring such as: text1somethingsomething

The error I get is

System.InvalidOperationException: The LINQ expression 's => EntityShaperExpression: 
    SalonML_API.Data.Models.DynamicContent
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.Name.Contains(s)' 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.
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitLambda[T](Expression`1 lambdaExpression)

My full code is

[HttpGet]
public async Task<IActionResult> GetContent([FromQuery] string[]? strArray)
{
    // ** todo get this to order by OrderIndex **
    List<DynamicContentDTO> dynContentList;

    if (strArray == null)
        dynContentList = await _context.DynamicContents
            .OrderBy(x => x.OrderIndex)
            .Select(d => new DynamicContentDTO(d))
            .ToListAsync();
    else
        dynContentList = await _context.DynamicContents
            .OrderBy(x => x.OrderIndex)
            .Where(i => strArray.Any(s => i.Name.Contains(s)))
            .Select(d => new DynamicContentDTO(d))
            .ToListAsync();

    return Ok(dynContentList);
}

Use `EF.Functions.Like` instead with corrected array (you need to create new with % at beginning and end) — Selvin, Dec 24 '22 at 16:26
Use [FilterByItems](https://stackoverflow.com/questions/67666649/lambda-linq-with-contains-criteria-for-multiple-keywords/67666993#67666993) extension and you can do the following: `var query = query.FilterByItems(strArray, (i, s) => i.Name.Contains(s), true);`. It will generate translatable LINQ query. — Svyatoslav Danyliv, Dec 24 '22 at 16:34

score 0 · Answer 1 · answered Dec 25 '22 at 12:31

Another alternative is by using LinqKit:

Expression<Func<DynamicContent, bool>> searchExpression = dynamicContent => strArray.Any(x => dynamicContent.Name.Contains(x));
        
dynContentList = _context.DynamicContents.AsExpandable()
    .OrderBy(x => x.OrderIndex)
    .Where(searchExpression.Compile())
    .Select(d => new DynamicContentDTO(d))
    .ToList();

LinqKit basically allows you to write nested expressions (like in your case) to help translate Linq->SQL correctly.

How to select all Names that contain a string from an array? (EF and Linq)

1 Answers1