Asp.net mvc Linq query for multiple filters

Question

I'm working on an Asp.net MVC Razor application. In my application view, I have a filter bar where the user may enter filtering options.

picture for reference

I'm wondering if there is a simpler linq expression for a scenario when a user enters multiple cities as an "or" function, and when there are multiple postal codes as a "and" function.

E.g: user enters "new york, montreal" i would have to retrieve all the entries with those cities.

I have working code, but it doesn't take into account if a user would enter multiple entries

code:

string city = collection["city"];                    // Default value = "City"
        string postal = collection["postCode"];              // Default value = "Postal Code"
        string skill = collection["skillSet"];               // Default value = ""
        string category = collection["taskCategory"];        // Default value = ""
        string viewall = collection["viewAll"];              // Default value = null, when clicked = "View all"
        string status = collection["status"];                // Defualt Value = ""

        if (city != "City")
        {
            ViewBag.city = city;
        }
        if (postal != "Postal Code")
        {
            ViewBag.postal = postal;
        }
        ViewBag.skill = skill;
        ViewBag.category = category;
        ViewBag.status = status;

        var viewModel = new W6ViewModel();

        // Default list for the Tasks nand Engineers
        viewModel.engineers = db.W6ENGINEERS.OrderBy(n => n.Name).ToList();
        viewModel.tasks = db.W6TASKS.ToList();

        var keys = db.W6ENGINEERS.Select(v => v.W6Key).ToList();
        var engSkills = db.W6ENGINEERS_SKILLS.Where(v => v.W6SKILLS.Name == skill).Select(k => k.W6Key).ToList();

        //Filters for the Tasks & Engineer
        if (viewall != "View all")
        {
            if (city != "City" && postal != "Postal Code" && category == "" && skill == "" && status == "")
            {
                viewModel.tasks =
                    db.W6TASKS.Where(w => w.City == city).Where(v => v.PostCode.Contains(postal)).ToList();
                viewModel.engineers =
                    db.W6ENGINEERS.Where(w => w.City == city).Where(v => v.PostCode.Contains(postal)).ToList();
            }
            else if (city != "City" && postal != "Postal Code" && category == "" && skill == "" && status != "")
            {
                viewModel.tasks =
                    db.W6TASKS.Where(w => w.City == city).Where(v => v.PostCode.Contains(postal)).Where(s => s.W6TASK_STATUSES.Name == status).ToList();
                viewModel.engineers =
                    db.W6ENGINEERS.Where(w => w.City == city).Where(v => v.PostCode.Contains(postal)).ToList();
            }
            else if (city != "City" && postal != "Postal Code" && category != "" && skill != "" && status == "")
            {
                viewModel.tasks =
                    db.W6TASKS.Where(w => w.City == city)
                        .Where(v => v.PostCode.Contains(postal))
                        .Where(c => c.W6TASKTYPECATEGORY.Name == category)
                        .ToList();
                viewModel.engineers = db.W6ENGINEERS.Where(c => c.City == city).Where(p => p.PostCode.Contains(postal)).Where(k => engSkills.Contains(k.W6Key)).ToList();
            }
            else if (city != "City" && postal != "Postal Code" && category != "" && skill != "" && status != "")
            {
                viewModel.tasks =
                    db.W6TASKS.Where(w => w.City == city)
                        .Where(v => v.PostCode.Contains(postal))
                        .Where(c => c.W6TASKTYPECATEGORY.Name == category)
                        .Where(s => s.W6TASK_STATUSES.Name == status)
                        .ToList();
                viewModel.engineers = db.W6ENGINEERS.Where(c => c.City == city).Where(p => p.PostCode.Contains(postal)).Where(k => engSkills.Contains(k.W6Key)).ToList();
            }
            else if (city == "City" && postal != "Postal Code" && category != "" && skill != "" && status == "")
            {
                viewModel.tasks =
                    db.W6TASKS.Where(v => v.PostCode.Contains(postal))
                        .Where(c => c.W6TASKTYPECATEGORY.Name == category)
                        .ToList();
                viewModel.engineers =
                    db.W6ENGINEERS.Where((p => p.PostCode.Contains(postal)))
                        .Where(k => engSkills.Contains(k.W6Key))
                        .ToList();
            }
            else if (city == "City" && postal != "Postal Code" && category != "" && skill != "" && status != "")
            {
                viewModel.tasks =
                    db.W6TASKS.Where(v => v.PostCode.Contains(postal))
                        .Where(c => c.W6TASKTYPECATEGORY.Name == category)
                        .Where(s => s.W6TASK_STATUSES.Name == status)
                        .ToList();
                viewModel.engineers =
                    db.W6ENGINEERS.Where((p => p.PostCode.Contains(postal)))
                        .Where(k => engSkills.Contains(k.W6Key))
                        .ToList();
            }
            else if (city != "City" && postal == "Postal Code" && category != "" && skill != "" && status == "")
            {
                viewModel.tasks =
                    db.W6TASKS.Where(w => w.City == city).Where(c => c.W6TASKTYPECATEGORY.Name == category).ToList();
                viewModel.engineers =
                    db.W6ENGINEERS.Where(c => c.City == city).Where(k => engSkills.Contains(k.W6Key)).ToList();
            }
            else if (city != "City" && postal == "Postal Code" && category != "" && skill != "" && status != "")
            {
                viewModel.tasks =
                    db.W6TASKS
                    .Where(w => w.City == city).Where(c => c.W6TASKTYPECATEGORY.Name == category)
                    .Where(s => s.W6TASK_STATUSES.Name == status)
                    .ToList();
                viewModel.engineers =
                    db.W6ENGINEERS.Where(c => c.City == city).Where(k => engSkills.Contains(k.W6Key)).ToList();
            }
            else if (city != "City" && postal == "Postal Code" && category == "" && skill == "" && status == "")
            {
                viewModel.tasks =
                    db.W6TASKS.Where(w => w.City == city).ToList();
                viewModel.engineers =
                    db.W6ENGINEERS.Where(w => w.City == city).ToList();
            }
            else if (city != "City" && postal == "Postal Code" && category == "" && skill == "" && status != "")
            {
                viewModel.tasks =
                    db.W6TASKS.Where(w => w.City == city).Where(s => s.W6TASK_STATUSES.Name == status).ToList();
                viewModel.engineers =
                    db.W6ENGINEERS.Where(w => w.City == city).ToList();
            }
            else if (city == "City" && postal != "Postal Code" && category == "" && skill == "" && status == "")
            {
                viewModel.tasks =
                    db.W6TASKS.Where(v => v.PostCode.Contains(postal)).ToList();
                viewModel.engineers =
                    db.W6ENGINEERS.Where(v => v.PostCode.Contains(postal)).ToList();
            }
            else if (city == "City" && postal != "Postal Code" && category == "" && skill == "" && status != "")
            {
                viewModel.tasks =
                    db.W6TASKS.Where(v => v.PostCode.Contains(postal)).Where(s => s.W6TASK_STATUSES.Name == status).ToList();
                viewModel.engineers =
                    db.W6ENGINEERS.Where(v => v.PostCode.Contains(postal)).ToList();
            }
            else if (city == "City" && postal == "Postal Code" && category != "" && skill != "" && status == "")
            {
                viewModel.tasks =
                    db.W6TASKS.Where(c => c.W6TASKTYPECATEGORY.Name == category).ToList();
                viewModel.engineers = db.W6ENGINEERS.Where(k => engSkills.Contains(k.W6Key)).ToList();
            }
            else if (city == "City" && postal == "Postal Code" && category != "" && skill != "" && status != "")
            {
                viewModel.tasks =
                    db.W6TASKS.Where(c => c.W6TASKTYPECATEGORY.Name == category).Where(s => s.W6TASK_STATUSES.Name == status).ToList();
                viewModel.engineers = db.W6ENGINEERS.Where(k => engSkills.Contains(k.W6Key)).ToList();
            }
            else if (city == "City" && postal == "Postal Code" && category == "" && skill != "" && status == "")
                viewModel.engineers = db.W6ENGINEERS.Where(k => engSkills.Contains(k.W6Key)).ToList();
            else if (city == "City" && postal == "Postal Code" && category == "" && skill != "" && status != "")
            {
                viewModel.engineers = db.W6ENGINEERS.Where(k => engSkills.Contains(k.W6Key)).ToList();
                viewModel.tasks = db.W6TASKS.Where(s => s.W6TASK_STATUSES.Name == status).ToList();
            }
            else if (city == "City" && postal == "Postal Code" && category != "" && skill == "" && status == "")
                viewModel.tasks = db.W6TASKS.Where(c => c.W6TASKTYPECATEGORY.Name == category).ToList();
            else if (city == "City" && postal == "Postal Code" && category != "" && skill == "" && status != "")
            {
                viewModel.tasks = db.W6TASKS
                    .Where(c => c.W6TASKTYPECATEGORY.Name == category)
                    .Where(s => s.W6TASK_STATUSES.Name == status)
                    .ToList();
            }
            else if(city == "City" && postal == "Postal Code" && category == "" && skill == "" && status != "")
            {
                viewModel.tasks = db.W6TASKS.Where(s => s.W6TASK_STATUSES.Name == status).ToList();
            }
        }

Thanks for your help.

Your question is unclear as to whether you want to handle commas or handle `or`. Regardless, I think you should look into [expression trees](http://stackoverflow.com/questions/10005948/what-are-expression-trees-and-how-do-you-use-them-and-why-would-you-use-them) — crthompson, Aug 21 '14 at 13:59
handle the "or" as well as the "and" thanks ill look into your link — Stephen Sugumar, Aug 21 '14 at 14:13

score 1 · Accepted Answer · answered Aug 21 '14 at 15:20

What you need is a PredicateBuilder, i know of one that handles or and And for such scenario, Fluentx library has that class and below is a sample of using it:

var predicate = PredicateBuilder.True<Customer>();

foreach (var product in products)
{
    predicate = predicate.And(c => c.Products.Any(x => x.Id == productId));
}

Just download the library and reference it and use the PredicateBuilder class.

Asp.net mvc Linq query for multiple filters

1 Answers1