EF Core 为标识列插入显式值 [英] EF Core insert explicit value for identity column

问题描述

我在将数据插入具有外键的数据库表时遇到问题.作为错误，我得到 <块引用>

无法为表中的标识列插入显式值IDENTITY_INSERT 设置为 OFF 时的MachineTypes".无法插入表 'SpareTypes' 中标识列的显式值IDENTITY_INSERT 设置为关闭.

BaseEntity.cs

公共抽象类 BaseEntity{[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]公共 Int64 Id { 获取；放;}公共日期时间 CreateDate { 获取；放;}}

MachineType.cs

公共类 MachineType : BaseEntity{[必需的][字符串长度(50)]公共字符串名称 { 获取；放;}}

备用类型.cs

 公共类 SpareType : BaseEntity{[必需的][字符串长度(25)]公共字符串名称 { 获取；放;}}

备件.cs

公共类 SparePart : BaseEntity{[必需的][字符串长度(100)]公共字符串内部代码 { 获取；放;}[字符串长度(4096)]公共字符串 描述 { 获取；放;}[字符串长度(255)]公共字符串 NameOnFolder { 获取；放;}公共小数点?输入 { 获取;放;}公共小数点?退出{得到;放;}公共小数点?厚度{得到;放;}公共字符串乐队{得到；放;}公共字符串颜色 { 获取；放;}公共布尔弹性{得到；放;}[必需的]公共虚拟机类型 MachineType { 获取；放;}[必需的]公共虚拟 SpareType SpareType { 获取；放;}}

SparePartViewModel.cs

 公共类 SparePartViewModel{公共 int Id { 获取；放;}公共日期时间 CreateDate { 获取；放;}[必需的][字符串长度(100)]公共字符串内部代码 { 获取；放;}[字符串长度(4096)]公共字符串 描述 { 获取；放;}[字符串长度(255)]公共字符串 NameOnFolder { 获取；放;}公共小数点?输入 { 获取;放;}公共小数点?退出{得到;放;}公共小数点?厚度{得到;放;}公共字符串乐队{得到；放;}公共字符串颜色 { 获取；放;}公共布尔弹性{得到；放;}[必需的]公共虚拟机类型 MachineType { 获取；放;}[必需的]公共虚拟 SpareType SpareType { 获取；放;}}

发布控制器

[Route("/api/v1/items")]公共类 SparePartController :控制器{私有 IRepository_repoSparePart;公共 SparePartController(IRepository repoSparePart){_repoSparePart = repoSparePart;}[HttpPost("")]公共异步任务Post([FromBody]SparePartViewModel viewModel){如果(模型状态.IsValid){var newItem = Mapper.Map(viewModel);newItem.CreateDate = DateTime.Now;_repoSparePart.Insert(newItem);如果(等待 _repoSparePart.SaveChangesAsync()){return Created($"items/{newItem.InternalCode}", Mapper.Map(viewModel));}}return BadRequest("保存失败.");}

}

AppContext.cs

 公共类 AppContext : IdentityDbContext{私有 IConfigurationRoot _config;公共 AppContext(IConfigurationRoot 配置，DbContextOptions 选项):基础(选项){_config = 配置；}public DbSet备用类型 { 获取；放;}public DbSet机器类型 { 获取；放;}公共 DbSet备件 { 得到;放;}受保护的覆盖无效 OnModelCreating(ModelBuilder builder){builder.Entity().HasIndex(s => new { s.Name }).IsUnique(真);builder.Entity().HasIndex(s => s.Name).IsUnique(真);base.OnModelCreating(builder);}protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder){base.OnConfiguring(optionsBuilder);optionsBuilder.UseSqlServer(_config["ConnectionStrings:RndDbContextConnection"]);}}

在发布过程中，所有数据都被正确捕获，但是什么时候应该插入到数据库中，我收到了一个错误.

解决方案

在插入新的 SparePart 实例时，需要事先从同一个 DbContext 中获取 MachineType 和 SpareType 引用.否则 EF 认为您正在尝试创建新的 MachineType 和 SpareType.并且由于设置了 Id 字段，数据库将不允许插入.像这样:

newItem.MachineType = _context.MachineTypes.Find();newItem.SpareType = _context.SpareTypes.Find();context.SpareParts.Add(newItem);context.SaveChanges();

您可以做的另一件事是在模型上公开外键，然后在将其添加到 DbSet 之前在新实例上设置它就足够了.备件:

