EF关联两张表：如何将CompetitionId转换为CompetitionName并展示

问题背景 #

我有两张数据库表：Rewards存储核心数据，Competitions存储竞赛名称信息。已经能用SQL内连接查询得到正确结果，现在需要在EF中实现相同逻辑，最终仅展示由CompetitionId转换而来的CompetitionName列。相关代码如下：

参考SQL语句 #

SELECT Rewards.Id, Rewards.CompetitionId, Competitions.CompetitionName
FROM Rewards
INNER JOIN Competitions
ON Rewards.CompetitionId=Competitions.CompetitionId

现有Model代码 #

public class Reward
{
    [Required]
    public int Id { get; set; }
    [Display(Name = "Competition")]
    [Required]
    public int CompetitionId { get; set; }
    [Required]
    [Display(Name = "League")]
    public string LeagueId { get; set; }
    [Required]
    [Display(Name = "Game Week")]
    public string GameWeekId { get; set; }
    [Required]
    [Display(Name = "Type")]
    public string CardTypeId { get; set; }
    [Display(Name = "Name")]
    public string? Name { get; set; }
    [Display(Name = "Year")]
    public string? YearId { get; set; }
    [Display(Name = "Number")]
    public string? Number { get; set; }
    [Display(Name = "Comment")]
    public string? Comment { get; set; }
}

public class Competition
{
    public int CompetitionId { get; set; }
    [Display(Name = "Competition Name")]
    public string CompetitionName { get; set; }
}

现有Controller代码 #

[Authorize]
// GET: Rewards
public async Task<IActionResult> Index(string searchString)
{
    if (_context.Rewards == null)
    {
        return Problem("Entity set 'RewardsContext.Rewards'  is null.");
    }

    var query = from reward in _context.Rewards
                join competition in _context.Competitions
                on reward.CompetitionId equals competition.CompetitionId
                select new
                {
                    reward.Id,
                    competition.CompetitionName
                };

    var rewardsWithTranslatedCompetitionNames = query.ToList();

    return View(rewardsWithTranslatedCompetitionNames);
}

解决方案 #

1. 优化匿名类型查询（快速适配） #

你的LINQ查询逻辑已经正确实现了内连接，和SQL逻辑一致。但要注意视图的模型绑定问题——匿名类型无法直接强类型绑定，需调整视图使用动态模型，或补充Reward的其他字段到匿名类型中：

修改后的Controller代码（补充字段+异步优化）：

[Authorize]
// GET: Rewards
public async Task<IActionResult> Index(string searchString)
{
    if (_context.Rewards == null)
    {
        return Problem("Entity set 'RewardsContext.Rewards'  is null.");
    }

    var query = from reward in _context.Rewards
                join competition in _context.Competitions
                on reward.CompetitionId equals competition.CompetitionId
                select new
                {
                    reward.Id,
                    reward.LeagueId,
                    reward.GameWeekId,
                    reward.CardTypeId,
                    reward.Name,
                    reward.YearId,
                    reward.Number,
                    reward.Comment,
                    CompetitionName = competition.CompetitionName
                };

    // 可选：添加搜索功能
    if (!string.IsNullOrEmpty(searchString))
    {
        query = query.Where(r => r.CompetitionName.Contains(searchString) || (r.Name?.Contains(searchString) ?? false));
    }

    var rewardsWithCompetitionNames = await query.ToListAsync();
    return View(rewardsWithCompetitionNames);
}

对应的视图（Index.cshtml）使用动态模型：

@model IEnumerable<dynamic>

<table class="table">
    <thead>
        <tr>
            <th>@Html.DisplayNameFor(model => model.Id)</th>
            <th>@Html.DisplayNameFor(model => model.CompetitionName)</th>
            <th>@Html.DisplayNameFor(model => model.LeagueId)</th>
            <th>@Html.DisplayNameFor(model => model.GameWeekId)</th>
            <!-- 其他需要展示的列 -->
            <th></th>
        </tr>
    </thead>
    <tbody>
@foreach (var item in Model) {
        <tr>
            <td>@Html.DisplayFor(modelItem => item.Id)</td>
            <td>@Html.DisplayFor(modelItem => item.CompetitionName)</td>
            <td>@Html.DisplayFor(modelItem => item.LeagueId)</td>
            <td>@Html.DisplayFor(modelItem => item.GameWeekId)</td>
            <!-- 其他列展示 -->
            <td>
                <a asp-action="Edit" asp-route-id="@item.Id">Edit</a> |
                <a asp-action="Details" asp-route-id="@item.Id">Details</a> |
                <a asp-action="Delete" asp-route-id="@item.Id">Delete</a>
            </td>
        </tr>
}
    </tbody>
