Why does Entity Framework add a related record even if it already exists in the database?

Question

Why does Entity Framework add a related record even if it already exists in the database? It should just update the junction table in this case)?

I am working with Entity Framework 6, I have a many-to-many relationship between Directors and Movies. I want to post a new movie using a Dto called MovieUpdateDto. Movie class has a property of type ICollection<Directors> Directors.

The json that represents the Dto looks like this:

{
    "Name": "new movie",
    "Description": "new movie Description",
    "Price": 1000.0,
    "Directors": [{"Id": 11, "Name": "Coco"}, {"Id": "12", "Name": "Jambo"}]
}

The problem with the Post is that it doesn't work as expected when inserting a movie with one or more existing directors (that have already been added to the db). In such cases, those directors are readded to the db.

As a side note, EF implicitly adds the directors as new records in the Directors table when I add the movies dto with directors information, and also implicitly takes care of the junction table MoviesDirectors.

Here is my first attempt:

// POST: api/movies
public void Post([FromBody] MovieUpdateDto movie) 
{
    var bookToInsert = Mapper.Map<Movie>(movie);

    foreach (var director in movieToInsert.Directors)
    {
        var existingDirector = _unitOfWork.Directors.Find(d => d.Id == director.Id);
        if (existingDirector.Any())
        {
            // don't add director again to the database, but do add (directorId, bookId) in the MoviesDirectors junction table. 
            // How do I reference the junction table though? Should this work somehow without referencing the juntion table? 
        }
    }
    _unitOfWork.Movies.Add(movieToInsert);
    _unitOfWork.Complete();
}

PS:

The junction table called MoviesDirectors is autogenerated by EF when setting the right configurations. More precisely, in the MovieConfiguration class, I have:

HasMany(c => c.Directors)
    .WithMany(t => t.Movies)
    .Map(m =>
    {
        m.ToTable("MoviesDirectors"); // a way to overwrite the name that EF gives this table
        m.MapLeftKey("MovieId");
        m.MapRightKey("DirectorId");
    });

which automatically creates MoviesDirectors.

I'm unsure about this, but isn't the problem related to the your Director model that defines `Id` as the primary key, and the MapRightKey defines `DirectorId`, shouldn't the Director class have a `DirectorId` property as the primary key for this to work properly - so that on inserting EF understands it shouldn't add a new Director row to the Directors table? — Dennis VW, Jul 04 '20 at 12:50
All the [documentation](https://learn.microsoft.com/en-us/ef/ef6/modeling/code-first/fluent/relationships) on the Map function define the primary keys of the classes as `Class`+`Id`. Never just `Id`. — Dennis VW, Jul 04 '20 at 12:53
Dennis, the Director class has an Id property; the MoviesDirectors class doesn't exist in code in C#. But the MoviesDirectors table has both a DirectorId and a MovieId (this is the junction table). When I add a new movie with directors info (see the json I posted in the OPP) automatically and implicitly records are added to both Directors table and the junction table (without writing any specific code). The problem is about the directors being added to the database without checking if they already exist. You may have a mixed case: some new directors, some existing ones, in the same json. — Sami, Jul 04 '20 at 12:55
Well, in the Map function I am using MovieId and DirectorId, not just Id... (but this is unrelated to the actual issue, right?) — Sami, Jul 04 '20 at 12:56
Do you use code first? Or are you coding against an existing database. — Dennis VW, Jul 04 '20 at 13:07
It would probably be easier if I could access the FK. If I had a FK property in Movies called DirectorId. But that works for 1 to Many relationships (1 director - many movies). I have instead a Many 2 Many between the two entities ... — Sami, Jul 04 '20 at 13:15
Then you should have used `DirectorId` and `MovieId` as the names of the primary keys in both Director and Movie classes. — Dennis VW, Jul 04 '20 at 13:27
What I think is happening, in this case the right foreign key `DirectorId` points to the parent entity of the navigation property specified in the WithMany call. When you let EF Code-First create the database and join table based on your Fluent mapping, it expects that `DirectorId` is the **name** of the primary key column in the Directors table. But since you called it `Director.Id` EF did not set up your relationship correctly and does not know that Director with ID 11 already exists. — Dennis VW, Jul 04 '20 at 13:27
Anyone correct me if I am wrong. But it seems like the documentation supports my thoughts. — Dennis VW, Jul 04 '20 at 13:28

score 0 · Answer 1 · answered Jul 04 '20 at 12:43

0

For your second attempt you can do just foreach (var director in movieToInsert.Directors.ToList()), but I would recommend just to check the director.Id to be not a default value (0 or Guid.Empty) and if it is not - using Attach:

foreach (var director in movieToInsert.Directors)
{
    if (director.Id > 0)
    {
       _unitOfWork.Directors.Attach(director);
    }
}

answered Jul 04 '20 at 12:43

Guru Stron

102,774
10
95
132

Thank you, Guru Stron. I actually don't have the Attach function in the implementation of unit of work repo with EF, I'll see if I can add it... and use it as you propose. (I remember having seen Attached in action in EF without unit of work). I have actually started from the idea that a repository as a collection of domain objects in memory should not have Save, Update methods... Attach? – Sami Jul 04 '20 at 12:58
@Sami Oh. Assumed that `Directors` is a `DbSet`. – Guru Stron Jul 04 '20 at 13:00
Sorry, I didn't make that one clear enough, for fear of the OP turning too lengthy – Sami Jul 04 '20 at 13:04
@Sami then as a quick fix you can try using your second attempt with `ToList` added as I wrote in the answer. – Guru Stron Jul 04 '20 at 13:09
And yet another reason to not hide EF behind your own UoW or Repositories. Entity framework is already an implementation of *both* patterns – pinkfloydx33 Jul 04 '20 at 15:31
Thank you. Well this is the only problem I've had with this approach and if I find a good way around it, it might optimistically speaking be the last (major one). Now even for the beauty of the exercise and having come so far it would be great to solve this.. couldn't get it to work yet. – Sami Jul 04 '20 at 15:40

Why does Entity Framework add a related record even if it already exists in the database?

1 Answers1