multiple one-to-one relations using composite primary keys

Question

I am trying an experiment to switch from a one-to-many that will always have 2 in the list, and have 2x one-to-one relations using composite keys. Modeled classes:

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; }
    //original code would be what you expect from a one-to-many relations:
    //public List<TeamGameStats> TeamStats{get;set;} = new();
}

So think of it like any sports game with a home and away, but sometime it switches. Ill leave out the Team as they are irrelevant

public class TeamGameStats
{
   public Game Game { get; set; }
   //current working attempt
   [Key]
   [Column(Order = 1)]
   public long GameId { get; set; }
   [Key] //not a key in original code
   [Column(Order = 2)]
   public long TeamId { get; set; }
   public long Score { get; set; }
   public long Catches { get; set; }
}

My current attempt fails at configuring in the OnModelCreating, which is where I am not understanding and could use some direction. I am trying to use this experiment to gain a deeper understanding of EFCore and setting up relations. I typically keep the models simple enough that I dont need to override OnModelCreating but I wanted to try something weird and more complex.

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) );

   //errors on this line
   builder.Entity<Game>()
       .HasOne(g => g.AwayTeamStats)
       .WithOne(tgs => tgs.Game)
       .HasForeignKey<TeamGameStats>(nameof(TeamGameStats.GameId), nameof(TeamGameStats.TeamId));
}

The error I get is:

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.

So I understand that the error is telling me navigations can only have 1 setup. but I dont understand why? Can I hack it by have a relation go from Game->TeamGameStats, and then another from TeamGameStates->Game (seems silly and bad)?

To me this idea should be possible, while it isn't the best, I don't see why I cant have multiple 1-to-1's if I use composite keys?

I have tried a few different ways using the Fluent methods but the above seems to be the furthest without exceptions saying everything is wrong.

Consider the following example:

• Team Bears : Id = 5
• Team Sharks : Id = 12

The Bears play the Sharks 3 times, and alternate between home and away.

• Game 1 : Id = 35, HomeTeamId = 5 (Bears), AwayTeamId = 12 (Sharks)
• So the PKs of the HomeTeamStats = (35,5) and AwayTeamStats = (35,12)
• Game 2 : Id = 67, HomeTeamId = 12 (Sharks), AwayTeamId = 5 (Bears)
• So the PKs of the HomeTeamStats = (67,12) and AwayTeamStats = (67,5)
• Game 3 : Id = 89, HomeTeamId = 5 (Bears), AwayTeamId = 12 (Sharks)
• So the PKs of the HomeTeamStats = (89,5) and AwayTeamStats = (89,12)

One use case I want for this is easier queries of the TeamStats table without some of the logical checks. Currently I either compare the Game.HomeTeamId to the list, or use a boolean IsHomeTeam in the TeamStats to find which was the Home and Away team.

It will also help if I ever need to update existing data.

Also to see if it is possible, as it is easier to read the class information in a breakpoint instead of looking at the list and finding a property indicating if it is the home or away team.

EDIT: the other use case, which was the reason that prompted the experiment, is updating existing TeamGameStats for an existing Game would be easy/trivial as there isn't really a cascading OnUpdate like there is for OnDelete.

Everything that currently exists (with a list and one-to-many relation) works fine. I was doing some refactoring and thought I would try to change it to learn.

---

For completeness of the answer from Steve

New TeamGameStats (Game stays the same as above)

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] //not a key in original code
[Column(Order = 2)]
public long TeamId { get; set; }

Adjustments to the 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 });
}

This works for updating, and when queries from the DbContext ask for a Game, it fills the HomeTeamStats or the AwayTeamStats respectively. While there is slight redundancy as one Property will always be null, so far I like this better as it is clearer and quicker to see and the database is still clean.

Answer 1

You have two relationships while trying to express the FK on the TeamGameStats side, but notice that the FK definitions are identical. These are actually the PK for this table. The solution is to tell EF that the "FK" is on the side that can differentiate the two, that is on the Game, with the HomeTeamId and AwayTeamid:

builder.Entity<Game>()
   .HasOne(g => g.HomeTeamStats)
   .WithOne(tgs => tgs.Game)
   .HasForeignKey<Game>(g => new {g.GameId, g.HomeTeamId} );

builder.Entity<Game>()
   .HasOne(g => g.AwayTeamStats)
   .WithOne(tgs => tgs.Game)
   .HasForeignKey<Game>(g => new {g.GameId, g.AwayTeamId} );

These will then get hooked up against the PK on the TeamGameStats which is the GameId + TeamId.