ASP.NET MVC中如何实现基于用户选择的动态列数据查询

好的，咱们来一步步实现这个动态列选择的功能！首先得解决几个小问题，比如表单绑定、动态LINQ查询，还有结果展示，我会把每个步骤都讲清楚。

第一步：创建专门的视图模型（ViewModel） #

你原来直接用Employee实体接收表单提交不太合适，因为咱们需要的是哪些列被勾选的状态，而不是实体本身。所以先新建一个视图模型来存勾选状态：

public class EmployeeColumnSelectionViewModel
{
    public bool Name { get; set; }
    public bool Email { get; set; }
    public bool Address { get; set; }
    public bool Phone { get; set; }
}

第二步：修正视图代码 #

原来的视图有几个小问题，比如复选框的name属性绑定不够准确，还有Phone的标签写错了。咱们换成强类型的HTML辅助方法，这样绑定更可靠：

@model DynamicCheck.Models.EmployeeColumnSelectionViewModel
@{ ViewBag.Title = "Select Employee Columns"; }
<h2>Choose Columns for Employee Report</h2>
@using (Html.BeginForm("Report", "Employees", FormMethod.Post))
{
    <div class="row mt-3">
        <div class="col-lg-3">
            @Html.CheckBoxFor(m => m.Name)
            @Html.LabelFor(m => m.Name, "Employee Name")
        </div>
        <div class="col-lg-3">
            @Html.CheckBoxFor(m => m.Email)
            @Html.LabelFor(m => m.Email, "Employee Email")
        </div>
        <div class="col-lg-3">
            @Html.CheckBoxFor(m => m.Address)
            @Html.LabelFor(m => m.Address, "Employee Address")
        </div>
        <div class="col-lg-3">
            @Html.CheckBoxFor(m => m.Phone)
            @Html.LabelFor(m => m.Phone, "Employee Phone") <!-- 修正了原来的标签错误 -->
        </div>
        <div class="col-lg-12 mt-4">
            <input type="submit" value="Generate Report" class="btn btn-primary"/>
        </div>
    </div>
    @if (!string.IsNullOrEmpty(ViewBag.Message))
    {
        <div class="alert alert-warning mt-3">@ViewBag.Message</div>
    }
}

第三步：实现动态列查询（性能最优版） #

接下来是核心的控制器逻辑，咱们要根据用户勾选的列，生成只包含这些列的SQL查询（EF会自动优化，只查需要的字段）。这里用if-else拼接不同的投影表达式，虽然代码多一点，但性能最好：

public ActionResult Report()
{
    // 返回空的选择视图模型，初始化表单
    return View(new EmployeeColumnSelectionViewModel());
}

