EF Core中用复合主键配置双一对一关联的问题求助

需求背景 #

将原本固定包含2条数据的一对多关联，重构为基于复合主键的两个一对一关联，对应主客场交替的体育赛事场景。

初始建模类 #

Game类 #

public class Game
{
    [Key]
    public long Id {get; set;}
    public long HomeTeamId { get; set; }
    public long AwayTeamId { get; set; }
    public TeamGameStats HomeTeamStats { get; set; }
    public TeamGameStats AwayTeamStats { get; set; }
    //原始一对多关联代码：
    //public List<TeamGameStats> TeamStats{get;set;} = new();
}

TeamGameStats类 #

public class TeamGameStats
{
   public Game Game { get; set; }
   //当前尝试实现
   [Key]
   [Column(Order = 1)]
   public long GameId { get; set; }
   [Key] //原始代码中并非主键
   [Column(Order = 2)]
   public long TeamId { get; set; }
   public long Score { get; set; }
   public long Catches { get; set; }
}

配置错误问题 #

重写OnModelCreating时配置失败，代码如下：

protected override void OnModelCreating(ModelBuilder builder)
{
   base.OnModelCreating(builder);
   builder.Entity<TeamGameStats>().HasKey(tgs => new { tgs.GameId, tgs.TeamId });

   builder.Entity<Game>()
       .HasOne(g => g.HomeTeamStats)
       .WithOne(tgs => tgs.Game)
       .HasForeignKey<TeamGameStats>(nameof(TeamGameStats.GameId), nameof(TeamGameStats.TeamId) );

   //此行报错
   builder.Entity<Game>()
       .HasOne(g => g.AwayTeamStats)
       .WithOne(tgs => tgs.Game)
       .HasForeignKey<TeamGameStats>(nameof(TeamGameStats.GameId), nameof(TeamGameStats.TeamId));
}

错误信息：

System.InvalidOperationException: 'Cannot create a relationship between 'Game.AwayTeamStats' and 'TeamGameStats.Game' because a relationship already exists between 'Game.HomeTeamStats' and 'TeamGameStats.Game'. Navigations can only participate in a single relationship.

核心疑问：导航属性只能参与一个关联的原因，以及复合主键下多一对一关联的可行性。

业务场景示例 #

• 球队Bears：Id=5，球队Sharks：Id=12
• 3场交替主客场比赛的主键对应关系：
  1. 比赛Id=35：HomeTeamStats主键(35,5)，AwayTeamStats主键(35,12)
  2. 比赛Id=67：HomeTeamStats主键(67,12)，AwayTeamStats主键(67,5)
  3. 比赛Id=89：HomeTeamStats主键(89,5)，AwayTeamStats主键(89,12)

该方案优势：简化查询、便于数据更新、调试直观。当前一对多方案运行正常，仅为学习目的重构。

可行解决方案 #

调整后的TeamGameStats类 #

public Game HomeGame { get; set; }
public Game AwayGame { get; set; }

private long _gameId;

[Key]
[Column(Order = 1)]
public long GameId 
{
   get 
   {
     if(HomeGame != null)
       return HomeGame.Id;
     else if(AwayGame != null)
       return AwayGame.Id;
     else
       return _gameId;
   }
   set{_gameId = value;} 
}

[Key] //原始代码中并非主键
[Column(Order = 2)]
public long TeamId { get; set; }

调整后的OnModelCreating配置 #

protected override void OnModelCreating(ModelBuilder builder)
{
   base.OnModelCreating(builder);
   builder.Entity<TeamGameStats>().HasKey(tgs => new { tgs.GameId, tgs.TeamId });

   builder.Entity<Game>()
      .HasOne(g => g.HomeTeamStats)
      .WithOne(tgs => tgs.HomeGame)
      .HasForeignKey<Game>(g => new { g.Id, g.HomeTeamId });
   builder.Entity<Game>()
      .HasOne(g => g.AwayTeamStats)
      .WithOne(tgs => tgs.AwayGame)
      .HasForeignKey<Game>(g => new { g.Id, g.AwayTeamId });
}

方案说明：支持数据更新，查询时自动填充对应属性，虽有轻微冗余但更直观。

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