ASP.NET Core 6 MVC SQL数据库搜索异常：点击按钮显示全部数据

问题描述 #

在ASP.NET Core 6 MVC项目中实现SQL数据库搜索功能时，点击搜索按钮后总是显示数据库全部数据，而非匹配筛选条件的结果。当前页面包含城市文本框、州下拉列表和搜索按钮，需求是仅展示符合筛选条件的数据并在表格中呈现。

现有控制器代码 #

public IActionResult Index()
{
    List<string> states = _Db.Officers
                             .Select(o => o.State).Distinct().ToList();

    List<Officer> officers = _Db.Officers.ToList();
    ViewBag.States = states;

    return View(officers);
}

[HttpPost]
public IActionResult Index(string state)
{
    List<Officer> Officers;

    if (string.IsNullOrEmpty(state) || state == "")
    {
        Officers = _Db.Officers.ToList();
    }
    else
    {
        Officers = _Db.Officers.Where(o => o.State == state).ToList();
    }

    ViewBag.States = _Db.Officers.Select(o => o.State).Distinct().ToList();

    return View("Index", Officers);
}

现有视图代码 #

@model IEnumerable<Officer>
<td>
  <td align= "right">City:</td>
    <input type= "text" name="City"/>
<td>

<td align="right">State:</td>
<td>
  <select>
      <option Value=""> Select a State . . .</option>
      <option value="DE">Delaware</option>
      <option value="DC">District of Columbia</option>
      <option value="IN">Indiana</option>
      <option value="MD">Maryland</option>
      <option value="MI">Michigan</option>
      <option value="NJ">New Jersey</option>
      <option value="NY">New York</option>
      <option value="OH">Ohio</option>
      <option value="PA">Pennsylvania</option>
      <option value="VA">Virginia</option>
      <option value="ON">Ontario</option>
      <option value="QC">Quebec</option>
  </select>
</td>

<input type="button" Value="Search"/>

<table id="Results"class="table table-bordered table-striped" style="width:100%; background-color:lightgray; border-color:black;display:none" >
   <thead>
    <tr>
        <th style="color:blue">
        First Name
        </th>
        <th style="color:blue">
        Last Name
        </th>
        <th style="color:blue">
            Phone
        </th>
        <th style="color:blue">
            Cell Phone
        </th>
        <th style="color:blue">
        City
        </th>
        <th style="color:blue">
        State
        </th>
        <th style="color:blue">
        Zip Code
        </th>
        <th style="color:blue">
        Agency
        </th>
        <th style="color:blue">
        Address
        </th>
        <th style="color:blue">
        Date Modified
        </th>
  </thead>
  @foreach (var officer in @Model)
  {
      <tr>
    <td>@officer.FirstName</td>
    <td>@officer.LastName</td>
    <td>@officer.Phone</td>
    <td>@officer.Cell</td>
    <td>@officer.City</td>
    <td>@officer.State</td>
    <td>@officer.PostalCode</td>
    <td>@officer.AgencyName</td>
    <td>@officer.Address1</td>
    <td>@officer.DateUpdated</td>
     </tr>
  }
</table>

<script>
    function func()
    {
       document.getElementById('Results').style.display = 'block';
       document.getElementById("HideDiv").style.display = 'block';
                        
    }
</script>

核心问题点 #

• 表单未正确提交：搜索控件未包裹在<form>标签中，且按钮为type="button"，无法将筛选参数传递到后端。
• 下拉列表无name属性：州下拉列表未设置name="state"，后端无法接收选中的州参数。
• 未处理城市筛选：后端仅接收state参数，未处理文本框的City筛选条件。
• 表格显示逻辑未绑定：按钮未调用func()函数，点击后表格仍处于隐藏状态。

修复步骤 #

1. 修正视图代码，完善表单结构 #

将搜索控件包裹在表单中，添加必要属性并绑定显示逻辑：

@model IEnumerable<Officer>

<form asp-action="Index" method="post" onsubmit="func()">
    <table>
        <tr>
            <td align="right">City:</td>
            <td><input type="text" name="City" /></td>
            <td align="right">State:</td>
            <td>
                <select name="state">
                    <option value=""> Select a State . . .</option>
                    <option value="DE">Delaware</option>
                    <option value="DC">District of Columbia</option>
                    <option value="IN">Indiana</option>
                    <option value="MD">Maryland</option>
                    <option value="MI">Michigan</option>
                    <option value="NJ">New Jersey</option>
                    <option value="NY">New York</option>
                    <option value="OH">Ohio</option>
                    <option value="PA">Pennsylvania</option>
                    <option value="VA">Virginia</option>
                    <option value="ON">Ontario</option>
                    <option value="QC">Quebec</option>
                </select>
            </td>
            <td><input type="submit" Value="Search" /></td>
        </tr>
    </table>
</form>

<table id="Results" class="table table-bordered table-striped" style="width:100%; background-color:lightgray; border-color:black;display:none" >
   <thead>
    <tr>
        <th style="color:blue">First Name</th>
        <th style="color:blue">Last Name</th>
        <th style="color:blue">Phone</th>
        <th style="color:blue">Cell Phone</th>
        <th style="color:blue">City</th>
        <th style="color:blue">State</th>
        <th style="color:blue">Zip Code</th>
        <th style="color:blue">Agency</th>
        <th style="color:blue">Address</th>
        <th style="color:blue">Date Modified</th>
  </thead>
  @foreach (var officer in Model)
  {
      <tr>
        <td>@officer.FirstName</td>
        <td>@officer.LastName</td>
        <td>@officer.Phone</td>
        <td>@officer.Cell</td>
        <td>@officer.City</td>
        <td>@officer.State</td>
        <td>@officer.PostalCode</td>
        <td>@officer.AgencyName</td>
        <td>@officer.Address1</td>
        <td>@officer.DateUpdated</td>
     </tr>
  }
</table>

<script>
    function func()
    {
       document.getElementById('Results').style.display = 'block';
       const hideDiv = document.getElementById("HideDiv");
       if(hideDiv) hideDiv.style.display = 'block';
    }
</script>

2. 更新控制器代码，支持多条件筛选 #

修改POST方法，同时处理州和城市的筛选条件：

public IActionResult Index()
{
    List<string> states = _Db.Officers
                             .Select(o => o.State).Distinct().ToList();

    List<Officer> officers = _Db.Officers.ToList();
    ViewBag.States = states;

    return View(officers);
}

[HttpPost]
public IActionResult Index(string state, string city)
{
    IQueryable<Officer> query = _Db.Officers;

    if (!string.IsNullOrEmpty(state))
    {
        query = query.Where(o => o.State == state);
    }

    if (!string.IsNullOrEmpty(city))
    {
        // 如需精确匹配，将Contains改为==
        query = query.Where(o => o.City.Contains(city));
    }

    List<Officer> officers = query.ToList();
    ViewBag.States = _Db.Officers.Select(o => o.State).Distinct().ToList();

    return View(officers);
}

3. 可选优化：动态生成下拉列表 #

利用ViewBag.States动态生成州选项，避免硬编码：

<select name="state">
    <option value=""> Select a State . . .</option>
    @foreach(var state in ViewBag.States)
    {
        <option value="@state">@state</option>
    }
</select>

内容的提问来源于stack exchange，提问作者cg1323