[HttpPost]
public ActionResult Report(EmployeeColumnSelectionViewModel model)
{
    // 先判断是否勾选了至少一列
    if (!model.Name && !model.Email && !model.Address && !model.Phone)
    {
        ViewBag.Message = "Please select at least one column to generate the report.";
        return View(model);
    }

    // 根据勾选情况构建动态查询
    IQueryable<dynamic> reportQuery = null;

    if (model.Name && model.Email && model.Address && model.Phone)
    {
        reportQuery = db.Employees.Select(x => new { x.Name, x.Email, x.Address, x.Phone });
    }
    else if (model.Name && model.Email && model.Address)
    {
        reportQuery = db.Employees.Select(x => new { x.Name, x.Email, x.Address });
    }
    else if (model.Name && model.Email && model.Phone)
    {
        reportQuery = db.Employees.Select(x => new { x.Name, x.Email, x.Phone });
    }
    else if (model.Name && model.Address && model.Phone)
    {
        reportQuery = db.Employees.Select(x => new { x.Name, x.Address, x.Phone });
    }
    else if (model.Email && model.Address && model.Phone)
    {
        reportQuery = db.Employees.Select(x => new { x.Email, x.Address, x.Phone });
    }
    else if (model.Name && model.Email)
    {
        reportQuery = db.Employees.Select(x => new { x.Name, x.Email });
    }
    else if (model.Name && model.Address)
    {
        reportQuery = db.Employees.Select(x => new { x.Name, x.Address });
    }
    else if (model.Name && model.Phone)
    {
        reportQuery = db.Employees.Select(x => new { x.Name, x.Phone });
    }
    else if (model.Email && model.Address)
    {
        reportQuery = db.Employees.Select(x => new { x.Email, x.Address });
    }
    else if (model.Email && model.Phone)
    {
        reportQuery = db.Employees.Select(x => new { x.Email, x.Phone });
    }
    else if (model.Address && model.Phone)
    {
        reportQuery = db.Employees.Select(x => new { x.Address, x.Phone });
    }
    else if (model.Name)
    {
        reportQuery = db.Employees.Select(x => new { x.Name });
    }
    else if (model.Email)
    {
        reportQuery = db.Employees.Select(x => new { x.Email });
    }
    else if (model.Address)
    {
        reportQuery = db.Employees.Select(x => new { x.Address });
    }
    else if (model.Phone)
    {
        reportQuery = db.Employees.Select(x => new { x.Phone });
    }

    // 执行查询，获取结果
    var reportData = reportQuery.ToList();
    // 把勾选状态传给结果视图（可选，比如用来高亮选中的列）
    ViewBag.SelectedColumns = model;
    // 返回结果视图
    return View("ReportResults", reportData);
}

第四步：创建结果展示视图 #

新建一个ReportResults.cshtml视图，用来动态渲染查询到的列（因为返回的是匿名类型，所以用dynamic接收）：

@model IEnumerable<dynamic>
@{ ViewBag.Title = "Employee Report Results"; }
<h2>Employee Report</h2>
@if (Model == null || !Model.Any())
{
    <p>No data available for the selected columns.</p>
}
else
{
    <table class="table table-striped table-bordered mt-3">
        <thead>
            <tr>
                @{
                    // 获取第一个对象的所有属性，作为表头
                    var firstRecord = Model.First();
                    var properties = firstRecord.GetType().GetProperties();
                    foreach (var prop in properties)
                    {
                        <th>@prop.Name</th>
                    }
                }
            </tr>
        </thead>
        <tbody>
            @foreach (var record in Model)
            {
                <tr>
                    @foreach (var prop in record.GetType().GetProperties())
                    {
                        <td>@prop.GetValue(record)</td>
                    }
                </tr>
            }
        </tbody>
    </table>
}
<p class="mt-3">@Html.ActionLink("Back to Column Selection", "Report")</p>

可选：简化版（用ExpandoObject，适合列很多的情况） #

如果你的Employee表有很多列，写一堆if-else太麻烦，可以用ExpandoObject动态构建对象。不过要注意：这种方式EF会查询所有列，然后在内存中过滤，性能不如上面的方法，适合列不多或者对性能要求不高的场景：

[HttpPost]
public ActionResult Report(EmployeeColumnSelectionViewModel model)
{
    if (!model.Name && !model.Email && !model.Address && !model.Phone)
    {
        ViewBag.Message = "Please select at least one column.";
        return View(model);
    }

    var reportData = db.Employees.Select(x =>
    {
        dynamic dynamicObj = new ExpandoObject();
        var propertyDict = (IDictionary<string, object>)dynamicObj;
        
        if (model.Name) propertyDict["Name"] = x.Name;
        if (model.Email) propertyDict["Email"] = x.Email;
        if (model.Address) propertyDict["Address"] = x.Address;
        if (model.Phone) propertyDict["Phone"] = x.Phone;
        
        return dynamicObj;
    }).ToList();

    ViewBag.SelectedColumns = model;
    return View("ReportResults", reportData);
}

这样就完成啦！用户勾选需要的列，提交后会生成只包含这些列的SQL查询，然后在结果页动态展示对应的表头和数据。

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