.net-5ef-core-5.0

How to update an existing object in a many to many relationship (.Net 5)


I have been using the .Net 5 and EF Core 5 for a small web app. Given EF Core 5 supports many - many out of the box there is no need for a joining table.

I've run into an issue when updating a object that already exists in the DB. For my app I have Athletes and Parents which have the many - many relationship.

public class Athlete
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string MiddleName { get; set; }
    public string Email { get; set; }
    public string ContactNumber { get; set; }
    public string Street { get; set; }
    public int Postcode { get; set; }
    public string City { get; set; }
    public StateEnum State { get; set; }
    public DateTime DateofBirth { get; set; }
    public DateTime DateSignedUp {get; set;}
    public virtual ICollection<Parent> Parents { get; set; }
}
public class Parent
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string MiddleName { get; set; }
    public string Email { get; set; }
    public string ContactNumber { get; set; }
    public string Street { get; set; }
    public int Postcode { get; set; }
    public string City { get; set; }
    public StateEnum State { get; set; }
    public DateTime DateofBirth { get; set; }
    public DateTime DateSignedUp {get; set;}
    public virtual ICollection<Athlete> Athletes { get; set; }
}

When I try to update the existing athlete that has a relation ship with two other parents I get an error:

Violation of PRIMARY KEY constraint 'PK_AthleteParent'. Cannot insert duplicate key in object 'dbo.AthleteParent'. The duplicate key value is (31, 1)

[HttpPost]
public async Task<ActionResult<Athlete>> PostAthlete(Athlete athlete)
{
     _context.Athletes.Update(athlete);
     await _context.SaveChangesAsync();
     return Ok(athlete));
}

From what I can tell when entity tries to update my Athlete it tries to insert new rows into the joining table even though the parents already exist in there. Is there a way to get entity to remove any records when the relationship is updated? Or is there a way to tell entity to take update the joining table to match the Athlete object that is passed in?


Solution

  • Given a simple example like this:

    public class Foo {
        Guid Id { get; set; }
        ICollection<Bar> Bars { get; set; }
    }
    
    public class Bar {
        Guid Id { get; set; }
        ICollection<Foo> Foos { get; set; }
    }
    

    You can call clear() on a tracked instance of Foo, and then re-add the Bar instances that you want assigned. I've found this is a nice way to avoid the constraint exception - much easier than manually trying to figure out what Bars have changed.

    var foo = context.Foos.Include(x => x.Bars).FirstOrDefault(x => x.Id == someGuid);
    foo.Bars.Clear();
    foo.Bars.Add(bar1);
    foo.Bars.Add(bar2);
    ...
    context.Update(foo);
    context.SaveChanges();