Filtering with multiple filters MVC

Question

I am building a reports page, which has multiple filters, and when the user selects 0 to all, it shows all columns, and filters through all data. I'm not sure how to join filters when more than one is selected. Sample code is below, I have posted only the relevant code because it is very lengthy, and the controller is pulling from the model, which is where 'IssueViewModel' is coming from. I did find Filter/Search using Multiple Fields - ASP.NET MVC but it required me to change too much of my code because of variable issues for what I have already written. Thank you!

Controller:

public ActionResult Reports(string JIssue, string Status)
    {
        string EX = "EX";
        var Issues = db.Data.Select(i => new IssueViewModel { });
if (JIssue == "2"){
            Issues = db.Data.Where(d => d.Column.Contains(EX)).Select(i => new IssueViewModel
        {
            Name = i.Name,
            Description = i.Description,
            Key = i.Key,
            LastUpdate = i.LastUpdate,
            ID = i.ID,
            IssueType = i.IssueType,
            ProgrammingStatus = i.ProgrammingStatus
        });  }
if (Status == "2"){
            Issues = db.Data.Where(d => d.Column2.Contains(1)).Select(i => new IssueViewModel
        {
            Name = i.Name,
            Description = i.Description,
            Key = i.Key,
            LastUpdate = i.LastUpdate,
            ID = i.ID,
            IssueType = i.IssueType,
            ProgrammingStatus = i.ProgrammingStatus
        });  }
        ViewBag.Issues = Issues.ToList();
        return View();

View:

@Html.DropDownList("JIssue", new List<SelectListItem> {
      new SelectListItem { Text = "All", Value = "1", Selected = true},
      new SelectListItem { Text = "EX", Value = "2"},
      new SelectListItem { Text = "EXX", Value = "3"},
      new SelectListItem { Text = "EZX", Value = "4"},
      new SelectListItem { Text = "ABC", Value = "5"},
      new SelectListItem { Text = "BCD", Value = "6"},
      new SelectListItem { Text = "CDE", Value = "7"},   
 }, "Select Project")

Please note that the model-view-controller tag is for questions about the pattern. There is a specific tag for the ASP.NET-MVC implementation. — , Sep 12 '16 at 01:29
You need to do this as per the answer you linked to. i.e. `var Issues = db.Data; if (condition) { Issues = Issues.Where(....); } if (nextCondition) { Issues = Issues.Where(...); }` etc, and then finally project the resulting query to your view model. — , Sep 12 '16 at 02:16
How can I do this without running into a "Cannot implicitly convert type "System.Linq.IQueryable to Program.Linq.IQueryable. If I'm not mistaken, I believe that means I won't be able to use my IssueViewModel, and I have a lot of queries written in my IssueViewModel, that I would otherwise have to write many join queries for. How can I get around that issue? — Millie, Sep 12 '16 at 02:53
Of course you can use your view model. But you need to do the filtering first based on the data model. The end result of your multiple `if` statements will be `IQueryable`. Then you finally do a `var model = Issues.Select(i => new IssueViewModel { ... }); return View(model);` — , Sep 12 '16 at 02:56

score 0 · Answer 1 · answered Sep 12 '16 at 01:47

0

I see a couple of possible improvements.

1) The JIssue string is one of your inputs. In the example if it's 2 then you check for EX in your Where clause. If you search by the Text not by the Value send over the text. This way you can exclude the if statement. (I understand you have more if statements for all other values.) So your first check would boil down to

Issues = db.Data.Where(d => d.Column.Contains(JIssue)).Select(...)

Same can be applied to the Status.

2) After checking each of the conditions you get the data (by executing Select). Instead, request the data only after running all Where clauses. Or, include all the conditions in one Where block.

So this would boil down to the following:

public ActionResult Reports(string JIssue, string Status) 
{
    var Issues  = db.Data
        .Where(d => d.Column.Contains(JIssue) &&
                    d.Column2.Contains(1) &&
                    /* potential other conditions for other columns*/ )
        .Select(i => /* your mapping here */)
        .ToList();

    ViewBag.Issues = Issues;
    return View();
}

answered Sep 12 '16 at 01:47

PiotrWolkowski

8,408
6
48
68

This is an idea, except doing it this way would take a long time as JIssue alone has 14 columns, the other filters, as noted, have 3, 7, 4, 5, 5, 3 and 3. Is there not a simpler way then writing a query for every single one? – Millie Sep 12 '16 at 01:53
I think I don't fully understand the question then. So if you are sending a value for JIssue do you need to check it for each column, like so: `.Where(d => d.Column.Contains(JIssue) && d.Column2.Contains(JIssue) && d.Column3.Contains(JIssue)...` ? And then do you need to do that for the second parameter on all 14 columns? – PiotrWolkowski Sep 12 '16 at 02:18
Yes, JIssue has 14 individual columns that can be selected. Suppose I run the first query, and I select all, which will bring all columns of JIssue, but then suppose I select yes for a programming task on one drop down list, and then in progress under the Status drop down list. What it should show is all Issues that are In Progress and listed as a Programming Task. It would be difficult to write an individual query for every option that the user may or may not select, seeing as JIssue has 14 columns alone, and may or may not be selected. – Millie Sep 12 '16 at 02:26
It would be much simpler to write each query for each selection once, and have a way to tell the program that this string has ended, pull the selected data and show it, and start with the next string for filtering until it has gone through all strings each time search has been hit, but I'm not sure how to implement this logic, if that makes any sense. – Millie Sep 12 '16 at 02:32
Based on your example in the question: `db.Data.Where(d => d.Column.Contains(EX))` how can I tell whether this `EX` parameter should be applied only to one column or to many columns, and how can I know to which columns should it be applied? – PiotrWolkowski Sep 12 '16 at 02:38
In order to do this, I created my drop down list, which has every combination possible, and then I have specified which query should be pulled for each label in the drop down list, so JIssueKey could equal anywhere from 1 to 14, and each has a different query, and it goes on for all of the other Drop Downs. The thing to do is to join the queries from each list. Right now, if I just select EX and no other filter, it will pull all EX data, but if I select EX and In Progress, it won't work because it is only running through the if statements for all, not one per filter. – Millie Sep 12 '16 at 02:43

Filtering with multiple filters MVC

1 Answers1