Filtering data with Expression Func - C# - How to apply filter on referent table?-CodePudding

I'm filtering my data with Expressions in c#.

In this particular case I used this piece of code to filter articles / products from my database.

private Expression<Func<Article, bool>> GetFilter(ProductsSearchDto productSearchDto)
{
    string stringSearch = !string.IsNullOrEmpty(productSearchDto.stringSearch)
           ? productSearchDto.stringSearch.ToLower()
           : string.Empty;


    return f => (!productSearchDto.BrandId.HasValue || f.BrandId == productSearchDto.BrandId) &&
                ((f.Title.ToLower().Contains(stringSearch)) ||
                (f.ShortDescription.ToLower().Contains(stringSearch)));
}

But now I would like to filter products by category also. Categories related to products are part of another table since one product might be part of different categories.

namespace Entities
{
    public class ArticleCategory
    {
        public long ArticleId { get; set; }
        public Article Article { get; set; }
        public long CategoryId { get; set; }
        public Category Category { get; set; }
    }
}

Article.cs class has collection of categories:

namespace Entities
{
    public class Article
    {
        public long? BrandId { get; set; }
        public Brand Brand { get; set; }
        public string Title { get; set; }
        public string ShortDescription { get; set; }
        public ICollection<ArticleCategory> Categories { get; set; }

    }
}

ProductsSearchDto productSearchDto contains categoryId which is used to fetch all products that are part of this specified category.

So I've tried something like this:

private Expression<Func<Article, bool>> GetFilter(ProductsSearchDto productSearchDto)
{
    string stringSearch = !string.IsNullOrEmpty(productSearchDto.stringSearch)
           ? productSearchDto.stringSearch.ToLower()
           : string.Empty;


    return f => (!searchQuery.CategoryId.HasValue || f.Categories.Select((x => x.CategoryId).ToList()).Contains((long)searchQuery.CategoryId) &&
                (!productSearchDto.BrandId.HasValue || f.BrandId == productSearchDto.BrandId) &&
                ((f.Title.ToLower().Contains(stringSearch)) ||
                (f.ShortDescription.ToLower().Contains(stringSearch)));
}

And this is how it's used in the application:

var articles = await _dbContext.Articles
                  .Where(GetFilter(searchObject))
                  .Select(x => new ArticleSimpleGetByCategoryDto
                  {
                      Id = x.Id,
                      Title = x.Title,
                      ShortDescription = x.ShortDescription,
                      Price = x.MPC,
                      Brand = x.Brand != null ? x.Brand.Title : "N/A",
                  })
                  .ToListAsync(cancellationToken);

P.S Error image:

Any kind of help would be awesome!! Thanks everyone,

Have a nice day/week/month/year :)

CodePudding user response：

Try the following expression. ToList() is bad idea for search query.

return f => (!searchQuery.CategoryId.HasValue || f.Categories.Any(x => x.CategoryId == searchQuery.CategoryId)
    (!productSearchDto.BrandId.HasValue || f.BrandId == productSearchDto.BrandId) &&
    ((f.Title.ToLower().Contains(stringSearch)) ||
    (f.ShortDescription.ToLower().Contains(stringSearch)));

CodePudding user response：

Instead of generating an Expression<Func<Article,Bool>>, just pass your IQueryable<Article> into the method and compose the expressions there. eg

    private IQueryable<Article> AddFilters(IQueryable<Article> query, ProductsSearchDto productSearchDto)
    {
        if (ProductsSearchDto.BrandId.HasValue)
            query = query.Where(f => f.BrandId == productSearchDto.BrandId;

        //...

        return query;
     }