ASP.NET 4.6 MVC时态表记录差异高亮实现方案咨询

核心思路：后端预处理差异，前端渲染高亮 #

由于分页场景下前端无法获取上一页的历史记录，建议在后端（控制器/服务层）预处理每条记录与前一条的差异状态，再传递到前端渲染高亮。前端直接处理仅适用于无分页的小数据量场景，逻辑完整性不足。

1. 后端改造：添加差异标记的ViewModel与预处理逻辑 #

步骤1：创建带差异标记的ViewModel

新增一个ViewModel类，继承或包含原有历史记录字段，同时添加每个字段的变更状态标记：

public class ServiceDeliveryHistoryDiffViewModel
{
    public int dbId { get; set; }
    public string Reference { get; set; }
    public DateTime DateLastModified { get; set; }
    public string ServiceSupportReference { get; set; }
    public DateTime? ClaimDate { get; set; }
    public string CreatedByName { get; set; }
    public int ServiceProviderId { get; set; }
    public string ProviderInvoiceNumber { get; set; }

    // 差异标记字段
    public bool IsReferenceChanged { get; set; }
    public bool IsServiceSupportReferenceChanged { get; set; }
    public bool IsClaimDateChanged { get; set; }
    public bool IsServiceProviderIdChanged { get; set; }
    public bool IsProviderInvoiceNumberChanged { get; set; }
}

步骤2：控制器中预处理差异数据

修改控制器逻辑，对排序后的历史记录逐一对比前一条，生成带差异标记的列表：

public async Task<ActionResult> ClientHistoryCompare(string currentFilter, string sortOrder, string searchString, DateTime? start, DateTime? end, int? pageHistory, int? pageClient)
{
    ViewBag.CurrentSort = sortOrder;
    ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
    ViewBag.DateSortParm = sortOrder == "Date" ? "date_desc" : "Date";

    if (searchString != null)
    {
        pageHistory = 1;
        pageClient = 1;
    }
    else
    {
        searchString = currentFilter;
    }

    ViewBag.CurrentFilter = searchString;

    ClientViewModel clientViewModel = new ClientViewModel();
    IQueryable<Client> clients = db.Clients.OrderBy(s => s.LastName);
    IQueryable<ClientHistory> clientHistories = db.ClientHistories;

    Guid clientGuid;

    if (!String.IsNullOrEmpty(searchString))
    {
        clients = clients.Where(s => s.Identifier.ToString() == searchString);
        var client = await clients.FirstOrDefaultAsync();
        
        if (client != null)
        {
            clientGuid = client.Identifier;
            clientHistories = clientHistories.Where(i => i.Identifier == clientGuid);
        }
        else return new HttpNotFoundResult("Client ID not found");
    }

    // 统一按时间排序（确保历史顺序正确）
    switch (sortOrder)
    {
        case "date_desc":
            clientHistories = clientHistories.OrderByDescending(s => s.DateLastModified);
            break;
        default:
            clientHistories = clientHistories.OrderBy(s => s.DateLastModified);
            break;
    }

    // 预处理差异数据
    var sortedHistories = await clientHistories.ToListAsync();
    var diffHistories = new List<ServiceDeliveryHistoryDiffViewModel>();
    
    for (int i = 0; i < sortedHistories.Count; i++)
    {
        var current = sortedHistories[i];
        var prev = i > 0 ? sortedHistories[i-1] : null;
        
        diffHistories.Add(new ServiceDeliveryHistoryDiffViewModel
        {
            dbId = current.dbId,
            Reference = current.Reference,
            DateLastModified = current.DateLastModified,
            ServiceSupportReference = current.ServiceSupportReference,
            ClaimDate = current.ClaimDate,
            CreatedByName = current.CreatedByName,
            ServiceProviderId = current.ServiceProviderId,
            ProviderInvoiceNumber = current.ProviderInvoiceNumber,
            
            // 对比字段差异
            IsReferenceChanged = prev != null && current.Reference != prev.Reference,
            IsServiceSupportReferenceChanged = prev != null && current.ServiceSupportReference != prev.ServiceSupportReference,
            IsClaimDateChanged = prev != null && current.ClaimDate != prev.ClaimDate,
            IsServiceProviderIdChanged = prev != null && current.ServiceProviderId != prev.ServiceProviderId,
            IsProviderInvoiceNumberChanged = prev != null && current.ProviderInvoiceNumber != prev.ProviderInvoiceNumber
        });
    }

    int pageSize = 10;
    int pageNumberClient = (pageClient ?? 1);
    int pageNumberHistory = (pageHistory ?? 1);
    
    // 更新ViewModel的历史记录列表类型
    clientViewModel.ServiceDeliveryHistories = diffHistories.ToPagedList(pageNumberHistory, pageSize);
    clientViewModel.Clients = await clients.ToPagedListAsync(pageNumberClient, pageSize);

    return View(clientViewModel);
}

优化点：将原有的ToListAsync提前改为IQueryable操作，让过滤和排序在数据库层面执行，提升性能。

2. 前端Razor页面改造：高亮变更字段 #

在foreach循环中，根据差异标记为对应单元格添加Bootstrap高亮样式（如bg-danger红色背景）：

<table class="table table-striped">
<tr>
    <th>DB ID</th>
    <th>Reference</th>
    <th>@Html.ActionLink("Last Modified", "ServiceDeliveryHistoryCompare", new { sortOrder = ViewBag.DateSortParm, currentFilter = ViewBag.CurrentFilter })</th>
    <th>Service Support Reference</th>
    <th>Claim Date</th>
    <th>Created By</th>
    <th>Service Provider ID</th>
    <th>Provider Invoice Number</th>
</tr>
@foreach (var sd in Model.ServiceDeliveryHistories)
{
<tr>
    <td>@sd.dbId</td>
    <td class="@(sd.IsReferenceChanged ? "bg-danger text-white" : "")">@sd.Reference</td>
    <td>@sd.DateLastModified.ToString("yyyy-MM-dd HH:mm:ss")</td>
    <td class="@(sd.IsServiceSupportReferenceChanged ? "bg-danger text-white" : "")">@sd.ServiceSupportReference</td>
    <td class="@(sd.IsClaimDateChanged ? "bg-danger text-white" : "")">@sd.ClaimDate?.ToString("yyyy-MM-dd")</td>
    <td>@sd.CreatedByName</td>
    <td class="@(sd.IsServiceProviderIdChanged ? "bg-danger text-white" : "")">@sd.ServiceProviderId</td>
    <td class="@(sd.IsProviderInvoiceNumberChanged ? "bg-danger text-white" : "")">@sd.ProviderInvoiceNumber</td>
</tr>
}
</table>
Page @(Model.ServiceDeliveryHistories.PageCount < Model.ServiceDeliveryHistories.PageNumber ? 0 : Model.ServiceDeliveryHistories.PageNumber) of @Model.ServiceDeliveryHistories.PageCount

3. Entity Framework时态表优化说明 #

如果使用的是SQL Server原生时态表（系统自动维护历史表），EF提供AsOf方法可查询指定时间点的实体状态，但你的场景是对比全量历史变更，仍需按上述逻辑处理。额外优化建议：

• 避免直接查询全量历史表，通过Identifier过滤后再拉取数据
• 使用OrderBy确保历史记录按修改时间顺序排列，对比逻辑才会准确

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