如何用LINQ+Razor实现数据库列唯一值显示及文本框填充？

• 实现类似SELECT DISTINCT的效果，避免显示重复的员工名称（如图中重复的Rob，仅需显示一条）
• 点击选中的员工名称后，将对应员工的部门名称和照片文件名填充到下方文本框中

相关代码 #

index.cshtml #

@page
@model IndexModel
@{ ViewData["Title"] = "Index"; }

<form method="post">
    <table>
        <tr>
            <td>
                @Html.TextBox("TxtDepartment")
            </td>
            <td>
                <button type="button" id="DepartmentSearch">Search</button>
            </td>
        </tr>
    </table>
</form>
<br />

<table>
    <tr>
        <td><div id="DepartmentResult"></div></td>&nbsp;
        <td><div id="EmployeeResult"></div></td>
    </tr>
</table>

<form method="post">
    <label>Department Name:</label>
    <input type="text" id="DeptName" />
    <label>Photo File Name:</label>
    <input type="text" id="NameResult" />
</form>

@section Scripts {
    <script>
        $("#DepartmentSearch").click(function()
        {
            $.ajax(
            {
                url: "/Index?handler=DisplayDepartment",
                type: "POST",
                data: { value: $("#TxtDepartment").val() },
                headers: { RequestVerificationToken: $('input:hidden[name="__RequestVerificationToken"]').val() },
                success: function(data) { $("#DepartmentResult").html(data); }
            });
        });
    </script>
}

Index.cs #

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using PracticeApp.Models;
using System.Linq;

namespace PracticeApp.Pages
{
    public class IndexModel : PageModel
    {
        public CompanyContext _context;

        public IndexModel(CompanyContext context) { _context = context; }

        public PartialViewResult OnGetDisplayDepartment(int value)
        {
            return Partial("_DisplayDepartmentPartial", _context.Departments.Where(x => x.DepartmentId == value).ToList());
        }

        public PartialViewResult OnGetDisplayEmployee(string value)
        {
            return Partial("_DisplayEmployeePartial", _context.Employees.Where(x => x.DepartmentName == value).ToList());
        }

        public PartialViewResult OnGetDisplayInfo(string value)
        {
            return Partial("_DisplayEmployeePartial", _context.Employees.Where(x => x.EmployeeName == value).ToString());
        }
    }
}

_DisplayDepartmentPartial.cshtml #

@model IEnumerable<Models.Department>

@if (Model.Count() != 0)
 {
    <table style="border: 1px solid black">
        <thead>
            <tr>
                <th colspan="2" style="border: 1px solid black; text-align: center;">Department Results</th>
            </tr>
        </thead>

        <tbody>
            <tr>
                <td align="center" style="border: 1px solid black; font-weight: bold;">
                    @Html.DisplayNameFor(m => m.DepartmentName)
                </td>
            </tr>

            @foreach (Models.Department item in Model)
             {
                <tr>
                    <td align="center" style="border: 1px solid black;">
                        <a id="EmployeeSearch" href="javascript:aa()">@item.DepartmentName</a>
                    </td>
                </tr>
             }
        </tbody>
    </table>
 }
else
{
    <p>No data</p>
}

<script>
    $("#EmployeeSearch").click(function()
    {
        $.ajax(
        {
            url: "Index?handler=DisplayEmployee",
            type: "POST",
            data: { value: $("#EmployeeSearch").text() },
            headers: { RequestVerificationToken: $('input:hidden[name="__RequestVerificationToken"]').val() },
            success: function(data) { $("#EmployeeResult").html(data); }
        });
    });
</script>

_DisplayEmployeePartial.cshtml #

@model IEnumerable<Models.Employee>

@if (Model.Count() != 0)
 {
    <table style="border: 1px solid black">
        <thead>
            <tr>
                <th colspan="2" style="border: 1px solid black; text-align: center;">Employee Results</th>
            </tr>
        </thead>

        <tbody>
            <tr>
                <td align="center" style="border: 1px solid black; font-weight: bold;">
                    @Html.DisplayNameFor(m => m.EmployeeName)
                </td>
            </tr>

            @foreach (Models.Employee item in Model)
             {
                <tr>
                    <td align="center" style="border: 1px solid black;">
                        <a id="PopulateNameData" href="javascript:aa()">@item.EmployeeName</a>
                    </td>
                </tr>
             }
        </tbody>
    </table>
 }
