EF Core保存实体失败，IDENTITY_INSERT为OFF

Question

我有一个模型类Category，它有一个可空的ParentId作为对其父类别的引用。我决定使用EF Core 7和它的FluentApi将这个模型映射到数据库，但我不想使用EF Core迁移，而是手动创建脚本。

CREATE TABLE [Categories] 
(
[Id] [bigint] IDENTITY(1,1) NOT NULL,       
[Created] [datetime2](0) NOT NULL,  
[ParentId] [bigint] NULL,
[Name] [nvarchar](255) NOT NULL,        
CONSTRAINT [PK_Categories_Id] 
PRIMARY KEY CLUSTERED ([Id] ASC)        
)   
ALTER TABLE [Categories] WITH CHECK 
ADD CONSTRAINT [FK_Cat_Cat] 
FOREIGN KEY([ParentId]) REFERENCES [Categories] ([Id])

模型类

public class Category
{    
public long Id { get; }
public string Name { get; set; };
public Category? Parent { get; set; }
public long? ParentId { get; set; } 
public ICollection<Category>? Categories { get; set; } = new List<Category>();            
}

EF Core 7 mapper

public class CategoryConfiguration : IEntityTypeConfiguration<Category>
{
public void Configure(EntityTypeBuilder<Category> builder)
{
builder.ToTable("Categories");
builder.HasKey(it => it.Id);
builder.Property(it => it.Id).ValueGeneratedNever();
builder.Property(it => it.Created).IsRequired();            
builder.Property(it => it.Name).IsRequired();   
builder.HasOne(it => it.Parent)
.WithMany(c => c.Categories)
.HasForeignKey(d => d.ParentId)
.IsRequired(false);
}
}
}

下面是应用程序代码。

var parentId = 1;  // id of the previously saved category in the DB
Category? parent = await _repository.GetById(parentId, cancellationToken);
var category = new Category 
{
Name = "test",
Parent = parent  //if I leave this line out the save will be successfully created              
};
await _repository.Insert(category);
try
{
await _repository.SaveAsync();
}
catch (DbUpdateException ex)
{
// Cannot insert explicit value for identity column in table 'Categories' when IDENTITY_INSERT is set to OFF.
}

你会注意到在映射器的Id属性上设置了ValueGeneratedNever()，这将是一个数据库的责任。

我尝试在数据库中显式设置身份:

SET IDENTITY_INSERT [Categories] ON

但是我仍然得到

当IDENTITY_INSERT设置为OFF时，不能在表'Categories'中插入标识列的显式值。

更新:在映射类上，我尝试了下面的

-  builder.Property(it => it.Id).UseIdentityColumn(1,1);
-  builder.Property(it => it.Id).UseIdentityColumn();
-  builder.Property(it => it.Id).ValueGeneratedNever();
-  builder.Property(it => it.Id).ValueGeneratedAdd(); 

但是我仍然有相同的错误。

存储库插入只是一个抽象的数据库上下文添加

public async Task Insert(T entity)
{
await _dbContext.Set<T>().AddAsync(entity);
}

Answer 1

这是因为默认情况下，实体的Id是0，而EntityFramework正试图在不允许的地方添加值。

试试这个:

builder.HasKey(it => it.Id);
// NOTE: Never get involved in primary key generation and consider 
//       they are unique in the domain
// Remove .ValueGeneratedNever() and use .UseIdentityColumn() instead
builder.Property(it => it.Id).UseIdentityColumn();