MVC视图中如何基于SavedJobs表过滤Job表数据并优化用户职位关联？

我需要从两个表中获取数据：Job表存储所有职位信息，SavedJobs表用于关联职位ID与申请该职位的用户ID。目标是在视图页面中通过SavedJobs表中的ID过滤Job表，展示用户已申请的职位列表。

错误尝试1：无法在单个视图访问多个模型数据 #

以下视图代码无法正常运行，因为无法在绑定Job集合的视图中访问SavedJob的字段：

@model IEnumerable<JobPortalApplication.Models.Job>

@{
    ViewBag.Title = "Index";
}

<h2>Applicants</h2>

<table class="table">
    <tr>
        <th>@Html.DisplayNameFor(model => model.Type)</th>
        <th>@Html.DisplayNameFor(model => model.Location)</th>
        <th>@Html.DisplayNameFor(model => model.Company)</th>
        <th>@Html.DisplayNameFor(model => model.Category)</th>
        <th>@Html.DisplayNameFor(model => model.Experience)</th>
        <th>@Html.DisplayNameFor(model => model.Salary)</th>
        <th>@Html.DisplayNameFor(model => model.Education)</th>
        <th>@Html.DisplayNameFor(model => model.Date)</th>
        <th></th>
    </tr>

@foreach (var item in Model) {
if(item.ID.Equals(JobPortal.Models.SavedJob.JobId)){// 无法访问SavedJob的JobId字段
    <tr>
        <td>@Html.DisplayFor(modelItem => item.Type)</td>
        <td>@Html.DisplayFor(modelItem => item.Location)</td>
        <td>@Html.DisplayFor(modelItem => item.Company)</td>
        <td>@Html.DisplayFor(modelItem => item.Category)</td>
        <td>@Html.DisplayFor(modelItem => item.Experience)</td>
        <td>$@Html.DisplayFor(modelItem => item.Salary)</td>
        <td>@Html.DisplayFor(modelItem => item.Education)</td>
        <td>@Html.DisplayFor(modelItem => item.Date)</td>
        <td>@Html.ActionLink("Details", "Details", new { id=item.ID })</td>
    </tr>
   }
}

</table>

错误尝试2：ViewModel使用不当 #

尝试用ViewModel传递多个模型，但未正确过滤数据，且视图双重循环效率低下：

控制器代码 #

public ActionResult Details(int? id)
{
    
    var Job1 = GetJobs();
    var SavedJobs1 = GetSavedJobs();

    IndexVM model = new IndexVM();
    model.JobsList = Job1;
    model.SavedJobList = SavedJobs1;

    if (id == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    User user = _dbContext.Users.Find(id);
    if (user == null)
    {
        return HttpNotFound();
    }
    return View(model);
}

视图代码 #

@foreach (var item1 in Model.JobsList)
{
    foreach (var item in Model.SavedJobList)
    {
        if (item.JobId.Equals(item1.ID))
        {
            <table class="table">
                <dt>@item1.ID</dt>
                <dt>@item1.Type</dt>
                <dt>@item1.Location</dt>
                <dt>@item1.Company</dt>
                <dt>@item1.Experience</dt>
                <dt>@item1.Salary</dt>
                <dt>@item1.Education</dt>
                <dt>@item1.Date</dt>
                <dt>@item1.Category</dt>
            </table>
        }
    }
}

正确实现方案 #

1. 创建专用ViewModel #

定义一个ViewModel用于传递过滤后的用户申请职位数据：

public class UserAppliedJobsVM
{
    public IEnumerable<Job> AppliedJobs { get; set; }
    // 可按需添加用户信息等额外字段
}

2. 控制器修正（过滤逻辑移至后端） #

先获取目标用户，再查询该用户的所有申请职位，避免传递全量数据：

public ActionResult UserAppliedJobs(int? userId)
{
    if (userId == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }

    var user = _dbContext.Users.Find(userId);
    if (user == null)
    {
        return HttpNotFound();
    }

    // 方式1：使用导航属性（需提前配置实体关联）
    var appliedJobs = _dbContext.SavedJobs
        .Where(sj => sj.UserId == userId)
        .Select(sj => sj.Job)
        .ToList();

    // 方式2：无导航属性时用Join查询
    // var appliedJobs = from sj in _dbContext.SavedJobs
    //                   join job in _dbContext.Jobs on sj.JobId equals job.ID
    //                   where sj.UserId == userId
    //                   select job;

    var model = new UserAppliedJobsVM
    {
        AppliedJobs = appliedJobs
    };

    return View(model);
}

3. 视图优化（仅渲染已过滤数据） #

@model JobPortalApplication.Models.UserAppliedJobsVM

@{
    ViewBag.Title = "我申请的职位";
}

<h2>我申请的职位</h2>

<table class="table">
    <tr>
        <th>@Html.DisplayNameFor(model => model.AppliedJobs.First().Type)</th>
        <th>@Html.DisplayNameFor(model => model.AppliedJobs.First().Location)</th>
        <th>@Html.DisplayNameFor(model => model.AppliedJobs.First().Company)</th>
        <th>@Html.DisplayNameFor(model => model.AppliedJobs.First().Category)</th>
        <th>@Html.DisplayNameFor(model => model.AppliedJobs.First().Experience)</th>
        <th>@Html.DisplayNameFor(model => model.AppliedJobs.First().Salary)</th>
        <th>@Html.DisplayNameFor(model => model.AppliedJobs.First().Education)</th>
        <th>@Html.DisplayNameFor(model => model.AppliedJobs.First().Date)</th>
        <th></th>
    </tr>

@foreach (var item in Model.AppliedJobs) {
    <tr>
        <td>@Html.DisplayFor(modelItem => item.Type)</td>
        <td>@Html.DisplayFor(modelItem => item.Location)</td>
        <td>@Html.DisplayFor(modelItem => item.Company)</td>
        <td>@Html.DisplayFor(modelItem => item.Category)</td>
        <td>@Html.DisplayFor(modelItem => item.Experience)</td>
        <td>$@Html.DisplayFor(modelItem => item.Salary)</td>
        <td>@Html.DisplayFor(modelItem => item.Education)</td>
        <td>@Html.DisplayFor(modelItem => item.Date)</td>
        <td>@Html.ActionLink("详情", "Details", new { id=item.ID })</td>
    </tr>
}

</table>

更佳的职位-用户关联方案 #

1. 配置实体导航属性：让EF自动处理关联查询，简化代码：

public class SavedJob
{
    public int Id { get; set; }
    public int JobId { get; set; }
    public int UserId { get; set; }

    // 导航属性
    public virtual Job Job { get; set; }
    public virtual User User { get; set; }
}

// Job实体添加反向导航
public class Job
{
    public int ID { get; set; }
    // 其他业务字段...
    public virtual ICollection<SavedJob> SavedJobs { get; set; }
}

2. 业务语义优化：将SavedJobs改为AppliedJobs，更贴合"用户申请职位"的业务场景，避免歧义。
3. 分层处理逻辑：过滤、关联等数据处理逻辑放在控制器/服务层，视图仅负责渲染，保持前后端职责清晰。

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