Copy/clone of Entity Framework with many-to-many relationship

dbcontext entity-framework entity-framework-6

Question

I am trying to clone entities in our context. Everything works fine except for a many-to-many table.

Here is the hierarchy:

  • Plan
    • Goals
      • Fundings
    • Accounts
      • ContributionStreams
        • Fundings

The Fundings table links ContributionStreams to Goals. One flaw is the fundings rows have a FundingId, ContributionStreamId, and a GoalId. I didn't know about using the later two as the primary key.

I am trying to copy a Plan along with all the lower level entities. As per this method, I detach the Plan and update the Ids, but the new Many-to-Many entries in the Fundings table point to the new ContributionStreams and the old Goals

Here is the code

var detachedPlan = db.Plans
  .Include("Goals")
  .Include("Accounts")
  .Include("Accounts.ContributionStreams")
  .Include("Accounts.ContributionStreams.Fundings").AsNoTracking()
  .FirstOrDefault(p => p.PlanId == originalPlan.PlanId);

foreach (var goal in detachedPlan.Goals.ToList())
{
    Guid newGoalId = Guid.NewGuid();
    goal.GoalId = newGoalId;
}

foreach (var account in detachedPlan.Accounts.ToList())
{
    account.AccountId = Guid.NewGuid();
    foreach (var contributionStream in account.ContributionStreams.ToList())
    {
        Guid newContributionStreamId = Guid.NewGuid();
        var fundingList = contributionStream.Fundings.ToList();
        contributionStream.ContributionStreamId = newContributionStreamId;
        foreach (Funding funding in fundingList)
        {
            funding.ContributionStreamId = newContributionStreamId;
            funding.FundingId = Guid.NewGuid();
        }
    }
}
detachedPlan.PlanId = Guid.NewGuid();
detachedPlan.Name += " copy";
db.Plans.Add(detachedPlan);

You can see that I have to create a reference from the Funding to the ContributionStream in addition to replacing the primary key funding.ContributionStreamId = newContributionStreamId;. Doing a similar assignment with the goals creates unwanted copies of the entities and they point to the old goals, but not the new contributionStream.

The Fundings class was auto generated from the database:

public partial class Funding
{
    public System.Guid FundingId { get; set; }
    public System.Guid ContributionStreamId { get; set; }
    public System.Guid GoalId { get; set; }
    public double Weight { get; set; }
    public string Notes { get; set; }

    public virtual ContributionStream ContributionStream { get; set; }
    public virtual Goal Goal { get; set; }
}

The SQL for Fundings is

CREATE TABLE [dbo].[Fundings] (
  [FundingId]            UNIQUEIDENTIFIER NOT NULL,
  [ContributionStreamId] UNIQUEIDENTIFIER NOT NULL,
  [GoalId]               UNIQUEIDENTIFIER NOT NULL,
  [Weight] FLOAT NOT NULL DEFAULT 1, 
  [Notes] NVARCHAR(4000) NULL, 
  PRIMARY KEY CLUSTERED ([FundingId] ASC),
  CONSTRAINT [FK_Fundings_ContributionStreams] FOREIGN KEY ([ContributionStreamId]) REFERENCES [dbo].[ContributionStreams] ([ContributionStreamId]) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT [FK_Fundings_Goals] FOREIGN KEY ([GoalId]) REFERENCES [dbo].[Goals] ([GoalId])
);
1
0
8/18/2018 2:45:14 PM

Popular Answer

Well, I decided to go with the "brute force" solution, below. I still don't understand the inner workings of why I can't update the funding.GoalId in the loop that creates the new goal.GoalId. Ignorance isn't bliss.

Here's the solution

// Keep track of the mapping of old goals to new goals
Dictionary<Guid, Guid> oldGoalId = new Dictionary<Guid, Guid>();

var detachedPlan = db.Plans
  .Include("Goals")
  .Include("Accounts")
  .Include("Accounts.ContributionStreams")
  .Include("Accounts.ContributionStreams.Fundings").AsNoTracking()
  .FirstOrDefault(p => p.PlanId == originalPlan.PlanId);

foreach (var goal in detachedPlan.Goals.ToList())
{
  Guid newGoalId = Guid.NewGuid();
  oldGoalId.Add(goal.GoalId, newGoalId); // record old and new goalId for the new goal

  // BTW, this seems like the right solution, but it's not.
  // This loop creates unwanted additional copies of the funding in the old Plan
  // foreach (Funding funding in goal.Fundings.ToList())
  //    funding.GoalId = newGoalId;
  goal.GoalId = newGoalId;
}

foreach (var account in detachedPlan.Accounts.ToList())
{
  account.AccountId = Guid.NewGuid();
  foreach (var contributionStream in account.ContributionStreams.ToList())
  {
    Guid newContributionStreamId = Guid.NewGuid();
    var fundingList = contributionStream.Fundings.ToList();
    contributionStream.ContributionStreamId = newContributionStreamId;
    foreach (Funding funding in fundingList)
    {
      funding.ContributionStreamId = newContributionStreamId;
      // update the GoalId here.
      // Not sure why it can't be updated in the commented out loop above
      funding.GoalId = oldGoalId[funding.GoalId];
      funding.FundingId = Guid.NewGuid();
    }
  }
}
detachedPlan.PlanId = Guid.NewGuid();
detachedPlan.Name += " copy";
db.Plans.Add(detachedPlan);
0
8/18/2018 3:25:41 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow