Using IDENTITY_INSERT with FK_ChildTable_ParentTable?

Question

Hello,

Wrapping SaveChanges with ON and OFF works fine with individual tables.

But not in case of a parent-child relationship where a foreign key exists.

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'ParentTable' when IDENTITY_INSERT is set to OFF.

Herewith is a sample application showing the scenario, removing the foreign key is not an option.

Thank you in advance for any guidance.

---

dbo.ParentTable.sql

CREATE TABLE [dbo].[ParentTable]
(
    [Id]            INT        IDENTITY (1, 1) NOT NULL,
    [ParentCell]    NCHAR (50) NOT NULL,
    CONSTRAINT [PK_ParentTable] PRIMARY KEY CLUSTERED ([Id] ASC)
);

dbo.ChildTable.sql

CREATE TABLE [dbo].[ChildTable]
(
    [Id]               INT        IDENTITY (1, 1) NOT NULL,
    [ChildCell]        NCHAR (50) NOT NULL,
    [ParentTableId]    INT        NOT NULL,
    CONSTRAINT [PK_ChildTable] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_ChildTable_ParentTable_ParentTableId] FOREIGN KEY ([ParentTableId]) REFERENCES [dbo].[ParentTable] ([Id]) ON DELETE CASCADE
);

Parent.cs

namespace WebApplication1.Models;
public partial class Parent
{
    public int Id { get; set; }
    public string ParentCell { get; set; } = null!;
    public virtual ICollection<Child> Children { get; set; } = [];
}

Child.cs

namespace WebApplication1.Models;
public partial class Child
{
    public int Id { get; set; }
    public string ChildCell { get; set; } = null!;
    public int ParentTableId { get; set; }
    public virtual Parent Parent { get; set; } = null!;
}

EntitiesDbContext.cs

using Microsoft.EntityFrameworkCore;
using WebApplication1.Models;
namespace WebApplication1.Contexts;
public partial class EntitiesDbContext : DbContext
{
    public EntitiesDbContext()
    {
    }
    public EntitiesDbContext(DbContextOptions<EntitiesDbContext> options)
        : base(options)
    {
    }
    public virtual DbSet<Parent> ParentDbSet { get; set; }
    public virtual DbSet<Child> ChildDbSet { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlServer("Name=ConnectionStrings:EntitiesDbConnectionString");
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Parent>(entity =>
        {
            entity.ToTable("ParentTable");
            entity.Property(e => e.ParentCell)
                .HasMaxLength(50)
                .IsFixedLength();
        });
        modelBuilder.Entity<Child>(entity =>
        {
            entity.ToTable("ChildTable");
            entity.Property(e => e.ChildCell)
                .HasMaxLength(50)
                .IsFixedLength();
            entity.HasOne(d => d.Parent).WithMany(p => p.Children).HasForeignKey(d => d.ParentTableId);
        });
        OnModelCreatingPartial(modelBuilder);
    }
    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

AddEntities.cshtml.cs

using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using WebApplication1.Contexts;
using WebApplication1.Models;
namespace WebApplication1.Pages
{
    public class IndexModel(EntitiesDbContext _context) : PageModel
    {
        private readonly EntitiesDbContext context = _context;
        public async Task OnGetAsync()
        {
            await AddEntitiesAsync();
        }
        private async Task AddEntitiesAsync()
        {
            var entities = new[]
            {
                new
                {
                    ParentCell = "AA1",
                    ChildCells = new[] { "BB11", "BB12" }
                },
                new
                {
                    ParentCell = "AA2",
                    ChildCells = new[] { "BB21", "BB22", "BB23" }
                }
            };
            List<Parent> parents = [];
            List<Child> children = [];
            for (int i = 0; i < entities.Length; i++)
            {
                int parentTableId = i + 1;
                string parentCell = entities[i].ParentCell;
                string[] childCells = entities[i].ChildCells;
                Parent parent = new()
                {
                    Id = parentTableId,
                    ParentCell = parentCell
                };
                parents.Add(parent);
                for (int j = 0; j < childCells.Length; j++)
                {
                    Child child = new()
                    {
                        Id = j + 1,
                        ChildCell = childCells[j],
                        ParentTableId = parentTableId,
                    };
                    children.Add(child);
                }
            }
            context.ParentDbSet.AddRange(parents);
            _ = context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.ParentTable ON");
            _ = await context.SaveChangesAsync(); // throws exception
            _ = context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.ParentTable OFF");
        }
    }
}

Answer 1

In your specific example you are turning on identity inserts for the parent table but not the child table. If you want to do both then you'd need to turn it off for both tables. But this is a bad idea. You should really never need to do this short of a disaster recovery situation where you need to restore rows that are no longer available and for which you cannot change their IDs.

Why are you doing this at all? In general if you have defined the relationship properly in EF then EF will handle setting parent/child relationships automatically for you. You don't need (nor should) manage IDs manually). Specifically, in your code the child table rows will always have the wrong IDs after the first run of that code. You shouldn't be setting the IDs at all. Given your models, set the Parent property of the child to the Parent object you created and EF will handle inserting the parent and children and fixing up their IDs and relationships automatically.

Not tested, but something like this should be sufficient.

async Task AddEntitiesAsync ()
{
    var entities = new[]
    {
        new
        {
            ParentCell = "AA1",
            ChildCells = new[] { "BB11", "BB12" }
        },
        new
        {
            ParentCell = "AA2",
            ChildCells = new[] { "BB21", "BB22", "BB23" }
        }
    };

    foreach(var entity in entities)
    {
        //Create the parent EF object
        var parentEntity = context.ParentDbSet.Add(new Parent() {
            ParentCell = entity.ParentCell
        });

        //Create the children
        foreach (var child in entity.ChildCells)
        {
            //I prefer to add the entities directly to the DbSets 
            //but an alternative is to simply add the children to parentEntity.Children, has same effect
            context.ChildDbSet.Add(new Child() {
                ChildCell = child,
                Parent = parentEntity
            });        
        };
    };

    //Save the changes
    await context.SaveChangesAsync();    
}

The parent will be added and its Id set automatically by EF. The children will be created and the Id they will use will be set by DB. The parent of the child will be set because the parent is automatically updated.

To make sure EF knows the Id properties are set automatically then you should add the following to your table configurations for both tables (or use the data annotations).

entity.HasKey(x => x.Id);

This tells EF to get the value after inserting a new object because the DB is going to generate it automatically.

Answer 2

Instead of IDENTITY, use a sequence. Then you can insert explicit values without pain.

CREATE SEQUENCE MySeq AS int START WITH 1
go
CREATE TABLE MyTable (id int NOT NULL DEFAULT NEXT VALUE FOR MySeq,
                      data nvarchar(50) NOT NULL)
go
INSERT MyTable(data)
   SELECT TOP 10 name FROM sys.objects
INSERT MyTable (id, data)
   SELECT TOP 10 object_id, name FROM sys.all_objects WHERE object_id < 0
SELECT * FROM MyTable
go
DROP TABLE MyTable
DROP SEQUENCE MySeq

You can do the same for the child table, but the child table should not have its own surrogate key. The key for the child table should be a two-column key with the ParentTableId as the leading key. Having surrogate keys all over the place, makes joins more difficult to write, updates more difficult to maintain. And the table structure more difficult to understand.