EF Core中如何访问连接表的子表？代码转换报错求助

我写了下面这个方法，用来从UserReward表中根据UserId获取所有Reward：

public async Task<IList<Reward>> GetAllRewardsByUserIdAsync(Guid userId)
{
    var userRewards = await context.UserReward
        .Include(d => d.User)
        .Include(d => d.Reward)
        .AsNoTracking()
        .Where(d => d.ApplicationUserId == userId)
        .ToListAsync();

    IList<Reward> rewards = new List<Reward>();

    foreach (Reward reward in userRewards)
    {
        rewards.Add(reward);
    }

    return rewards;
}

运行时出现了Can't convert UserReward into Reward的错误，请问该如何解决？

以下是ApplicationUser、Reward和UserReward的实体类定义：

public class ApplicationUser : IdentityUser<Guid>
{
    public ApplicationUser()
    {
    
    }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public Guid CategoryId { get; set; }
    public Category Category { get; set; }
    public IList<Reward> Rewards { get; set; }
    public DateTime CreatedDateTime { get; set; }
}

public class Reward
{
    public Guid RewardId { get; set; }
    public string Name { get; set; }
    public bool HasBeenUsed { get; set; }
    public IList<ApplicationUser> Users { get; set; }
    public DateTime ExpiresOn { get; set; }
}

public class UserReward
{
    public Guid ApplicationUserId { get; set; }
    public ApplicationUser User { get; set; }

    public Guid RewardId { get; set; }
    public Reward Reward { get; set; }
}

错误原因 #

userRewards是UserReward类型的集合，你在foreach里错误地把集合中的UserReward实例当成Reward类型遍历，这两种类型无法直接转换，因此触发报错。

解决方法 #

需要从每个UserReward实例中取出它的Reward属性，再添加到结果集合中，提供两种实现方式：

方式1：修正foreach循环

public async Task<IList<Reward>> GetAllRewardsByUserIdAsync(Guid userId)
{
    var userRewards = await context.UserReward
        .Include(d => d.Reward) // 不需要Include User，因为仅需获取Reward数据
        .AsNoTracking()
        .Where(d => d.ApplicationUserId == userId)
        .ToListAsync();

    IList<Reward> rewards = new List<Reward>();

    foreach (var userReward in userRewards) // 遍历UserReward实例
    {
        rewards.Add(userReward.Reward); // 取出对应的Reward属性
    }

    return rewards;
}

方式2：用LINQ直接映射（更简洁）

public async Task<IList<Reward>> GetAllRewardsByUserIdAsync(Guid userId)
{
    return await context.UserReward
        .Include(d => d.Reward)
        .AsNoTracking()
        .Where(d => d.ApplicationUserId == userId)
        .Select(userReward => userReward.Reward) // 直接选择Reward属性完成映射
        .ToListAsync();
}

额外优化 #

如果EF Core的多对多关系配置正确（通过UserReward作为连接表），还可以直接通过ApplicationUser的导航属性获取Reward，写法更直观：

public async Task<IList<Reward>> GetAllRewardsByUserIdAsync(Guid userId)
{
    var user = await context.ApplicationUsers
        .Include(u => u.Rewards)
        .AsNoTracking()
        .FirstOrDefaultAsync(u => u.Id == userId);
    
    return user?.Rewards ?? new List<Reward>();
}

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