Entity Framework Core 2.0 Many to Many Inserts before primary key is generated

Question

I'm trying to create an entity object that has many to many relationships with other entities. The relationships are indicated as follows.

public class Change {
    // Change Form Fields
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ChangeId { get; set; }
    public string ChangeTitle { get; set; }
    public string ChangeType { get; set; }
    public DateTime DateSubmitted { get; set; }
    public DateTime TargetDate { get; set; }

    //Many to Many Collections
    public virtual ICollection<Change_CriticalBankingApp> Change_CriticalBankingApps { get; set; } = new List<Change_CriticalBankingApp>();
    public virtual ICollection<Change_ImpactedBusiness> Change_ImpactedBusinesses { get; set; } = new List<Change_ImpactedBusiness>();
    public virtual ICollection<Change_ImpactedService> Change_ImpactedServices { get; set; } = new List<Change_ImpactedService>();
    public virtual ICollection<Change_TestStage> Change_TestStages { get; set; } = new List<Change_TestStage>();
    public virtual ICollection<Change_TypeOfChange> Change_TypeOfChanges { get; set; } = new List<Change_TypeOfChange>();

And the DbContext set up is as follows

public class ChangeContext : DbContext {
    public ChangeContext(DbContextOptions<ChangeContext> options) : base(options) {
        Database.Migrate();
    }

    public DbSet<Change> Change { get; set; }     
    public DbSet<TestStage> TestStage { get; set; }
    public DbSet<TypeOfChange> TypeOfChange { get; set; }
    public DbSet<CriticalBankingApp> CriticalBankingApp { get; set; }
    public DbSet<ImpactedBusiness> ImpactedBusiness { get; set; }
    public DbSet<ImpactedService> ImpactedService { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        modelBuilder.Entity<Change_CriticalBankingApp>().HasKey(t => new { t.ChangeId, t.CriticalBankingAppId });
        modelBuilder.Entity<Change_ImpactedBusiness>().HasKey(t => new { t.ChangeId, t.ImpactedBusinessId });
        modelBuilder.Entity<Change_ImpactedService>().HasKey(t => new { t.ChangeId, t.ImpactedServiceId });
        modelBuilder.Entity<Change_TestStage>().HasKey(t => new { t.ChangeId, t.TestStageId });
        modelBuilder.Entity<Change_TypeOfChange>().HasKey(t => new { t.ChangeId, t.TypeOfChangeId });
    }
}

Where I start running into problems is I'm not generating an Id using Entity Framework, the primary key is an identity in SQL Server 2012 and I get that back once the insert is completed, as opposed to using a GUID (which I've read pretty much everywhere is super frowned upon in the DBA world).

