EF Seed Many-to-Many Related Tables

Question

I have the following simplified many-to-many related models:

public class Singer
{
    [Key]
    public int Id { get; set; }

    public string Name { get; set; }

    public virtual ICollection<Song> Songs { get; set; }
}

public class Song
{
    [Key]
    public int Id { get; set; }

    public string Title { get; set; }

    public virtual ICollection<Singer> Singers { get; set; }
}

The DB tables created are below:

dbo.Singers
Id
Name

dbo.Songs
Id
Title

dbo.SingerSongs
Singer_Id
Song_Id

I used the following seed code to add items to already populated tables while preventing duplicates:

public static void SeedNewSingerAndSong(AppContext context)
{
    // Create new song
    Song newSong = new Song() { Title = "New Song" };

    context.Songs.AddOrUpdate(
        item => item.Title,
        newSong
    );

    context.SaveChanges();

    // Create new Singer
    Singer newSinger = new Singer() { Name = "New Singer" };

    context.Singers.AddOrUpdate(
        item => item.Name,
        newSinger
    );

    context.SaveChanges();
}

Recently, I updated the seed code to link "New Singer" to "Existing Song" and "New Song" as follows:

public static void SeedNewSingerAndSong(AppContext context)
{
    // Create new song
    Song newSong = new Song() { Title = "New Song" };

    context.Songs.AddOrUpdate(
        item => item.Title,
        newSong
    );

    context.SaveChanges();

    // Find existing songs
    Song foundExistingSong = context.Songs.Single(x => x.Title == "Existing Song");
    Song foundNewSong = context.Songs.Single(x => x.Title == "New Song");

    // Create new Singer
    Singer newSinger = new Singer() { Name = "New Singer" };

    // Assign songs to new Singer
    newSinger.Songs.Add(foundExistingSong);
    newSinger.Songs.Add(foundNewSong);

    context.Singers.AddOrUpdate(
        item => item.Name,
        newSinger
    );

    context.SaveChanges();
}

This doesn't work (no relationship gets added) probably because "New Singer" has already been added previously. If I manually delete "New Singer", the singer gets added together with the relationships when seeding. However, I don't want to delete items just so I could add relationships. How do I make this seed work?

Update: The problem with duplicate "New Song" when "New Song" already exists prior to seeding has been fixed with the updated code.

Shouldn't your identifier expression for Songs.AddOrUpdate refer to Title instead of Name? — pere57, Mar 13 '18 at 20:18
@pere57 I actually noticed that too and fixed it before I saw your comment but thanks anyway. Like I said this is a simplified representation of my actual models. — Titoy Koh, Mar 13 '18 at 20:21
@Mikkk I updated the question to include the underlying tables created. — Titoy Koh, Mar 13 '18 at 20:36
Are you sure the newSong is tracked after the call to AddOrUpdate(newSong)? Or to put it another way, is the duplicate created on the call to AddOrUpdate(newSong) or the call to AddOrUpdate(newSinger)? — pere57, Mar 13 '18 at 20:43
@pere57 I guess you're right "New Song" is duplicated on the AddOrUpdate(newSinger) I'll update the question. — Titoy Koh, Mar 13 '18 at 20:49
Is it possible to confirm that tracking is the problem? Can you test by e.g. retrieving newSong from the database before adding it to newSinger? — pere57, Mar 13 '18 at 20:59
[This](https://stackoverflow.com/questions/23067806/addorupdate-does-not-modify-children) and [this](https://stackoverflow.com/questions/8550756/how-to-seed-data-with-many-to-may-relations-in-entity-framework-migrations) might be relevant. One indicates that AddOrUpdate doesn't update relationships and the other explains how to avoid duplicates. — pere57, Mar 13 '18 at 21:17
@pere57 Thanks. I updated my code as you suggested. Duplicate songs is no issue anymore. However, the main problem of no relationships getting added still remains. — Titoy Koh, Mar 13 '18 at 21:19
@pere57 can you add your comment as answer instead so I can accept it or at least upvote? And maybe provide some code if necessary? — Titoy Koh, Mar 13 '18 at 21:25

score 3 · Answer 1 · answered Mar 14 '18 at 15:51

To give some more background information, AddOrUpdate does far less than its name suggests.

The method finds out if an entity exists. If it doesn't, it marks it as Added; if it does, it marks it as Modified. However, whereas marking as Added also marks nested entities as Added, marking as Modified only marks the entity itself as Modified -- and only its scalar properties, i.e. not its associations.
It has an annoying bug: if it finds an existing instance it stops tracking the instance that's visible in your code and starts tracking an internal (invisible) instance. Any subsequent changes to the instance in your code will be ignored.

In short: it's good for adding or updating isolated entities, not for related entities. I think the inevitable conclusion is: don't use AddOrUpdate to seed related entities.

(Personally, I would take this a step further and not use it, period).

Mikkk · Answer 2 · 2018-03-14T08:24:13.060

You need to add the Singer to the Songs as well. Both lists Singer.Songs & Song.Singers need to be maintained by your code.

public static void SeedNewSingerAndSong(AppContext context)
{
    ...

    // Assign songs to new Singer
    newSinger.Songs.Add(foundExistingSong);
    newSinger.Songs.Add(foundNewSong);

    // Assign Singer to Songs
    foundExistingSong.Singers.Add(newSinger);
    foundfoundNewSong.Singers.Add(newSinger);

    // The following duplicates "New Song" as explained in *Additional Info
    context.Singers.AddOrUpdate(
        item => item.Name,
        newSinger
    );

    context.SaveChanges();
}

@Mikkk your suggestion is much appreciated. Some code showing how to best improve my code would have been helpful. — Titoy Koh, Mar 14 '18 at 01:49

pere57 · Accepted Answer · 2018-03-13T22:08:57.603

For the duplicate issue:

newSong is not being tracked by Entity Framework and therefore isn't associated with any existing record in the database. When it is added to newSinger it is viewed as a new record and assigned a new id (primary key). Hence you appear to get duplicates of newSong.

To fix, you need to make newSong tracked so that EF associates it with its underlying record. In this case you can use:

context.Attach(newSong)

In general an entity retrieved by a query will be tracked. e.g.:

Song newSong = context.Songs.Single(x => x.Title == "New Song");

For the relationships issue:

This and this indicate that AddOrUpdate doesn't update relationships. One solution is to update the relationships separately while the other is to explicitly state the primary key values for all the added entities. I suspect that the latter would also remove the duplicate issue.

You could try something like:

var newSinger = context.Singers.Include("Songs").Single(x => x.Name == "New Singer");
newSinger.Songs.Clear();
newSinger.Songs = new List<Songs>{ foundNewSong, foundExistingSong };
context.SaveChanges();

To clarify, I need to AddOrUpdate() the new singer first without the related songs then add the songs later? But why use Clear()? — Titoy Koh, Mar 14 '18 at 02:45
Yes, AddOrUpdate on the new singer and then add the songs. And you are right, Clear isn't needed. I copied the example from some other code and modified it without checking properly. You could use Clear to save creating a new list and Add the items instead. — pere57, Mar 14 '18 at 02:50

EF Seed Many-to-Many Related Tables

3 Answers3