使用实体框架的动态查询

Question

我有一个名为Customer的实体。客户有name、address、gender、birthdate、city和number of kids。

我想让它成为可能，用户可以非常动态地过滤这些字段。例如，他可以添加一个文本字段来过滤名字，然后添加另一个文本字段来过滤另一个名字，然后再添加两个文本字段来过滤两个日期之间的出生日期等等…用户还可以选择在两个日期之间过滤或等于一个日期。所以事先不知道用户想要应用多少过滤器。

我如何构建这种查询，最好使用LINQ?

Answer 1

搜索动态Linq查询生成器Net MVC 4。

Answer 2

这将类似于您编写SQL查询的方式，检查是否给出了参数，然后将其作为过滤器应用。

    public class Customer
    {
        public string Name;
        public string Address;
        public string City;
        public string Gender;
        public DateTime Birthdate;
        public int NumberOfKids;
    }
    public IEnumerable<Customer> CustomerSearch(
        string partialName = null,
        string partialAddress = null,
        string partialCity = null,
        string gender = null,
        DateTime? exactBirthdate = null,
        DateTime? startDate = null,
        DateTime? endDate = null,
        int? minNumberOfKids = null)
    {
        // Sample data
        var customers = new [] {
            new Customer { Name = "Jack", Birthdate = DateTime.Today.AddYears(-30), NumberOfKids = 1 },
            new Customer { Name = "Jill", Birthdate = DateTime.Today.AddYears(-33).AddMonths(3), NumberOfKids = 2 },
            new Customer { Name = "Bob", Birthdate = DateTime.Today.AddYears(-35), NumberOfKids = 3 }
        };
        var query =
            from c in customers
            where (String.IsNullOrWhiteSpace(partialName) || c.Name.Contains(partialName))
               && (String.IsNullOrWhiteSpace(partialAddress) || c.Address.Contains(partialAddress))
               && (String.IsNullOrWhiteSpace(partialCity) || c.City.Contains(partialCity))
               && (String.IsNullOrWhiteSpace(gender) || c.Gender == gender)
               && (!exactBirthdate.HasValue || c.Birthdate.Date == exactBirthdate.Value.Date)
               && (!startDate.HasValue || !endDate.HasValue || c.Birthdate.Date >= startDate.Value.Date && c.Birthdate.Date <= endDate.Value.Date)
               && (!minNumberOfKids.HasValue || c.NumberOfKids >= minNumberOfKids.Value)
            select c;
        return query;
    }

然后这样命名:

        test.CustomerSearch("J", minNumberOfKids: 2).ToList().ForEach(c => Console.WriteLine("J and 2 kids " + c.Name));
        test.CustomerSearch(exactBirthdate: DateTime.Today.AddYears(-35)).ToList().ForEach(c => Console.WriteLine("exact birthdate " + c.Name));
        test.CustomerSearch(startDate: DateTime.Today.AddYears(-36), endDate: DateTime.Today.AddYears(-31)).ToList().ForEach(c => Console.WriteLine("birthdate between " + c.Name));