</table>

2. 使用强类型ViewModel（推荐方案） #

匿名类型在视图中不够直观，容易出错，推荐创建ViewModel封装需要展示的字段：

首先创建ViewModel：

public class RewardViewModel
{
    public int Id { get; set; }
    [Display(Name = "Competition")]
    public string CompetitionName { get; set; }
    [Display(Name = "League")]
    public string LeagueId { get; set; }
    [Display(Name = "Game Week")]
    public string GameWeekId { get; set; }
    [Display(Name = "Type")]
    public string CardTypeId { get; set; }
    [Display(Name = "Name")]
    public string? Name { get; set; }
    [Display(Name = "Year")]
    public string? YearId { get; set; }
    [Display(Name = "Number")]
    public string? Number { get; set; }
    [Display(Name = "Comment")]
    public string? Comment { get; set; }
}

修改Controller查询逻辑：

[Authorize]
// GET: Rewards
public async Task<IActionResult> Index(string searchString)
{
    if (_context.Rewards == null)
    {
        return Problem("Entity set 'RewardsContext.Rewards'  is null.");
    }

    var query = from reward in _context.Rewards
                join competition in _context.Competitions
                on reward.CompetitionId equals competition.CompetitionId
                select new RewardViewModel
                {
                    Id = reward.Id,
                    CompetitionName = competition.CompetitionName,
                    LeagueId = reward.LeagueId,
                    GameWeekId = reward.GameWeekId,
                    CardTypeId = reward.CardTypeId,
                    Name = reward.Name,
                    YearId = reward.YearId,
                    Number = reward.Number,
                    Comment = reward.Comment
                };

    if (!string.IsNullOrEmpty(searchString))
    {
        query = query.Where(r => r.CompetitionName.Contains(searchString) || (r.Name?.Contains(searchString) ?? false));
    }

    var rewardsWithCompetitionNames = await query.ToListAsync();
    return View(rewardsWithCompetitionNames);
}

对应的强类型视图：

@model IEnumerable<YourNamespace.RewardViewModel>

<table class="table">
    <thead>
        <tr>
            <th>@Html.DisplayNameFor(model => model.Id)</th>
            <th>@Html.DisplayNameFor(model => model.CompetitionName)</th>
            <th>@Html.DisplayNameFor(model => model.LeagueId)</th>
            <th>@Html.DisplayNameFor(model => model.GameWeekId)</th>
            <!-- 其他列 -->
            <th></th>
        </tr>
    </thead>
    <tbody>
@foreach (var item in Model) {
        <tr>
            <td>@Html.DisplayFor(modelItem => item.Id)</td>
            <td>@Html.DisplayFor(modelItem => item.CompetitionName)</td>
            <td>@Html.DisplayFor(modelItem => item.LeagueId)</td>
            <td>@Html.DisplayFor(modelItem => item.GameWeekId)</td>
            <!-- 其他列 -->
            <td>
                <a asp-action="Edit" asp-route-id="@item.Id">Edit</a> |
                <a asp-action="Details" asp-route-id="@item.Id">Details</a> |
                <a asp-action="Delete" asp-route-id="@item.Id">Delete</a>
            </td>
        </tr>
}
    </tbody>
</table>

3. 添加导航属性（符合EF Core设计规范） #

如果允许修改Model，可以给Reward类添加导航属性，简化查询逻辑：

修改Reward Model：

public class Reward
{
    [Required]
    public int Id { get; set; }
    [Required]
    [Display(Name = "Competition")]
    public int CompetitionId { get; set; }
    // 添加导航属性
    public virtual Competition Competition { get; set; }
    // 其他原有属性...
}

查询逻辑简化为：

var query = _context.Rewards
                    .Include(r => r.Competition) // 预加载关联数据
                    .Select(r => new RewardViewModel
                    {
                        Id = r.Id,
                        CompetitionName = r.Competition.CompetitionName,
                        // 其他属性映射
                    });

这种方式更符合EF的ORM设计思想，后续扩展关联数据查询会更便捷。

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