Entity Framework - Foreign Key Constraint

asp.net-mvc c# database entity-framework-6

Question

In a database, I have three tables:

Song (ID, Title, ReleaseDate)
Album (ID, Title, ReleaseDate)
Artist (ID, FirstName, LastName)

A song may be associated to an album, an artist, or both thanks to my Related table:

(ID, ParentID, SongID, TrackNumber) RelatedSong (Foreign Key on Album.ID and Artist.ID for ParentID and obviously Song.ID for SongID)

I therefore anticipated that Entity Framework would provide models based on these four tables that I could then easily execute and run in my MVC project, however it fails to save due to a Foreign Key Constraint. It warns that Artist.ID is NULL if I set the ParentID to equal Album.ID, and vice versa. Any recommendations? To prevent the database from changing, I am redoing the front-end for an existing application. To make this work, I need to know how the model should be built. Either the model or the modelBuilder contains it (Fluent API).

album design

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

Designer Model:

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

Related Music:

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

Song Related To:

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

Song:

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

DbContext:

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

UPDATE:
The controller code for the Create method is shown below.

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

2ND UPDATE
Maybe something is wrong with the database model? I'm not sure why this wouldn't be doable, as it seems like the most effective approach to link data to several "parents" in my opinion. It's not feasible, according to another article I recently read (but why would the database designer permit this?).

look at more than one foreign key per column

1
2
5/23/2017 11:58:45 AM

Accepted Answer

Your issue is this:

1.

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

There ought to beWithOptional(e => e.Album)

2.

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

There ought to beWithOptional(e => e.Album)

3.

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

There ought to beWithOptional(e => e.Artist)

4.

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

There ought to beWithOptional(e => e.Artist)

5.

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

There ought to beWithOptional(e => e.Song)

Although you stated that they are not necessary, you made them necessary in setup. Additionally, you want to use nullable types for foreign key properties.

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

so forth.

1
7/5/2019 11:30:40 AM

Popular Answer

this is where your code has been fixed.

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

Based on this, you can address any more issues.

I hope this will be useful.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow