ASP.NET Core MVC：使用ViewModel获取详情功能失效问题

问题背景 #

在ASP.NET Core MVC项目中，Index方法通过多表关联查询构造NodeIndexViewModel并成功渲染视图，但访问详情页时触发以下PostgreSQL错误：

PostgresException: 42P01: relation "NodeIndexViewModel" does not exist

错误的Details方法代码如下：

public async Task<IActionResult> Details(int? id)
{
    if (id == null || _context.NodeIndexViewModel == null)
    {
        return NotFound();
    }

    var nodeIndexViewModel = await _context.NodeIndexViewModel
                                           .FirstOrDefaultAsync(m => m.Id == id);

    if (nodeIndexViewModel == null)
    {
        return NotFound();
    }

    return View(nodeIndexViewModel);
}

问题原因 #

NodeIndexViewModel是视图模型（ViewModel），仅用于封装视图所需数据，并非EF Core映射的数据库实体，因此_context中不存在对应的NodeIndexViewModel集合。直接通过_context.NodeIndexViewModel查询时，EF Core会尝试在数据库中查找名为NodeIndexViewModel的表，而该表实际不存在，导致报错。

解决方案 #

需要像Index方法一样，通过多表关联查询构造NodeIndexViewModel实例，或者先查询对应的实体再映射为ViewModel。以下是修正后的Details方法：

public async Task<IActionResult> Details(int? id)
{
    if (id == null)
    {
        return NotFound();
    }

    var nodeDetail = await (from node in _context.Nodes
                           join bnbridge in _context.BundleNodes on node.Id equals bnbridge.NodeId into NodeBundleIDGroup
                           from ax in NodeBundleIDGroup.DefaultIfEmpty()
                           join bundle in _context.Bundles on ax.BundleId equals bundle.Id into NodeBundleGroup
                           from bx in NodeBundleGroup.DefaultIfEmpty()
                           join agreement in _context.Agreements on bx.AgreementId equals agreement.Id into agGroup
                           from cx in agGroup.DefaultIfEmpty()
                           join org in _context.Organizations on cx.OrgId equals org.Id into oGroup
                           from ex in oGroup.DefaultIfEmpty()
                           join conorg in _context.Organizations on node.OrgId equals conorg.Id into tGroup
                           from fx in tGroup.DefaultIfEmpty()
                           where node.Id == id
                           select new NodeIndexViewModel
                           {
                               Id = node.Id,
                               Name = node.Name,
                               AssignedOrg = fx.ShortName,
                               ContractingOrg = ex.ShortName,
                               Expiry = bx.EndUtc,
                               LastActive = node.ActiveDate,
                               NodeType = bx.NodeTypeID
                           }).FirstOrDefaultAsync();

    if (nodeDetail == null)
    {
        return NotFound();
    }

    return View(nodeDetail);
}

优化建议 #

如果多表关联逻辑重复，可以将其封装为一个可复用的方法，避免Index和Details方法中重复编写查询代码：

private IQueryable<NodeIndexViewModel> GetNodeIndexQuery()
{
    return from node in _context.Nodes
           join bnbridge in _context.BundleNodes on node.Id equals bnbridge.NodeId into NodeBundleIDGroup
           from ax in NodeBundleIDGroup.DefaultIfEmpty()
           join bundle in _context.Bundles on ax.BundleId equals bundle.Id into NodeBundleGroup
           from bx in NodeBundleGroup.DefaultIfEmpty()
           join agreement in _context.Agreements on bx.AgreementId equals agreement.Id into agGroup
           from cx in agGroup.DefaultIfEmpty()
           join org in _context.Organizations on cx.OrgId equals org.Id into oGroup
           from ex in oGroup.DefaultIfEmpty()
           join conorg in _context.Organizations on node.OrgId equals conorg.Id into tGroup
           from fx in tGroup.DefaultIfEmpty()
           select new NodeIndexViewModel
           {
               Id = node.Id,
               Name = node.Name,
               AssignedOrg = fx.ShortName,
               ContractingOrg = ex.ShortName,
               Expiry = bx.EndUtc,
               LastActive = node.ActiveDate,
               NodeType = bx.NodeTypeID
           };
}

然后在Index和Details方法中调用：

• Index方法中替换原查询：var results = GetNodeIndexQuery();
• Details方法中：var nodeDetail = await GetNodeIndexQuery().Where(n => n.Id == id).FirstOrDefaultAsync();

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