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.