How to handle a 2-to-1 relation in MVC?

Question

So, in my model I have a Player class and a Game class, Game has 2 required Players, like so: (along with some other fields I didn't include here)

public class Game
{
    public Player Player1 { get; set; }

    [Required]
    public int Player1Id { get; set; }

    public Player Player2 { get; set; }

    [Required]
    public int Player2Id { get; set; }
}

When trying to migrate this model to the database, I get the following error:

Introducing FOREIGN KEY constraint 'FK_dbo.Games_dbo.Players_Player2Id' on table 'Games' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

(The answers to this stackoverflow question give a good explanation of why I get this error.)

To avoid this error, I added the following fluent API code:

modelBuilder.Entity<Game>()
        .HasRequired(c => c.Player1)
        .WithMany()
        .HasForeignKey(c => c.Player1Id)
        .WillCascadeOnDelete(false);

modelBuilder.Entity<Game>()
       .HasRequired(c => c.Player2)
       .WithMany()
       .HasForeignKey(c => c.Player2Id)
       .WillCascadeOnDelete(false);

The issue I now get however, is when I try deleting a Player, whose Id is referenced in one or more Games.

The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

If I'm understanding this error correctly, I get this error because of one of the foreign key constraints on Game failing (cascade delete is false, so the Game doesn't get deleted along with the player. Instead Player1Id (or Player2Id) is set to null, which breaks the constraint, since both are Required and non-nullable)

So I tried creating a trigger like in this SO question in the Seed() method.

context.Database.ExecuteSqlCommand("CREATE TRIGGER Game_Player_Del on dbo.Players instead of delete as " +
            "set nocount on " +
            "delete from dbo.Games where Player1Id in (select Id from deleted) or " +
            "Player2Id in (Select Id from deleted) " + 
            "delete from dbo.Players where Id in (select Id from deleted)");

When I check my database, the trigger does get added, but it doesn't seem to work as I still get the same error as before adding the trigger.

So, what should I do to make this work as intended? (Deleting a player also deletes associated Games, deleting a Game doesn't delete the players)

Edit: a possible solution I just thought of would be to edit the delete method in my controller, to first get all games where either Player1Id or Player2Id are equal to Player.Id, delete those and then delete the player, but (I think) I shouldn't have to worry about Games when deleting a player, entity framework should do this for me.

Edit 2: (in response to usr's comment) Here's my deletion code:

Controller:

[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
    repo.deletePlayer(id);
    return RedirectToAction("Index");
}

Repo:

public void deletePlayer(int id)
{
    using (var context = new TennisContext())
    {
        player = context.Players.Find(id);
        context.Entry(player).State = EntityState.Deleted;
        context.SaveChanges();
    }
}

Edit 3: I managed to make it work by querying and deleting the affected games before deleting the player.

Repo code:

public void deletePlayer(int id)
{
    using (var context = new TennisContext())
    {
        player = context.Players.Find(id);
        List<Game> playerGames = context.Games.Where(g => g.Player1Id == id || g.Player2Id == id).ToList();
        foreach (Game g in playerGames)
        {
            context.Entry(g).State = EntityState.Deleted;
        }
        context.Entry(player).State = EntityState.Deleted;
        context.SaveChanges();
    }
}

Answer 1

Delete the games manually using Entity Framework code. Query the games that are affected and pass them to the appropriate delete method (e.g. the Entry(player).State = EntityState.Deleted thing or any other equivalent).

Since you can't use automatic cascading do it manually.

Answer 2

You have [Required] attribute on both players Ids. What will happen if you try to delete on of the players? Maybe Id one of the player should be nullable? Agree with comment that trigger - is not solution for you.