So what ends up happening is I either try and do the insert and it tries to insert the many to many relationships with changeId in the junction table being null (because it isn't generated yet) or when I try what I have below to do an insert and an update in one post method. It errors out because the ChangeId key value is already being tracked. Here is what I'm attempting below.

Controller method

    public IActionResult CreateChange([FromBody] ChangeModel change) {
        if (change == null) {
            return BadRequest();
        }

        //Remove many to many from Change to insert without them (as this can't be done until primary key is generated.
        List<Change_CriticalBankingAppModel> criticalApps = new List<Change_CriticalBankingAppModel>();
        criticalApps.AddRange(change.Change_CriticalBankingApps);
        List<Change_ImpactedBusinessModel> impactedBusinesses = new List<Change_ImpactedBusinessModel>();
        impactedBusinesses.AddRange(change.Change_ImpactedBusinesses);
        List<Change_ImpactedServiceModel> impactedServices = new List<Change_ImpactedServiceModel>();
        impactedServices.AddRange(change.Change_ImpactedServices);
        List<Change_TestStageModel> testStages = new List<Change_TestStageModel>();
        testStages.AddRange(change.Change_TestStages);
        List<Change_TypeOfChangeModel> changeTypes = new List<Change_TypeOfChangeModel>();
        changeTypes.AddRange(change.Change_TypeOfChanges);

        change.Change_CriticalBankingApps.Clear();
        change.Change_ImpactedBusinesses.Clear();
        change.Change_ImpactedServices.Clear();
        change.Change_TestStages.Clear();
        change.Change_TypeOfChanges.Clear();

        //Map Change model to change entity for inserting
        var changeEntity = Mapper.Map<Change>(change);
        _changeRepository.AddChange(changeEntity);

        if (!_changeRepository.Save()) {
            throw new Exception("Creating change failed on save.");
        }

        var changetoReturn = Mapper.Map<ChangeModel>(changeEntity);

        //Iterate through Many to many Lists to add generated changeId
        foreach (var criticalApp in criticalApps) {
            criticalApp.ChangeId = changetoReturn.ChangeId;
        }
        foreach (var impactedBusiness in impactedBusinesses) {
            impactedBusiness.ChangeId = changetoReturn.ChangeId;
        }
        foreach (var impactedService in impactedServices) {
            impactedService.ChangeId = changetoReturn.ChangeId;
        }
        foreach (var testStage in testStages) {
            testStage.ChangeId = changetoReturn.ChangeId;
        }
        foreach (var changeType in changeTypes) {
            changeType.ChangeId = changetoReturn.ChangeId;
        }

        //Add many to many lists back to change to update
        changetoReturn.Change_CriticalBankingApps = criticalApps;
        changetoReturn.Change_ImpactedBusinesses = impactedBusinesses;
        changetoReturn.Change_ImpactedServices = impactedServices;
        changetoReturn.Change_TestStages = testStages;
        changetoReturn.Change_TypeOfChanges = changeTypes;

        changeEntity = Mapper.Map<Change>(changetoReturn);

        _changeRepository.UpdateChange(changeEntity);
        if (!_changeRepository.Save()) {
            throw new Exception("Updating change with many to many relationships failed on save.");
        }

        changetoReturn = Mapper.Map<ChangeModel>(changeEntity);

        return CreatedAtRoute("GetChange",
            new { changeId = changetoReturn.ChangeId },
            changetoReturn);
    }

Relevant Repository methods

public Change GetChange(int changeId) {
    return _context.Change.FirstOrDefault(c => c.ChangeId == changeId);
}
public void AddChange(Change change) {
    _context.Change.Add(change);
}
public void UpdateChange(Change change) {
    _context.Change.Update(change);
}
public bool ChangeExists(int changeId) {
    return _context.Change.Any(c => c.ChangeId == changeId);
}

I encounter this error on the update attempt.

I understand that if I were to have entity framework generate the guid instead of having the database generate the identity int that I would have a much easier time with this but a requirement for this project is to not use Guid's.

Any help on how to successfully process this would be greatly appreciated.

EDIT: In case it helps, here is the http post I'm using with postman.

{
    "changeTitle": "Test",
    "changeType": "Test",
    "dateSubmitted": "02/12/2018",
    "targetDate": "02/12/2018",
    "change_CriticalBankingApps": [
        {
            "criticalBankingAppId" : 1,
            "description" : "Very critical"
        },
        {
            "criticalBankingAppId" : 2,
            "description" : "Moderately critical"
        }
        ],
    "change_impactedBusinesses": [
        {
            "ImpactedBusinessId" : 1
        },
        {
            "ImpactedBusinessId" : 2
        }
        ]
}

Answer 1

The error you are getting has nothing to do with the guid vs db identity.

You are getting it because you are:

1. Fetching an entity from the database
2. Creating new entity (not tracked) from within your controller (the mapper does this)
3. Try to update the entity that is not tracked by entity framework

The update will try to add the entity to the EF repository, but will fail because it already contains an entity with the given ID.

If you plan to make changes to an entity, you need to make sure entity framework tracks the entity prior to calling the update method.

If EF does not track your entity, it does not know which fields have been updated (if any).

---

Edit:

If you want to get rid of the error, you could detach your original entity. Make sure you do it prior to mapping the changetoReturn back into your changeEntity.

dbContext.Entry(entity).State = EntityState.Detached;

But since your new entity won't be tracked, I don't think anything will be updated (EF does not know what has been changed).

---

Edit 2:

Also take a look at this to get your changes back into your original entity.

Change this:

changeEntity = Mapper.Map<Change>(changetoReturn);

Into this:

Mapper.Map(changetoReturn, changeEntity);

Using Automapper to update an existing Entity POCO

Answer 2

add new entities via joint table...that way, entities are tracked both in the joint table and their individual respective tables

Answer 3

Ok, whether this is an elegant solution is up for debate, but I was able to detach the entity state from changeEntity after doing the initial insert as follows

_changeRepository.AddChange(changeEntity);
_changecontext.Entry(changeEntity).State = EntityState.Detached;

Then after reattaching all of the many to many lists back to changeToReturn, I created a new Change entity and added that entity state, and updated on that as follows.

var newChangeEntity = Mapper.Map<Change>(changeToReturn);
_changecontext.Entry(newChangeEntity).State = EntityState.Added;
_changeRepository.UpdateChange(newChangeEntity);

Then I returned this mapped back to a view model.

It seems hacky and perhaps through a deeper understanding of entity framework I'll discover a much better way of going about this but this works for now.