32

There is a many to many relationship between Artist and ArtistType. I can easily add artist ArtistType like below

foreach (var artistType in this._db.ArtistTypes
    .Where(artistType => vm.SelectedIds.Contains(artistType.ArtistTypeID)))
{
    artist.ArtistTypes.Add(artistType);
}

_db.ArtistDetails.Add(artist);
_db.SaveChanges();

This goes and updates the many to many association table with correct mapping. But when I try to remove any item from table I do not get any error but it does not remove it from the table? 

foreach (var artistType in this._db.ArtistTypes
    .Where(at => vm.SelectedIds.Contains(at.ArtistTypeID)))
{
    artistDetail.ArtistTypes.Remove(artistType);
}

this._db.Entry(artistDetail).State = EntityState.Modified;
this._db.SaveChanges();

What am I missing?

Janis S.
  • 2,526
  • 22
  • 32
akd
  • 6,538
  • 16
  • 70
  • 112

2 Answers2

57

Standard way is to load the artist including the current related types from the database and then remove the types with the selected Ids from the loaded types collection. Change tracking will recognize which types have been removed and write the correct DELETE statements to the join table:

var artist = this._db.Artists.Include(a => a.ArtistTypes)
    .SingleOrDefault(a => a.ArtistID == someArtistID);

if (artist != null)
{
    foreach (var artistType in artist.ArtistTypes
        .Where(at => vm.SelectedIds.Contains(at.ArtistTypeID)).ToList())
    {
        artist.ArtistTypes.Remove(artistType);
    }
    this._db.SaveChanges();        
}
Slauma
  • 175,098
  • 59
  • 401
  • 420
  • 1
    All of a sudden, I was able to see the big picture with the help of this answer. Thanks a ton! – dotNET May 23 '16 at 21:07
  • 4
    isn't this wasteful since you are fully loading all the child ArtistTypes when you really only need the ids. – craigrs84 Feb 07 '19 at 23:58
  • 3
    Is it possible to only load the Artist Types that you care about for the given artist? I have a situation to where there could be thousands of "artist types" that it would load when I only want to remove one association. – Brandon Feb 25 '19 at 15:40
  • If you can access the shadow table directly (between Artist and ArtistType), you could probably just delete the entry directly. – Jeppe Oct 16 '21 at 16:35
2

For removing only one field, I came up with this solution. It seems odd but in EF, most of the things are odd anyway because we try to tell EF the database ops in terms of OOP.

using (var db = new Context())
{
    //Create existing entities without fetch:
    var artist = new Artist() { ArtistID = _artistID };
    var type = new Type() { TypeID = _typeID };

    //Add one entity to other's list 
    //This is in memory, not connected.
    //So we do this because we try to tell EF that we want to remove this item
    //Without fetch, we should add it first in order to remove :)
    artist.ArtistTypes.Add(type);

    //Attach that entity which you add an item to its list:
    db.Artists.Attach(artist); 
    //It's now connected and recognized by EF as database operation

    //After attaching, remove that item from list and save db
    artist.ArtistTypes.Remove(type);
    db.SaveChanges();
}

That's it! With this solution, you are no longer fetching all entries of joined table ArtistTypes.

ninbit
  • 530
  • 6
  • 24