else
{
    <p>No data</p>
}

<script>
    $("#PopulateNameData").click(function()
    {
        $.ajax(
        {
            url: "Index?handler=DisplayEmployee",
            type: "POST",
            data: { value: $("#PopulateNameData").text() },
            headers: { RequestVerificationToken: $('input:hidden[name="__RequestVerificationToken"]').val() },
            success: function(data) { $("#NameResult").html(data); }
        });
    });
</script>

Employee Model #

using System;
using System.ComponentModel.DataAnnotations;

namespace PracticeApp.Models
{
    public partial class Employee
    {
        [Display(Name = "Employee ID")] public int EmployeeId { get; set; }
        [Display(Name = "Department ID")] public int DepartmentId { get; set; }
        [Display(Name = "Name")] public string EmployeeName { get; set; } = null!;
        public string DepartmentName { get; set; } = null!;
        public DateTime DateofJoining { get; set; }
        public string PhotoFileName { get; set; }
    }
}

问题截图 #

解决方案 #

1. 去除重复员工名称 #

后台代码修改

在Index.cs的OnGetDisplayEmployee方法中，通过DistinctBy（.NET 6+）或GroupBy实现去重：

// .NET 6及以上版本用DistinctBy
public PartialViewResult OnGetDisplayEmployee(string value)
{
    var uniqueEmployees = _context.Employees
        .Where(x => x.DepartmentName == value)
        .DistinctBy(e => e.EmployeeName)
        .ToList();
    return Partial("_DisplayEmployeePartial", uniqueEmployees);
}

// .NET 5及以下版本用GroupBy
public PartialViewResult OnGetDisplayEmployee(string value)
{
    var uniqueEmployees = _context.Employees
        .Where(x => x.DepartmentName == value)
        .GroupBy(e => e.EmployeeName)
        .Select(g => g.First())
        .ToList();
    return Partial("_DisplayEmployeePartial", uniqueEmployees);
}

前端视图修改

_DisplayEmployeePartial.cshtml中循环生成的<a>标签不能重复使用id，改为class避免JS绑定失效：

<a class="PopulateNameData" href="javascript:void(0)">@item.EmployeeName</a>

2. 点击名称填充文本框 #

后台新增方法

在Index.cs中添加获取员工详细信息的接口：

public JsonResult OnGetGetEmployeeInfo(string employeeName)
{
    var employee = _context.Employees
        .FirstOrDefault(e => e.EmployeeName == employeeName);
    return new JsonResult(employee);
}

前端JS修改

修改_DisplayEmployeePartial.cshtml中的点击事件，请求员工信息并填充文本框：

$(".PopulateNameData").click(function()
{
    var employeeName = $(this).text();
    $.ajax({
        url: "/Index?handler=GetEmployeeInfo",
        type: "GET",
        data: { employeeName: employeeName },
        headers: { RequestVerificationToken: $('input:hidden[name="__RequestVerificationToken"]').val() },
        success: function(data) {
            if(data) {
                $("#DeptName").val(data.DepartmentName);
                $("#NameResult").val(data.PhotoFileName);
            }
        }
    });
});

额外修复

_DisplayDepartmentPartial.cshtml中的<a>标签同样存在重复id问题，改为class并修改JS：

<a class="EmployeeSearch" href="javascript:void(0)">@item.DepartmentName</a>

$(".EmployeeSearch").click(function()
{
    var deptName = $(this).text();
    $.ajax({
        url: "/Index?handler=DisplayEmployee",
        type: "POST",
        data: { value: deptName },
        headers: { RequestVerificationToken: $('input:hidden[name="__RequestVerificationToken"]').val() },
        success: function(data) { $("#EmployeeResult").html(data); }
    });
});

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