CODEWITHSUNDEEP
sqlentity-framework
Ungaro
Ungaro

Reputation: 532

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

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?

Upvotes: 2

Views: 5723

Answers (2)

Ungaro
Ungaro

Reputation: 532

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.

Upvotes: 0

Slauma
Slauma

Reputation: 177133

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);
    }
    //...
}

Upvotes: 3

Related Questions