Entity Framework - Foreign Key Constraint

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

Question

I have 3 tables in a database:

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

I have a Related table so that song can be related to an Album or an Artist, or both:

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

So, using these four tables, I expected Entity Framework to generate models that would allow me to simply execute and run in my MVC project, but it fails upon saving due to a Foreign Key Constraint. If I set the ParentID = Album.ID, then it complains that Artist.ID is NULL, and vise-versa. Any suggestions? I am rewriting the front-end for an existing application so the database can't change. I need to know how the model needs to be constructed so that this works. It's either in the model or the modelBuilder (Fluent API).

Album Model:

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

Artist 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 Album:

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

Related Song:

[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:
Below is the controller code for the Create method.

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

UPDATE 2:
Maybe the database model is not correct or something? Not sure why this wouldn't be possible because it seems to me like this is the most efficient way to relate data to more than one "parent". I just read another post that says that it's not possible (but why would the database designer allow me to do this?)...

See: Multiple foreign keys to a single column

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

Accepted Answer

Your problem is here:

1.

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

There should be WithOptional(e => e.Album)

2.

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

There should be WithOptional(e => e.Album)

3.

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

There should be WithOptional(e => e.Artist)

4.

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

There should be WithOptional(e => e.Artist)

5.

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

There should be WithOptional(e => e.Song)

You wrote that them are not required, but in configuration you set them required. And you should set foreign key properties to nullable types.

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

and so on.

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

Popular Answer

here the correction of your code

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 fix the other problems if any

hope this will help you



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