实体框架 - 外键约束

本文关键字：约束 框架 实体 | 更新日期: 2023-09-27 18:31:44

>我在数据库中有 3 个表：

歌曲（ID、标题、发行日期）
专辑（ID、标题、发行日期）
艺术家（ID、名字、姓氏）

我有一个相关表，以便歌曲可以与专辑或艺术家或两者相关：

RelatedSong （ID， ParentID， SongID， TrackNumber） （外键在 Album.ID 和 Artist.ID 上，用于ParentID和显然 Song.ID 用于SongID）

因此，使用这四个表，我希望实体框架生成模型，这些模型允许我简单地在我的 MVC 项目中执行和运行，但由于外键约束，它在保存时失败。 如果我设置 ParentID = Album.ID，那么它会抱怨 Artist.ID 为 NULL，反之亦然。 有什么建议吗？ 我正在重写现有应用程序的前端，因此数据库无法更改。 我需要知道如何构建模型才能正常工作。 它要么在模型中，要么在模型生成器（Fluent API）中。

专辑型号：

[Table("Album")]
public partial class Album
{
    public Album()
    {
        RelatedAlbums = new HashSet<RelatedAlbum>();
        RelatedSongs = new HashSet<RelatedSong>();
    }
    public Guid ID { get; set; }
    [Required]
    public string Title { get; set; }
    public DateTime ReleaseDate { get; set; }
    public virtual ICollection<RelatedAlbum> RelatedAlbums { get; set; }
    public virtual ICollection<RelatedSong> RelatedSongs { get; set; }
}

艺术家模型：

[Table("Artist")]
public partial class Artist
{
    public Artist()
    {
        RelatedAlbums = new HashSet<RelatedAlbum>();
        RelatedSongs = new HashSet<RelatedSong>();
    }
    public Guid ID { get; set; }
    public string FirstName { get; set; }
    [Required]
    public string LastName { get; set; }
    public virtual ICollection<RelatedAlbum> RelatedAlbums { get; set; }
    public virtual ICollection<RelatedSong> RelatedSongs { get; set; }
}

相关专辑：

[Table("RelatedAlbum")]
public partial class RelatedAlbum
{
    public Guid ID { get; set; }
    public Guid ParentID { get; set; }
    public Guid AlbumID { get; set; }
    public virtual Album Album { get; set; }
    public virtual Artist Artist { get; set; }
}

相关歌曲：

[Table("RelatedSong")]
public partial class RelatedSong
{
    public Guid ID { get; set; }
    public Guid ParentID { get; set; }
    public Guid SongID { get; set; }
    public int? TrackNumber { get; set; }
    public virtual Album Album { get; set; }
    public virtual Artist Artist { get; set; }
    public virtual Song Song { get; set; }
}

歌：

[Table("Song")]
public partial class Song
{
    public Song()
    {
        RelatedSongs = new HashSet<RelatedSong>();
    }
    public Guid ID { get; set; }
    [Required]
    public string Title { get; set; }
    public DateTime ReleaseDate { get; set; }
    public virtual ICollection<RelatedSong> RelatedSongs { get; set; }
}

数据库上下文：

public partial class MusicDbContext : DbContext
{
    public MusicDbContext()
        : base("name=MusicDbContext")
    {
    }
    public virtual DbSet<Album> Albums { get; set; }
    public virtual DbSet<Artist> Artists { get; set; }
    public virtual DbSet<RelatedAlbum> RelatedAlbums { get; set; }
    public virtual DbSet<RelatedSong> RelatedSongs { get; set; }
    public virtual DbSet<Song> Songs { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Album>()
            .HasMany(e => e.RelatedAlbums)
            .WithRequired(e => e.Album)
            .WillCascadeOnDelete(false);
        modelBuilder.Entity<Album>()
            .HasMany(e => e.RelatedSongs)
            .WithRequired(e => e.Album)
            .HasForeignKey(e => e.ParentID)
            .WillCascadeOnDelete(false);
        modelBuilder.Entity<Artist>()
            .HasMany(e => e.RelatedAlbums)
            .WithRequired(e => e.Artist)
            .HasForeignKey(e => e.ParentID)
            .WillCascadeOnDelete(false);
        modelBuilder.Entity<Artist>()
            .HasMany(e => e.RelatedSongs)
            .WithRequired(e => e.Artist)
            .HasForeignKey(e => e.ParentID)
            .WillCascadeOnDelete(false);
        modelBuilder.Entity<Song>()
            .HasMany(e => e.RelatedSongs)
            .WithRequired(e => e.Song)
            .WillCascadeOnDelete(false);
    }
}

更新：
下面是 Create 方法的控制器代码。 

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "ID,ParentID,SongID,TrackNumber")] RelatedSong relatedSong)
{
    if (ModelState.IsValid)
    {
        relatedSong.ID = Guid.NewGuid();
        db.RelatedSongs.Add(relatedSong);
        db.SaveChanges();
        return RedirectToAction("Index");
    }
    ViewBag.ParentID = new SelectList(db.Albums, "ID", "Title", relatedSong.ParentID);
    ViewBag.SongID = new SelectList(db.Songs, "ID", "Title", relatedSong.SongID);
    return View(relatedSong);
}

