Entity Framework 6 Many-to-many wants to insert duplicate rows

Question

It shouldn't be this hard! I am about to give up on EF...

My model has weekly newspaper Editions. Each Edition can have many Classifieds. Each Classified can appear in one or more Editions. My models:

public class Classifieds
{ 
  [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int ClassifiedId { get; set; }
  ...
  public virtual ICollection<EditionModel> Editions { get; set; }
}

public class EditionModel
{
  [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
  public int EditionId { get; set; } // This is YYYYWW, WW = week number
  public Date PublicationDate { get; set; }
  public virtual ICollection<Classifieds> Classifieds { get; set; }
}

The OnModelCreating override:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   modelBuilder.Entity<EditionModel>()
     .Property(z => z.EditionId)
     .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

   modelBuilder.Entity<EditionModel>()
     .HasMany(c => c.Classifieds)
     .WithMany(d => d.Editions)
     .Map(x =>
     {
       x.MapLeftKey("EditionId");
       x.MapRightKey("ClassifiedId");
       x.ToTable("EditionModelClassifieds");
     });
   base.OnModelCreating(modelBuilder);
}

The Create Classifieds Action code (HTTP put):

public async Task<ActionResult> Create(Classifieds classifieds, int[] EditionList)
{
  var allPubs = PopulateEditionList(); // Current and next 12 editions
  db.Set<Classifieds>().Attach(classifieds);
  db.Entry(classifieds).Collection(x => x.Editions).Load();
  foreach (var p in EditionList)
  {
      var anEd = (from x in allPubs where x.EditionId == p select x).Single();
      classifieds.Editions.Add(anEd);
  }
  ...
  if (ModelState.IsValid)
  {
     var ads = db.Classifieds.Add(classifieds);
     await db.SaveChangesAsync().ConfigureAwait(false);
     return View("Receipt", classifieds);
  }
  ...
}

On submit of a new Classifieds, I get

Violation of PRIMARY KEY constraint 'PK_dbo.EditionModels'.  
Cannot insert duplicate key in object 'dbo.EditionModels'.

Why does EF insist on wanting insert duplicate rows in EditionModels instead of just linking the junction table row to an existing row therein? How do I get around this?

score 3 · Answer 1 · answered Nov 23 '13 at 13:37

Apparently PopulateEditionList() doesn't attach the editions to the context. Add this line in the foreach loop:

db.Set<EditionModel>().Attach(anEd);

Possibly you can simplify the whole procedure. I'm not sure why you load the editions into memory. You could just create a "stub entity" based on the key which is enough to create the relationship. Also attaching classifieds to the context seems redundant because you want to add it as a new entity to the database. Trying to load related editions from the database for a parent entity that doesn't exist yet in the DB seems redundant as well. The result can only be an empty collection.

So, the following simpler version might work as well:

public async Task<ActionResult> Create(Classifieds classifieds, int[] EditionList)
{
    classifieds.Editions = new List<EditionModel>();
    foreach (var p in EditionList)
    {
        var anEd = new EditionModel { EditionId = p };
        db.Set<EditionModel>().Attach(anEd);
        classifieds.Editions.Add(anEd);
    }
    //...
    if (ModelState.IsValid)
    {
        var ads = db.Classifieds.Add(classifieds);
        await db.SaveChangesAsync().ConfigureAwait(false);
        return View("Receipt", classifieds);
    }
    //...
}

Thank you for replying, Slauma. The solution you proposed did not work, MSSQL tried to add a duplicate row into the EditionModels table. This was my original problem... Here is the output: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.EditionModelClassifieds_dbo.EditionId". The conflict occurred in database "ipdb", table "dbo.EditionModels", column 'EditionId'. The statement has been terminated. — Ungaro, Nov 24 '13 at 03:16

score 0 · Accepted Answer · answered Nov 24 '13 at 14:47

The problem was that EditionModel was not part of the context. Thank you, Slauma for pointing me in the right direction. The corrected Create Action is:

...
classifieds.Editions = new List<EditionModel>();
foreach (var p in EditionList)
{
   var ed = await db.EditionModel.FindAsync(p);
   if (ed == null)
      ed = new EditionModel { EditionId = p, PublicationDate = (from q in allPubs where                       q.EditionId == p select q).Single().PublicationDate };
   InsertOrUpdate<EditionModel>(ed);
   classifieds.Editions.Add(ed);
}
...

The rest of the code is as above.

Entity Framework 6 Many-to-many wants to insert duplicate rows

2 Answers2

Linked