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

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])
);

Can you post the `Funding` class (mainly to see the navigation properties)? — Ivan Stoev, Aug 18 '18 at 08:41
Regarding multiple key columns: put the `Key` attribute to each involved column and add the `Order` property to the `Column` attribute. Don't know, if that contributes to the overall problem. — Jürgen Röhr, Aug 18 '18 at 12:10
You better add `detachedPlan` to a new context instance. EF will do the rest. — Gert Arnold, Aug 19 '18 at 17:48

score 0 · Answer 1 · answered Aug 18 '18 at 15:25

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);

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

1 Answers1