Filter values from DB by 4 parameters

Question

I have Experience entity Here is it:

public class Experience:BaseEntity
{
    public virtual string Name { get; set; }
    public virtual string Description { get; set; }
    public virtual DateTime? CreationDate { get; set; }
    public virtual string City { get; set; }
    public virtual int Price { get; set; }
    public Currency Currency { get; set; }
    public virtual int Rating { get; set; }
    public virtual double? Lat { get; set; }
    public virtual double? Lng { get; set; }
    public virtual DateTime? CompleteDate { get; set; }
    public virtual string CoverPic { get; set; }
    public virtual string Images { get; set; }
    public virtual string BroadcastingType { get; set; }
    public virtual bool IsActive { get; set; }
    public virtual bool IsFinished { get; set; }
    public virtual bool IsConfirmed { get; set; }
    public virtual int CountryId { get; set; }
    [ForeignKey("CountryId")]
    public virtual Country Country { get; set; }
    public virtual string UserId { get; set; }
    [ForeignKey("UserId")]
    public virtual AppUser IdentityUser { get; set; }


    public virtual ICollection<Proposals.Proposals> Proposals { get; set; }
    public virtual ICollection<Comments.Comments> Comments { get; set; }
}

I need to filter it by max and min price, rating and countryId.

So I wrote this method

public async Task<List<ExperienceListDto>> GetFilteredExperiences(FilterExperienceDto filterExperience)
    {
        var experiences = await _context.Experiences.Where(x =>
                x.Price >= filterExperience.MinPrice && x.Price <= filterExperience.MaxPrice &&
                filterExperience.MinPrice.HasValue && filterExperience.MaxPrice.HasValue ||
                x.Rating == filterExperience.Rating && filterExperience.Rating.HasValue ||
                x.CountryId == filterExperience.CountryId && filterExperience.CountryId.HasValue)
            .ProjectTo<ExperienceListDto>().ToListAsync();


        return experiences;
    }

But it filters only by min, max price. I want to filter it by all parameters. How I can do this?

I think your issue is that you're not using parenthesis. Group your conditions in parenthesis the way you want them logically. I think it doesn't branch like you expect it to. — Joelius, Nov 21 '19 at 08:16

score 1 · Accepted Answer · edited Nov 21 '19 at 08:11

You need to write your code in this way to get the desired result:

IQueryable<Experience> query = _context.Experiences;

if (filterExperience.MinPrice.HasValue && filterExperience.MaxPrice.HasValue)
    query = query.Where( x => x.Price >= filterExperience.MinPrice && x.Price <= filterExperience.MaxPrice);

if (filterExperience.Rating.HasValue)
    query = query.Where(x => x.Rating == filterExperience.Rating);

if (filterExperience.CountryId.HasValue)
    query = query.Where( x => x.CountryId == filterExperience.CountryId);

var experienceList = query.ToList();

Reference: https://stackoverflow.com/a/5541505/1273882

score 0 · Answer 2 · answered Nov 21 '19 at 07:55

You need to filter only has value in request body.

var query  = await _context.Experiences.AsQueryable();

                    if(filterExperience.MinPrice.HasValue)
                        query = query.Where(x.Price >= filterExperience.MinPrice)

                    if (filterExperience.MaxPrice.HasValue)
                        query = query.Where(x.Price >= filterExperience.MaxPrice);

                    if(filterExperience.Rating.HasValue)
                        query = query.Where(x.Rating == filterExperience.Rating);

                    .
                    ..
                    ....



                    var expriences = query.ProjectTo<ExperienceListDto>().ToListAsync();

score 0 · Answer 3 · answered Nov 21 '19 at 08:22

I noticed that the ||/OR statements could be messing your desired results because there might be different execution sequence of the conditional operators.

From your current code, you could do consecutive .Where() to filter them step by step

public async Task<List<ExperienceListDto>> GetFilteredExperiences(FilterExperienceDto filterExperience)
{
   var experiences = await _context.Experiences
.Where(x => x.Price >= filterExperience.MinPrice && x.Price <= filterExperience.MaxPrice && filterExperience.MinPrice.HasValue && filterExperience.MaxPrice.HasValue)
.Where(x.Rating == filterExperience.Rating && filterExperience.Rating.HasValue)
.Where(x.CountryId == filterExperience.CountryId && filterExperience.CountryId.HasValue)
            .ProjectTo<ExperienceListDto>().ToListAsync();


   return experiences;
}

Filter values from DB by 4 parameters

3 Answers3