Is it optimal way to filter records from table using Entity Framework?

Question

I have the SQL Products table migrated from MongoDb and I use ASP Core + MediatR. The filtering data comes from frontend Autocompletes where user can select multiple values (3 different ProductGroups + 2 different Suppliers for example). Wanted to ask is my implementation optimal, or did I miss something? Below the code for action:

    [HttpPut("[action]")]
    public async Task<IQueryable<ArticleDto>> Put([FromBody] ProductsQueryAll query)
    {
        var response = await _mediator.Send(query);
        return response;
    }

And below the code for MediatR model and Handler:

 public class ProductsQueryAll : IRequest<IQueryable<ArticleDto>>
    {
        public string[] ProductGroups { get; set; }
        public int[] SupplierIds { get; set; }
        public string[] Categories { get; set; }
    }

    public class ProductsQueryAllHandler : IRequestHandler<ProductsQueryAll, IQueryable<ArticleDto>>
    {
        private readonly DbContext _dbContext;
        private readonly IMapper _mapper;

        public ProductsQueryAllHandler(DbContext dbContext, IMapper mapper)
        {
            _dbContext = dbContext;
            _mapper = mapper ?? throw new ArgumentNullException(nameof(mapper));
        }

        public Task<IQueryable<ArticleDto>> Handle(ProductsQueryAll request, CancellationToken cancellationToken)
        {
            var predicate = PredicateBuilder.True<ArticleDm>();
            if (request.ProductGroups?.Length > 0)
            {
                predicate = predicate.And(x => request.ProductGroups.Any(y => y == x.ProductGroup));
            }
            if (request.SupplierIds?.Length > 0)
            {
                predicate = predicate.And(x => request.SupplierIds.Any(y => y == x.SupplierId));
            }
            if (request.Categories?.Length > 0)
            {
                predicate = predicate.And(x => request.Categories.Any( y => y == x.Category));
            }


            var entityList = _dbContext.Articles
                .AsNoTracking()
                .Where(predicate);

            return Task.FromResult(_mapper.ProjectTo<ArticleDto>(entityList));
        }
    }

Answer 1

Small syntax improvements

Foo?.Length > 0 can always be refactored to Foo?.Any()

x => request.SupplierIds.Any(y => y == x.SupplierId) can be somewhat improved by using
x => request.SupplierIds.Contains(x.SupplierId)

I don't particularly like the name ProductsQueryAll. ProductsQuery seems more appropriate. "All" implies that you're going to get all of them, which is not the case since you're allowing filters.
That being said, there may be circumstances that I'm not aware of that help explain why your name could make sense, e.g. to disambiguate it from something very similar.

I'm aware that this is not something everyone agrees on, but I find the _ prefix for private fields superfluous, and simply use camelcasing (context, mapper). That being said, you might be subjected to an existing coding style, at which point I do agree that conformity to the rest of the existing codebase and development team style is more important.

Query logic

Because of the nature of your filtering logic, you could've omitted the predicate builder and instead chained your Where calls directly. I tend to structure this kind of logic like so:

var query = _dbContext.Articles.AsQueryable();

if (request.ProductGroups?.Any())
    query = query.Where(x => request.ProductGroups.Contains(x.ProductGroup));

if (request.SupplierIds?.Any())
    query = query.Where(x => request.SupplierIds.Contains(x.SupplierId));

if (request.Categories?.Any())
    query = query.Where(x => request.Categories.Contains(x.Category));

Returning an IQueryable

This is something you should avoid at all costs. Do not return IQueryable. Your return type should be IEnumerable.

Also note that it is easy to still return an IQueryable even when your return type is IEnumerable (since IQueryable : IEnumerable), but you should actively avoid this. This is done by enumerating your IQueryable, most commonly done via ToList or ToListAsync.

Building this off of my previous suggestion:

public async Task<IEnumerable<ArticleDto>> Handle(ProductsQueryAll request, CancellationToken cancellationToken)
{
    // see previous snippet

    return await query
                   .ProjectTo<ArticleDto>(mapper.ConfigurationProvider)
                   .ToListAsync();
}

Small things to point out:

• Since you're working asynchronously, you should make this method actually asynchronous so you can benefit the most from it.
• ProjectTo has two signatures. You used mapper.ProjectTo(query) (oversimplified), but I prefer query.ProjectTo(mapper) (oversimplified) because it's more consistent with the rest of the syntax being used here, because it's always of the form query.Method(...).