Deleting selected relations in many-to-many relationship in EF?
In SQL, I have:
[Bands] 1 ---- * [BandsGenres] * ---- 1 [Genres]
In my EF layer, it translates to:
[Bands] * ---- * [Genres]
(BandsGenres is BandId and GenreId)
My problem is, I can't seem to figure out how to delete just the selected relationships I need. Basically, I'm trying to update a list of type Genre for a band. A list of Guids is passed in and I'm able to get those using joins. However, the .Remove() method isn't removing the old genres and adding the new ones. I tried using .Attach(g) and .DeleteObject(g) but that removed the actual Genre (from Genres) instead of just the relationship.
public void UpdateBandGenres(Guid bandId, IEnumerable<Guid> genres)
{
using (var ctx = new OpenGroovesEntities())
{
开发者_StackOverflow社区 var newGenres = from g in ctx.Genres
join t in genres on g.GenreId equals t
select g;
//var bandGenres = ctx.Genres.Include("Bands").Where(g => g.Bands.Any(b => b.BandId == bandId));
var bandGenres = ctx.Bands.SingleOrDefault(b => b.BandId == bandId).Genres;
bandGenres.ToList().ForEach(g =>
{
bandGenres.Remove(g);
});
newGenres.ToList().ForEach(g => bandGenres.Add(g));
ctx.SaveChanges();
}
}
How can I delete/add or update my list of genre relationships for a band, given a list of genre IDs? Thanks.
If I understand your problem correcly, the genres
collection contains all the Genre
objects that should be in the Band
genre list as a result of running the UpdateBandGenres
method (and not just the list to add or list to delete). In that case the simplest would be removing all the genres from the collection and adding all the genres with the Guid
s from your genres
collection.
First of all, you don't need joins to grab your newGenres
:
var newGenres = ctx.Genres.Where(g => genres.Contains(g.GenreId));
Secondly, you need to fetch the Band
object, since modifying its Genres
collection will tell EF to modify the BandGenres
table in SQL:
Band band = ctx.Bands.SingleOrDefault(b => b.BandId == bandId);
After that you can clear the band.Genres
collection and add the newGenres
on top. As a result you code will look like:
public void UpdateBandGenres(Guid bandId, IEnumerable<Guid> newGenreIds)
{
using (var ctx = new OpenGroovesEntities())
{
List<Genre> newGenres = ctx.
Genres.
Where(g => newGenreIds.Contains(g.GenreId)).
ToList();
Band band = ctx.Bands.Single(b => b.BandId == bandId);
band.Genres.Clear();
newGenres.ForEach(band.Genres.Add);
ctx.SaveChanges();
}
}
Btw, I'd also recommend being consistent with naming your variables - e.g. IEnumerable<Guid> genres
might be a bit misleading, since it's actually a collection of Guid
s, and not a collection of Genre
objects. Therefore I named it newGenreIds
to be consistent with your Guid bandId
variable name.
精彩评论