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?

Answer 1

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;
}

Answer 2

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

Answer 3

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();