更新 2：
也许数据库模型不正确或其他什么？ 不知道为什么这是不可能的，因为在我看来，这是将数据与多个"父级"相关联的最有效方法。 我刚刚读了另一篇文章，说这是不可能的（但为什么数据库设计师允许我这样做？...

请参见： 单个列的多个外键

实体框架 - 外键约束

你的问题在这里：

1.

    modelBuilder.Entity<Album>()
        .HasMany(e => e.RelatedAlbums)
        .WithRequired(e => e.Album)
        .WillCascadeOnDelete(false);

应该有WithOptional(e => e.Album)

阿拉伯数字。

    modelBuilder.Entity<Album>()
        .HasMany(e => e.RelatedSongs)
        .WithRequired(e => e.Album)
        .HasForeignKey(e => e.ParentID)
        .WillCascadeOnDelete(false);

应该有WithOptional(e => e.Album)

3.

    modelBuilder.Entity<Artist>()
        .HasMany(e => e.RelatedAlbums)
        .WithRequired(e => e.Artist)
        .HasForeignKey(e => e.ParentID)
        .WillCascadeOnDelete(false);

应该有WithOptional(e => e.Artist)

4.

    modelBuilder.Entity<Artist>()
        .HasMany(e => e.RelatedSongs)
        .WithRequired(e => e.Artist)
        .HasForeignKey(e => e.ParentID)
        .WillCascadeOnDelete(false);

应该有WithOptional(e => e.Artist)

5.

    modelBuilder.Entity<Song>()
        .HasMany(e => e.RelatedSongs)
        .WithRequired(e => e.Song)
        .WillCascadeOnDelete(false);

应该有WithOptional(e => e.Song)

您写道它们不是必需的，但在配置中您将它们设置为必需的。并且应将外键属性设置为可为空的类型。

    [Table("RelatedSong")]
    public partial class RelatedSong
    {
        public Guid ID { get; set; }
        public Guid? ParentID { get; set; }
        ...
    }
    [Table("RelatedAlbum")]
    public partial class RelatedAlbum
    {
        public Guid ID { get; set; }
        public Guid? ParentID { get; set; }
        public Guid? AlbumID { get; set; }
        ...
    }

等等。

您正在尝试插入没有"专辑"的"相关歌曲"，这是必需的

if (ModelState.IsValid)
{
    relatedSong.ID = Guid.NewGuid();
    db.RelatedSongs.Add(relatedSong);
    db.SaveChanges();
    return RedirectToAction("Index");
}

如果您的关系/类型设置略有不同，您可以使用这样的东西

 if (ModelState.IsValid)
    {
       Song song = GetSongById(originalSongId, db);
        Song relatedSong = GetSongById(relatedSongId, db);
        song.RelatedSongs.Add(relatedSong);
        db.SaveChanges();
        return RedirectToAction("Index");
    }

您可以在 Song 表中有两个 FK，并使它们都为可为空，在这种情况下，您可以将歌曲引用到没有第三个表的歌曲，同时 EF 将完美运行。

歌曲（ID、标题、发行日期、专辑 ID 可为空、艺术家 ID 可为空）

专辑（ID、标题、发行日期）

艺术家（ID、名字、姓氏）

这里是对代码的更正 

Song(ID,Title,ReleaseDate)
Album(ID,Title,ReleaseDate)
Artist(ID,FirstName,LastName)
RelatedSong(ID,ParentID,SongID,ArtistID,AlbumID,TrackNumber)
[Table("RelatedSong")]
public partial class RelatedSong
{
    public Guid ID { get; set; }    
    public Guid ParentID { get; set; }    // this will be used for the Parent Song
    public Guid SongID { get; set; }    
    public Guid ArtistId {get; set;} // this will be used for artist foreign key
    public Guid AlbumId {get; set;} // this will be used for album foreign key
    public int? TrackNumber { get; set; }    
    public virtual Album Album { get; set; }    
    public virtual Artist Artist { get; set; }    
    public virtual Song Song { get; set; }
}  
 modelBuilder.Entity<Album>()
            .HasMany(e => e.RelatedSongs)
            .WithRequired(e => e.Album)
            .HasForeignKey(e => e.ParentID) // here you should use AlbumId and not ParentID
            .WillCascadeOnDelete(false);
modelBuilder.Entity<Artist>()
            .HasMany(e => e.RelatedSongs)
            .WithRequired(e => e.Artist)
            .HasForeignKey(e => e.ParentID) // here you should use ArtistId and not ParentID, which you already used it in the Album above
 modelBuilder.Entity<Song>()
            .HasMany(e => e.RelatedSongs)
            .WithRequired(e => e.Song)
            .HasForeignKey(e=>e.ParentID); // here you will use the parent id for the song relation
            .WillCascadeOnDelete(false);

基于此，您可以解决其他问题（如果有）

希望这对你有帮助

相关文章：