Entity Framework - Foreign Key Constraint

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

Are you using Code First? can you paste the code of your models? — Claies, Nov 24 '14 at 22:11
I'm using Visual Studio to add ADO.NET Entity Data Model, connect directly to my database, and have it generate the models for me. Then, I add a Controller based on each model, and voila, all the code is written for me, but it doesn't work :-(. I can post some of the code I guess. — clockwiseq, Nov 24 '14 at 22:21
To clarify, I'm using ADO.NET Entity Data Model, and then choosing the "Code First from database" option. Also, the error is the following: {"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_RelatedSong_Album\". The conflict occurred in database \"MusicDatabase\", table \"dbo.Album\", column 'ID'.\r\nThe statement has been terminated."} — clockwiseq, Nov 24 '14 at 22:38
so you are using Database First. Are the models and the DbContext which you posted the automatic code or code which you customized? — Claies, Nov 24 '14 at 22:44
that code looks fine, what does the insert code you are using look like? — Claies, Nov 24 '14 at 23:20

score 1 · Accepted Answer · edited Jul 05 '19 at 11:30

1

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.

edited Jul 05 '19 at 11:30

Kitesaint1309

59
2
8

answered Nov 25 '14 at 14:33

Kirill Bestemyanov

11,946
2
24
38

Well, thanks for the reply, but that still renders an error (even before saving the RelatedSong object. The Create view won't render due to the changes in the model since changing those to WithOptional. WebApplication11.Models.Artist_RelatedAlbums: : Multiplicity conflicts with the referential constraint in Role 'Artist_RelatedAlbums_Source' in relationship 'Artist_RelatedAlbums'. Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'. – clockwiseq Nov 25 '14 at 15:32
"And you should set foreign key properties to nullable types." – Kirill Bestemyanov Nov 25 '14 at 16:15
How would I go about doing that? In the model? – clockwiseq Nov 25 '14 at 20:35
Ok, now that I know what you meant, I tried that, and it doesn't work in my case. The reason is, you have listed public Guid? ParentID and public Guid? AlbumID which the AlbumID is the ParentID and I wanted to use the single property instead of 3. The issue is that using the foreign key attribute [ForeignKey("Album")], it won't let me specify 2 foreign keys. Unless I find a way around that, I am stuck. – clockwiseq Nov 26 '14 at 16:46
You should not use [ForeignKey("Album")] attribute. This code modelBuilder.Entity() .HasMany(e => e.RelatedSongs) .WithRequired(e => e.Artist) .HasForeignKey(e => e.ParentID) says that ParentId is foreign key that links related songs and artist – Kirill Bestemyanov Nov 26 '14 at 21:22

Kaido · Answer 2 · 2014-11-25T14:36:30.140

0

You are trying to insert a "Related Song" without an "Album" which is mandatory in

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

You could use something like this if you had the relation / types set up slightly differently

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

edited Nov 25 '14 at 14:36

answered Nov 25 '14 at 14:30

Kaido

3,383
24
34

I do not believe this is correct, as the problem is the relationships between two foreign tables. As answered by Kirill above, his solution does not work either. I believe my problem exists in the database where the design is not "correct". If I didn't have Guids as the identifier, there would be no way to uniquely identify which is the correct ParentID (which table it corresponds to). I believe I need to add another column to the RelatedSong table. – clockwiseq Nov 25 '14 at 15:36

score 0 · Answer 3 · answered Nov 26 '14 at 06:45

You can have two FK in the Song table and make both as nullable, in this case you can refer a song to both without a third table, and at the same time EF would work perfectly.

Song (ID, Title, ReleaseDate, AlbumID nullable, ArtistID nullable)

Album (ID, Title, ReleaseDate)

Artist (ID, FirstName, LastName)

Monah · Answer 4 · 2014-11-26T07:32:50.457

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

Entity Framework - Foreign Key Constraint

4 Answers4