ASP MVC best method to search/return all values from database

Question

In the case of a default value being used in an ASP MVC drown down list, I need the code to return all values or simply ignore that particular search criteria.

Below is the code I have in my View and Controller. Since the '%' does not seem to be working, is there another keyword/operator that will do the job?

View:

        <form method="post">
          <select name="Table" title="Table" style="font-size:8pt;">
            <option value="%">--Table Name--</option>
            <option value="AgentContEd">CE</option>
            <option value="AgentProductTraining">PT</option>
          </select>
          <select name="IssueType" style="font-size:8pt;">
            <option value="%">--Issue Type--</option>
            <option value="W">Warning</option>
            <option value="E">Error</option>
          </select>
          <select name="Status" style="font-size:8pt;">
            <option value="%">--Status Type--</option>
            <option value="O">Open</option>
            <option value="U">Under Review</option>
          </select>


        <input type="image" src="@Url.Content("~/Content/Images/Filter.bmp")" alt="Filter" style="padding-top: 0px;" />
        <a href="#" style="padding-left: 30px;"></a>
        </form>

Controller:

    public ViewResult Index(FormCollection dropDownSelection)
    {
        //security
        //if (!Security.IsViewer(User)) return RedirectToAction("Message", "Home", new { id = 2 });

        //if (ViewBag.Level == 0) return RedirectToAction("Message", "Home", new { id = 2 });

        string table = dropDownSelection["Table"];
        string issue = dropDownSelection["IssueType"];
        string status = dropDownSelection["Status"];

        var followUpItem = from follow in db.FollowUpItems
                           where follow.TableName.Equals(table) &&
                                 follow.IssueType.Equals(issue) &&
                                 follow.Status.Equals(status)
                           select follow;

        return View(followUpItem.ToList());
    }

*cough* [`Sqlmethods.Like`](http://msdn.microsoft.com/en-us/library/system.data.linq.sqlclient.sqlmethods.like.aspx) *cough* — Brad Christie, Jan 22 '13 at 21:20
We are using .Net 4.0. Is SqlMethods.Like only available in 4.5? — NealR, Jan 22 '13 at 21:26
supported in [3.5](http://msdn.microsoft.com/en-us/library/system.data.linq.sqlclient.sqlmethods.like%28v=vs.90%29.aspx) and up. — Brad Christie, Jan 22 '13 at 21:28
Ugh... this threw a "NotSupportedException" even though we are connecting to a SQL database. — NealR, Jan 22 '13 at 21:39

score 2 · Answer 1 · answered Jan 22 '13 at 21:24

2

You can use either SqlMethods.Like or simply the String.Contains method. (Keep in mind that String.Contains will be problematic if you retain the use of % or any other SQL wildcards.)

So, the three variations would look like:

var followUpItem = from follow in db.FollowUpItems
                   where SqlMethods.Like(follow.TableName, table) &&
                         follow.IssueType.Contains(issue) &&
                         follow.Status.Equals(status)
                   select follow;

answered Jan 22 '13 at 21:24

Brad Christie

100,477
16
156
200

We are using .Net 4.0. Is SqlMethods.Like only available in 4.5? – NealR Jan 22 '13 at 21:26
@NealR: Yes, just look at the "Other versions" drop down on that linked MSDN page. – Brad Christie Jan 22 '13 at 21:29
Ugh... this threw a "NotSupportedException" even though we are connecting to a SQL database. – NealR Jan 22 '13 at 21:37
You only need to comment on this answer if it's relevant to what I've posted (and not the question). That being said, are you using LINQ-to-SQL or just the LINQ library? – Brad Christie Jan 22 '13 at 21:41
If you're using LINQ-to-Entities, have a look at [this answer](http://stackoverflow.com/a/2585564/298053). – Brad Christie Jan 22 '13 at 21:44
We are using LINQ only the LINQ Library i believe. – NealR Jan 22 '13 at 22:08

JayC · Accepted Answer · 2013-01-22T21:33:15.387

I haven't compiled this, but I'm guessing you want something like:

 public ViewResult Index(FormCollection dropDownSelection)
    {
        //security
        //if (!Security.IsViewer(User)) return RedirectToAction("Message", "Home", new { id = 2 });

        //if (ViewBag.Level == 0) return RedirectToAction("Message", "Home", new { id = 2 });

        string table = dropDownSelection["Table"];
        string issue = dropDownSelection["IssueType"];
        string status = dropDownSelection["Status"];

        var followUpItem = from follow in db.FollowUpItems
                   where (follow.TableName.Equals(table) || table.Equals("%")) &&
                         (follow.IssueType.Equals(issue) || issue.Equals("%")) &&
                         (follow.Status.Equals(status) || status.Equals("%"))
                   select follow;

        return View(followUpItem.ToList());
    }

This is basically what we're trying to do. However, we have the default value of the drop down list set to '%' so we don't have to worry about it in the controller if no value is selected by the user. — NealR, Jan 22 '13 at 21:28

ASP MVC best method to search/return all values from database

2 Answers2