Linq to EF and site filter - how to build a query for optional parameters?

T

Timofey Yatsenko2014-04-30 11:13:01

LINQ

Timofey Yatsenko, 2014-04-30 11:13:01

I am writing a small application and trying to perform the most banal operation, to make a selection from the database, taking into account the filter that the user sets.
EF is used, queries are made using LINQ.
Search data comes from the user: country, list of resorts and regions. Each of these parameters is optional.
The problem is in building a LINQ query for optional parameters.
Class with input parameters:

public class FilterConditions
    {
        public int? country { get; set;}
        public int[] resorts { get; set; }
        public int[] regions { get; set; }
        public int[] hotels { get; set; }
    }

Controller action with the request itself

public IHttpActionResult GetHotels([FromUri] FilterConditions conditions)
{
      var hotels = (from hotel in db.Hotels
             join spo in db.SpoData on hotel.Key equals spo.HotelKey
             where (conditions.country == null || conditions.country == spo.CountryKey) &&
                        (conditions.regions == null ||  conditions.regions.Contains((int)spo.RegionKey)) &&
                        conditions.resorts == null || conditions.resorts.Contains((int)spo.ResortKey))
             select new HotelDto { Name = hotel.Name, Id = hotel.Key, CountryKey = hotel.CountryKey });
           
       return Ok(hotels);
}

The problem is that the LINQ query builder wraps everything that is written in the query into the database, regardless of whether it is needed or not. Those. check conditions.country == null and conditions.regions == null it also wraps in the database.
And if it still copes with the first case (conditions.country == null ), because this is an ordinary int, then in the case of conditions.resorts it already falls, since it is an array.
I understand that the task is trivial, but I can’t figure out how to solve it concisely and beautifully.

Reply

Answer the question

In order to leave comments, you need to log in

1 answer(s)

T

trerums, 2014-04-30
@thekip

Did something similar recently. I think you need to build a query based on the user's input, not just paste whatever comes into the query. Here is an example of how I did it:
A model that accepts user input:

public class PostFilterModel
    {
        public PostFilterModel()
        {
            Currency = Enumerable.Empty<Currency>();
            Condition = Enumerable.Empty<Condition>();
            Transmission = Enumerable.Empty<Transmission>();
            Rudder = Enumerable.Empty<Rudder>();
            Body = Enumerable.Empty<Body>();
            Engine = Enumerable.Empty<Engine>();
            Gear = Enumerable.Empty<Gear>();
        }

        public int City { get; set; }
        public int Region { get; set; }
        public int Brand { get; set; }
        public int Model { get; set; }
        public int MinHorsePower { get; set; }
        public int MaxHorsePower { get; set; }
        public int MinEngineCapacity { get; set; }
        public int MaxEngineCapacity { get; set; }
        public int MinMileage { get; set; }
        public int MaxMileage { get; set; }
        public int MinPrice { get; set; }
        public int MaxPrice { get; set; }
        public int MinYear { get; set; }
        public int MaxYear { get; set; }

        public IEnumerable<Currency> Currency { get; set; }
        public IEnumerable<Condition> Condition { get; set; }
        public IEnumerable<Transmission> Transmission { get; set; }
        public IEnumerable<Rudder> Rudder { get; set; }
        public IEnumerable<Body> Body { get; set; }
        public IEnumerable<Engine> Engine { get; set; }
        public IEnumerable<Gear> Gear { get; set; }
    }

And here is the query to the database:

public ICollection<Post> GetByFilter(PostFilterModel filter)
        {
            IQueryable<Post> Posts = uow.PostRepository.GetAll();

            //
            // Обработка входных параметров, которые содержат только одно значение
            //
            if (filter.City > 0)
                Posts = Posts.Where(p => p.City.CityId == filter.City);

            if (filter.Region > 0)
                Posts = Posts.Where(p => p.City.Region.RegionId == filter.Region);

            if (filter.Brand > 0)
                Posts = Posts.Where(p => p.Car.Brand.BrandId == filter.Brand);

            if (filter.Model > 0)
                Posts = Posts.Where(p => p.Car.Model.ModelId == filter.Model);

            if (filter.MinPrice > 0)
                Posts = Posts.Where(p => p.Price >= filter.MinPrice);

            if (filter.MaxPrice > 0)
                Posts = Posts.Where(p => p.Price <= filter.MaxPrice);

            if (filter.MinYear > 0)
                Posts = Posts.Where(p => p.Car.Year >= filter.MinYear);

            if (filter.MaxYear > 0)
                Posts = Posts.Where(p => p.Car.Year <= filter.MaxYear);

            //
            // Обработка входных параметров, которые могут содержать множественные значения
            //
            if (filter.Condition.Count() > 0)
                Posts = Posts.Where(p => filter.Condition.Contains(p.Car.Condition));

            if (filter.Transmission.Count() > 0)
                Posts = Posts.Where(p => filter.Transmission.Contains(p.Car.Transmission));

            if (filter.Rudder.Count() > 0)
                Posts = Posts.Where(p => filter.Rudder.Contains(p.Car.Rudder));

            if (filter.Body.Count() > 0)
                Posts = Posts.Where(p => filter.Body.Contains(p.Car.Body));

            if (filter.Engine.Count() > 0)
                Posts = Posts.Where(p => filter.Engine.Contains(p.Car.Engine));

            if (filter.Gear.Count() > 0)
                Posts = Posts.Where(p => filter.Gear.Contains(p.Car.Gear));

            // Выполнение запроса
            return Posts.ToList();
        }

Hope this helps you somehow.