c# – 如何处理MVC中的2对1关系？

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

尝试将此模型迁移到数据库时,出现以下错误：

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.

(this stackoverflow question的答案很好地解释了为什么我会收到此错误.)

为了避免这个错误,我添加了以下流畅的API代码：

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

我现在遇到的问题是,当我尝试删除一个Player,其Id在一个或多个游戏中被引用.

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.

如果我正确地理解了这个错误,我得到这个错误,因为游戏失败的一个外键约束(级联删除是假的,所以游戏不会随玩家一起被删除.相反,Player1Id(或Player2Id)是设置为null,这会打破约束,因为它们都是必需的且不可为空的)

所以我尝试在Seed()方法中创建一个类似this SO question的触发器.

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

当我检查我的数据库时,触发器确实被添加,但它似乎不起作用,因为我仍然得到与添加触发器之前相同的错误.

那么,我该怎么做才能使这项工作按预期进行？ (删除播放器也删除关联的游戏,删除游戏不会删除播放器)

编辑：我想到的一个可能的解决方案是编辑我的控制器中的删除方法,首先获得Player1Id或Player2Id等于Player.Id的所有游戏,删除那些然后删除播放器,但(我认为)删除播放器时我不必担心游戏,实体框架应该为我做这个.

编辑2 :(回应usr的评论)这是我的删除代码：

控制器：

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

回购：

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

编辑3：在删除播放器之前,我设法通过查询和删除受影响的游戏来使其工作.

回购代码：

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