Migration of data - SqlException: The MERGE statement conflicted with the FOREIGN KEY constraint

Question

My application is developing and I have come to realize my old data model needed a full remake as it did not force me to add/remove entities from aggregate roots.

Therefore I now need to migrate my old data model into the new data model and are trying to use AutoMapper to simplify conversion of objects into the new graph.

IDs of my old Parent entity must be preserved, whereas IDs of related entities can change (if this is easier).

Simplified data model

Old and new entities are in general relatively similar, but they need a conversion. For simplicity I have left out irrelevant properties and references as they are not related to my issue.

Child entities cannot exist without a Parent. GrandChild entities cannot exist without a Child.

public class Parent
{
    long Id { get; set; }
    ICollection<Child> Children { get; set; }
}

public class Child
{
    long Id { get; set; }
    long ParentId { get; set; }
    Parent Parent { get; set; }
    ICollection<GrandChild> GrandChildren { get; set; }
}

public class GrandChild
{
    long Id { get; set; }
    long ChildId { get; set; }
    Child Child { get; set; }
}

AutoMapper configuration

CreateMap<OldParent, Parent>()
    .ForMember(dest => dest.Children, opt => opt.MapFrom(src => src.Children));
CreateMap<OldChild, Child>()
    .ForMember(dest => dest.GrandChildren, opt => opt.MapFrom(src => src.GrandChildren))
    .ForMember(dest => dest.Id, opt => opt.Ignore())
    .ForMember(dest => dest.Parent, opt => opt.Ignore())
    .ForMember(dest => dest.ParentId, opt => opt.Ignore());
CreateMap<OldGrandChild, GrandChild>()
    .ForMember(dest => dest.Id, opt => opt.Ignore())
    .ForMember(dest => dest.Child, opt => opt.Ignore())
    .ForMember(dest => dest.ChildId, opt => opt.Ignore());

Code to convert data

List<OldParent> oldParents = await oldContext.ParentDbSet
                                  .Include(p => p.Children)
                                  .ThenInclude(c => c.GrandChildren)
                                  .ToListAsync();

List<Parent> newParents = mapper.Map<List<Parent>>(oldParents);

using (var transaction = context.Database.BeginTransaction())
{
    await context.Database.ExecuteSqlRawAsync("SET IDENTITY_INSERT [dbo].[Parents] ON;");
    await context.MeetingDbSet.AddRangeAsync(newParents);
    await context.SaveChangesAsync();
    await context.Database.ExecuteSqlRawAsync("SET IDENTITY_INSERT [dbo].[Parents] OFF;");
    await transaction.CommitAsync();
}

When debugging I have identified GrandChild.ChildId = 0 and I believe this is causing my problems.

This is the error I get:

SqlException: The MERGE statement conflicted with the FOREIGN KEY constraint "FK_GrandChildren_Children_ChildId". The conflict occurred in database "my_en_db", table "dbo.Children", column 'Id'

The easy solution would be to copy the Id, but I cannot do this for dependent entities as SET IDENTITY_INSERT [dbo].[Parents] is only allowed for on table per transaction.

Another solution would be to change GrandChild.ChildId from long to long?, but I'm unsure of this will break the dependency and risk GrandChild entities to exist even if the principal entity is deleted?