Inserting a record to Recursive Table with EF Core 6, identity table set ON

Viewed 60

I have the following two tables

CREATE TABLE [Finance].[Account]
(
    [AccountId] INT IDENTITY(1, 1) NOT NULL,
    [Name] NVARCHAR(100)  NOT NULL,    
    [AvailableBalance] DECIMAL(13, 4) NULL, 
    [CurrentBalance] DECIMAL(13, 4) NOT NULL, 
    [TypeId] INT NULL, 
    CONSTRAINT [PK_Account_Id] 
        PRIMARY KEY CLUSTERED ([AccountId] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY], 
    CONSTRAINT [FK_Account_Type] 
        FOREIGN KEY (TypeId) REFERENCES [Finance].[AccountType](AccountTypeId)
) ON [PRIMARY]

CREATE TABLE [Finance].[AccountType]
(
    [AccountTypeId] INT IDENTITY(1, 1) NOT NULL,
    [AccountTypeParentId] INT NULL,
    [Name] NVARCHAR(100) NOT NULL

    CONSTRAINT [PK_AccountType] 
        PRIMARY KEY CLUSTERED ([AccountTypeId])
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY], 
        CONSTRAINT [FK_AccountType_Parent_AccountType] 
            FOREIGN KEY (AccountTypeParentId) REFERENCES [Finance].[AccountType](AccountTypeId), 
   ) ON [PRIMARY]

With the following class structure

public class Account 
{
    public int AccountId { get; private set; }
    public string Name { get; private set; }
    public decimal AvailableBalance { get; private set; }
    public decimal CurrentBalance { get; private set; }
    public AccountType Type { get; private set; }
    
    public void SetType(AccountType type)
    {
        if (type != null)
        {
            Type = type;
        }
    }
}

public class AccountType 
{
    public int AccountTypeId { get; private set; }
    public string Name { get; private set; }
    public int? AccountTypeParentId { get; private set; } = null;
    public AccountType ParentType { get; private set; }

    protected AccountType()
    {
    }

    private AccountType(int accountTypeId, string name) 
    {
        AccountTypeId = accountTypeId;
        Name = name;
    }

    public static AccountType CreateAccountType(string name)
    { 
        return new AccountType(0, name, DateTime.UtcNow);
    }

    public void SetParent(AccountType type)
    {
        ParentType = type;
    }
}

I am using following mapping in EF Core 6

modelBuilder.Entity<AccountType>(entity =>
{
    entity.ToTable("AccountType", schema: "Finance").HasKey(c=>c.AccountTypeId);
    entity.Property(c => c.AccountTypeId).ValueGeneratedOnAdd();
    entity.Property(t => t.AccountTypeId).HasColumnName("AccountTypeId");
    entity.Property(t => t.Name).HasColumnName("Name");
    entity.Property(t => t.AccountTypeParentId).HasColumnName("AccountTypeParentId");
    entity.HasOne(t => t.ParentType)
       .WithMany()
       .HasForeignKey("AccountTypeParentId");       
});

modelBuilder.Entity<Account>(entity =>
{
    entity.ToTable("Account", schema: "Finance");
    entity.Property(c => c.AccountId).ValueGeneratedOnAdd();
    entity.Property(o => o.CurrentBalance).HasColumnType("decimal(13,4)");
    entity.Property(o => o.AvailableBalance).HasColumnType("decimal(13,4)");
    entity.HasOne(b => b.Type).WithOne().HasForeignKey<AccountType>(b => b.AccountTypeId);
});

When trying to insert a record using the following call

Account account = CreateAccount();
AccountType type = AccountType.CreateAccountType("ParentType");
type.SetParent(AccountType.CreateAccountType("ChildType"));
account.SetType(type);

_context.Accounts.Add(account);

I am getting the following exception

SqlException: Cannot insert explicit value for identity column in table 'AccountType' when IDENTITY_INSERT is set to OFF.

It seems like everything is set right, I am new to EF Core, so not sure how it handles recursive insert.

Thanks in advance for any help.

0 Answers
Related