[ForeignKey("MachineType")]公共 Int64 MachineTypeId { 获取；放;}[外键(备用类型")]公共 Int64 SpareTypeId{ 获取；放;}

I have problem with inserting data to database table which has foreign keys. As error I'm getting

Cannot insert explicit value for identity column in table 'MachineTypes' when IDENTITY_INSERT is set to OFF.Cannot insert explicit value for identity column in table 'SpareTypes' when IDENTITY_INSERT is set to OFF.

BaseEntity.cs

public abstract class BaseEntity
{
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public Int64 Id { get; set; }

    public DateTime CreateDate { get; set; }
}

MachineType.cs

public class MachineType : BaseEntity
{
    [Required]
    [StringLength(50)]
    public string Name { get; set; }
}

SpareType.cs

   public class SpareType : BaseEntity
    {
        [Required]
        [StringLength(25)]
        public string Name { get; set; }
    }

SparePart.cs

public class SparePart : BaseEntity
{
[Required]
[StringLength(100)]
public string InternalCode { get; set; }

[StringLength(4096)]
public string Description { get; set; }

[StringLength(255)]
public string NameOnFolder { get; set; }

public decimal? Enter { get; set; }

public decimal? Exit { get; set; }

public decimal? Thickness { get; set; }

public string Band { get; set; }

public string Color { get; set; }

public bool Elastic { get; set; }

[Required]
public virtual MachineType MachineType { get; set; }

[Required]
public virtual SpareType SpareType { get; set; }
}

SparePartViewModel.cs

 public class SparePartViewModel
    {
        public int Id { get; set; }

        public DateTime CreateDate { get; set; }

        [Required]
        [StringLength(100)]
        public string InternalCode { get; set; }

        [StringLength(4096)]
        public string Description { get; set; }

        [StringLength(255)]
        public string NameOnFolder { get; set; }

        public decimal? Enter { get; set; }

        public decimal? Exit { get; set; }

        public decimal? Thickness { get; set; }

        public string Band { get; set; }

        public string Color { get; set; }

        public bool Elastic { get; set; }



        [Required]
        public virtual MachineType MachineType { get; set; }

        [Required]
        public virtual SpareType SpareType { get; set; }
    }

Controller for posting

[Route("/api/v1/items")]
public class SparePartController : Controller
{
private IRepository<SparePart> _repoSparePart;


public SparePartController(IRepository<SparePart> repoSparePart)
{
    _repoSparePart = repoSparePart;

}

[HttpPost("")]
public async Task<IActionResult> Post([FromBody]SparePartViewModel viewModel)
{
    if (ModelState.IsValid)
    {
        var newItem = Mapper.Map<SparePart>(viewModel);
        newItem.CreateDate = DateTime.Now;

        _repoSparePart.Insert(newItem);

        if (await _repoSparePart.SaveChangesAsync())
        {
            return Created($"items/{newItem.InternalCode}", Mapper.Map<SparePartViewModel>(viewModel));
        }
    }
    return BadRequest("Failed to save.");
}

}

AppContext.cs

   public class AppContext : IdentityDbContext<ApplicationUser>
    {
        private IConfigurationRoot _config;

        public AppContext(IConfigurationRoot config, DbContextOptions options) : base(options)
        {
            _config = config;
        }

        public DbSet<SpareType> SpareTypes { get; set; }
        public DbSet<MachineType> MachineTypes { get; set; }
        public DbSet<SparePart> SpareParts { get; set; }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.Entity<SpareType>()
                .HasIndex(s => new { s.Name })
                .IsUnique(true);

            builder.Entity<MachineType>()
                .HasIndex(s => s.Name)
                .IsUnique(true);


            base.OnModelCreating(builder);
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            optionsBuilder.UseSqlServer(_config["ConnectionStrings:RndDbContextConnection"]);
        }
    }

During the posting all the data are catch correctly, but when should be inserted into database I'm getting an error.

解决方案

When inserting the new SparePart instance, the MachineType and SpareType references need to be fetched from the same DbContext beforehand. Otherwise EF thinks you are trying to create a new MachineType and SpareType. And because the Id field is set, the database won't allow the insert. Something like so:

newItem.MachineType = _context.MachineTypes.Find(<some_id>);
newItem.SpareType = _context.SpareTypes.Find(<some_id>);
context.SpareParts.Add(newItem);
context.SaveChanges();

Another thing you can do is expose the foreign key on your model, then it is enough to set it on the new instance before adding it to the DbSet. In SparePart:

[ForeignKey("MachineType")]
public Int64 MachineTypeId { get; set; }

[ForeignKey("SpareType")]
public Int64 SpareTypeId{ get; set; }

这篇关于EF Core 为标识列插入显式值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！