实体框架核心-两个实体之间的多个一对多关系

Question

我有两个实体-团队和游戏。一支球队可以有很多场比赛(一对多)。

看起来像这样：

public class Team
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Game> Games { get; set; }
}
public class Game
{
public int Id { get; set; }
public DateTime Date { get; set; }
public int TeamId { get; set; }
public Team Team { get; set; }
}

这很好，但我想通过将比赛分为两类——主场和客场比赛，使其更加精致。然而，这将在两个实体之间引入另一种关系，我不知道如何定义它

我想会是这样吗？

public class Team
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Game> HomeGames { get; set; }
public ICollection<Game> AwayGames { get; set; }
}
public class Game
{
public int Id { get; set; }
public DateTime Date { get; set; }
public int HomeTeamId { get; set; }
public Team HomeTeam { get; set; }
public int AwayTeamId{ get; set; }
public Team AwayTeam { get; set; }
}

这样做会混淆实体框架，它无法决定如何解决关系。

有什么想法吗？

Answer 1

您必须告诉实体框架两个实体中的哪些属性涉及一个关联。在fluent映射API中，这是：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Team>().HasMany(t => t.HomeGames)
.WithOne(g => g.HomeTeam)
.HasForeignKey(g => g.HomeTeamId);
modelBuilder.Entity<Team>().HasMany(t => t.AwayGames)
.WithOne(g => g.AwayTeam)
.HasForeignKey(g => g.AwayTeamId).OnDelete(DeleteBehavior.Restrict);
}

您必须使用fluent API，因为默认情况下，EF将尝试创建两个带有级联删除的外键。SQL Server不允许这样做，因为它有臭名昭著的"多级联路径"限制。其中一个键不应该是级联的，只能通过fluent API进行配置。

Answer 2

基于关系-EF核心|Microsoft文档您可以使用数据注释

数据注释

有两个数据注释可用于配置关系，[ForeignKey]和[InverseProperty]。

[ForeignKey]

您可以使用数据注释来配置属性应用作给定的外键属性关系当外键属性为不是按惯例发现的。

[InverseProperty]

您可以使用数据注释来配置从属实体和主体实体上的导航属性配对。这通常是在有多对导航时完成的两种实体类型之间的属性。

public class Team
{
public int Id { get; set; }
public string Name { get; set; }
[InverseProperty("HomeTeam")]
public ICollection<Game> HomeGames { get; set; }
[InverseProperty("AwayTeam")]
public ICollection<Game> AwayGames { get; set; }
}
public class Game
{
public int Id { get; set; }
public DateTime Date { get; set; }
public int HomeTeamId { get; set; }
[ForeignKey("HomeTeamId")]
public Team HomeTeam { get; set; }
public int AwayTeamId{ get; set; }
[ForeignKey("AwayTeamId")]
public virtual Team AwayTeam { get; set; }
}

如果使用db。Database.Migrate()你会得到错误的

System.Data.SqlClient.SqlException：'引入FOREIGN KEY"Games"表上的约束"FK_Games_Teams_HomeTeamId"可能导致循环或多个级联路径。指定ON DELETE NO ACTION或ONUPDATE NO ACTION，或修改其他FOREIGN KEY约束。无法创建约束或索引。参见以前的错误

您可以将HomeTeamId AwayTeamId设置为int可为空

public class Team
{
public int Id { get; set; }
public string Name { get; set; }
[InverseProperty("HomeTeam")]
public ICollection<Game> HomeGames { get; set; }
[InverseProperty("AwayTeam")]
public ICollection<Game> AwayGames { get; set; }
}
public class Game
{
public int Id { get; set; }
public DateTime Date { get; set; }
public int? HomeTeamId { get; set; }
[ForeignKey("HomeTeamId")]
public Team HomeTeam { get; set; }
public int? AwayTeamId{ get; set; }
[ForeignKey("AwayTeamId")]
public virtual Team AwayTeam { get; set; }
}

或参阅级联删除-EF核心|Microsoft文档

• 这里是我测试和工作的完整代码(db优先，而不是代码优先)

• 对于代码优先使用int

• 对于程序.cs

  using System;
  using System.Collections.Generic;
  using System.ComponentModel.DataAnnotations.Schema;
  using Microsoft.EntityFrameworkCore;
  namespace stackoverflow54196199
  {
  public class Team
  {
  public int Id { get; set; }
  public string Name { get; set; }
  [InverseProperty("HomeTeam")]
  public ICollection<Game> HomeGames { get; set; }
  [InverseProperty("AwayTeam")]
  public ICollection<Game> AwayGames { get; set; }
  }
  public class Game
  {
  public int Id { get; set; }
  public DateTime Date { get; set; }
  public int HomeTeamId { get; set; }
  [ForeignKey("HomeTeamId")]
  public Team HomeTeam { get; set; }
  public int AwayTeamId { get; set; }
  [ForeignKey("AwayTeamId")]
  public Team AwayTeam { get; set; }
  }
  
  public class MyContext : DbContext
  {
  public DbSet<Game> Games { get; set; }
  public DbSet<Team> Teams { get; set; }
  
  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
  optionsBuilder.UseSqlServer("Server=.;Integrated Security=true;Initial Catalog=stackoverflow54196199;Persist Security Info=False;");
  }
  }
  class Program
  {
  static void Main(string[] args)
  {
  var db = new MyContext();
  foreach (var game in db.Games.Include(i => i.AwayTeam).Include(i => i.HomeTeam))
  {
  Console.WriteLine(game.HomeTeam.Name);
  Console.WriteLine(game.AwayTeam.Name);
  }
  Console.ReadLine();
  }
  }
  }
  

• 对于stackoverflow 54196199.csproj

  <PropertyGroup>
  <OutputType>Exe</OutputType>
  <TargetFramework>netcoreapp2.1</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
  <PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.1.0" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="2.1.0" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.1.0" />
  </ItemGroup>