Entity Framework - Many To Many Relationship Inserting Duplicate To Parent Table

Question

I'm trying to do a simple insert with a many-to-many relationship using Entity Framework 5.

I have two POCO classes as follows.

public class Category
{
    public virtual string Title { get; set; }
    public virtual DateTime EntryDate { get; set; }
    public virtual DateTime LastUpdated { get; set; }
    public virtual ICollection<Article> Articles { get; set; }
}

public class Article
{
    public virtual string Title { get; set; }
    public virtual string Content { get; set; }
    public virtual DateTime EntryDate { get; set; }
    public virtual DateTime LastUpdated { get; set; }
    public virtual ICollection<Category> Categories { get; set; }
}

And the following fluent api mapping code...

public class CategoryMap : EntityTypeConfiguration<Category>
{
    public CategoryMap()
    {
        this.ToTable("Categories");
        this.HasKey(x => x.ID);

        this.Property(x => x.Title).IsRequired().HasMaxLength(255);
    }
}

public class ArticleMap : EntityTypeConfiguration<Article>
{
    public ArticleMap()
    {
        this.ToTable("Articles");
        this.HasKey(x => x.ID);

        this.HasMany(x => x.Categories)
            .WithMany(x => x.Articles)
            .Map(x =>
            {
                x.ToTable("MapArticleCats");
                x.MapLeftKey("CategoryID");
                x.MapRightKey("ArticleID");
            });

        this.Property(x => x.Title).IsRequired().HasMaxLength(255);
        this.Property(x => x.Content).IsRequired().HasMaxLength(4000);
    }
}

The entity framework will then generate both Category and Article tables along with a third mapping table of which details were specified in the ArticleMap code (MapArticleCats), which looks like the following in SQL Server.

ArticleID - int
CategoryID -int

The following code (give or take a few lines) adds the Categories to the Article in my controller.

IEnumerable<Category> GetCats = CategoryRepository.GetAll();

//DO SOME CODE TO FIGURE WHICH CATEGORIES I NEED.
IEnumerable<Category> Categories = InferCategoriesFromPostedData(Model.Categories, GetCats);

foreach (Category c in Categories)
{
    Article.Categories.Add(c);
}

This seems to create some strange behavior on insert. It will insert a new category into the category table (DUPLICATE) and also insert the newly created CategoryID (instead of the original id) and the correct ArticleID to the MapArticleCats table.

Can anyone see where I've gone wrong?

score 4 · Accepted Answer · edited May 23 '17 at 11:59

OK what I should've done before submitting this article (and previous ones...massively guilty) was to search StackOverflow and bit more thoroughly.

I found the answer to my problem here

What was happening was nothing to do with the above code at all. It was because I was essentially injecting TWO data contexts into my controller (one in the article repository and the other in the category repository) and therefore creating duplicate records.

==================================================================================
IMPORTANT EDIT
==================================================================================

Just to elaborate on my answer...

Even after doing the above fix this still didn't solve my original issue of not being able to update the navigation properties (Categories). The fix for this is found below...

I was model binding the entity (Article) using AsNoTracking() and this is OK if you want to just update the main entity itself but you can't do anything with the navigation properties. e.g. adding/removing categories from the article in my case.

This left me in a bit of a pickle as I'd tried a similar method by detaching the entity from the context, but both ways ultimately left me with the same problem of not being able to manipulate the navigation properties.

The way to fix this was to simply just to model bind/select the entity as normal. No AsNoTracking(), no Detaching. and when attaching the entity back to the context use the following code...

    public bool Attach(T entity)
    {
        T original = _dbSet.Find(entity.ID);
        _dbContext.Entry(original).CurrentValues.SetValues(entity);
        _dbContext.Entry(original).State = EntityState.Modified;

        return this.Commit();
    }

This way you can select it out without doing any AsNoTracking/Detaching rubbish and it will update just fine. This is OK for me, some people might find the extra trip to the db a bit offensive, but for the sake of all that grief that suits me just fine.

score 0 · Answer 2 · edited Jan 05 '18 at 03:49

0

In your dbcontext CaveChanges call:

ChangeTracker.DetectChanges()

If ChangeTracker.HasChanges Then
    ChangeTracker.DetectChanges()
End If

This works for me.

edited Jan 05 '18 at 03:49

Robert Columbia

6,313
15
32
40

answered Nov 21 '13 at 13:41

cris

1

score 0 · Answer 3 · answered Jan 15 '14 at 10:50

Problem for me was that I was trying to set new instance to the navigation property.

I was doing:

IList<Category> categories = _categoryService.GetSomeCategories();
article.Categories = categories;
_articleService.UpdateArticle(article);

Instead, you should hold on to existing instance of ICollection like this:

IList<Category> categories = _categoryService.GetSomeCategories();
article.Categories.Clear();
foreach (var category in categories) {
  article.Categories.Add(category);
}
_articleService.UpdateArticle(article);

Duplicate PRIMARY KEY error is gone.

Entity Framework - Many To Many Relationship Inserting Duplicate To Parent Table

3 Answers3