Find in Entity Framework multiple OR parameters-CodePudding

Suppose I have a product in my database with the description “white shirt size 50”.

The search parameter would be “shirt 50”. I have a more complex query in which I add several “OR”s and I can't get them to work.

I get the following error:

The LINQ expression

'DbSet() .Where(p => p.IdTienda == __request_IdTienda_0) .Join( inner: DbSet(), outerKeySelector: p => p.IdArticulo, innerKeySelector: a => a.Id, resultSelector: (p, a) => new TransparentIdentifier<Publicacion, Articulo>( Outer = p, Inner = a )) .Where(ti => __arrayrequest_1 .Any(s => ti.Outer.Descripcion.Contains(s)) || ti.Outer.Codigo == __request_Filtro_SearchText_2 || ti.Inner.Codigo == __request_Filtro_SearchText_2 || ti.Inner.CodigoUniversal == __request_Filtro_SearchText_2 || ti.Inner.CodigoUniversalBulto == __request_Filtro_SearchText_2)'

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.

My code so far is the following:

var arrayrequest = request.Filtro.SearchText.Split().ToList();

var query = from publicacion in _dbContext.Publicaciones.Where(p => p.IdTienda == request.IdTienda)
            join articulo in _dbContext.Articulos 
                 on publicacion.IdArticulo equals articulo.Id
            where
                arrayrequest.Any(s => publicacion.Descripcion.Contains(s))
                                 || publicacion.Codigo == request.Filtro.SearchText
                                 || articulo.Codigo == request.Filtro.SearchText
                                 || articulo.CodigoUniversal == request.Filtro.SearchText
                                 || articulo.CodigoUniversalBulto == request.Filtro.SearchText
            select publicacion;

var publicaciones = await query
                          .Include(p => p.Articulo)
                          .Include(p => p.TributoPublicacion)
                          .ToArrayAsync();

The error occurs in the section

arrayrequest.Any(s => publicacion.Descripcion.Contains(s))`

I use Entity Framework Core 5 - any help is welcome

CodePudding user response：

Don't want to repeat myself, but it is good to show how it can be solved.

EF do not supports complex predicates with local collections and here you need to build expression tree dynamically. This answer has GetItemsPredicate function which helps in building needed condition.

Then you can rewrite your query in this way:

var arrayrequest = request.Filtro.SearchText.Split().ToList();

var query = from publicacion in _dbContext.Publicaciones.Where(p => p.IdTienda == request.IdTienda)
            join articulo in _dbContext.Articulos 
                 on publicacion.IdArticulo equals articulo.Id
            select publicacion;
            
var descriptionPredicate = query.GetItemsPredicate(arrayrequest, (publicacion, s) => publicacion.Descripcion.Contains(s));

Expression<Func<Publicacion, bool>> otherPredicate = publicacion => publicacion.Codigo == request.Filtro.SearchText
                                 || articulo.Codigo == request.Filtro.SearchText
                                 || articulo.CodigoUniversal == request.Filtro.SearchText
                                 || articulo.CodigoUniversalBulto == request.Filtro.SearchText;

query = query.Where(descriptionPredicate.CombineOr(otherPredicate)));

var publicaciones = await query
    .Include(p => p.Articulo)
    .Include(p => p.TributoPublicacion)
    .ToArrayAsync();