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

问题描述

我在将数据插入具有外键的数据库表时遇到问题。
作为错误，我得到

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

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

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

BaseEntity.cs

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

    public DateTime CreateDate { get; set; }
}

MachineType.cs

MachineType.cs

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

SpareType.cs

SpareType.cs

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

SparePart.cs

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

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; }
    }

发布控制器

[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

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.

推荐答案

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

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();

您可以做的另一件事是在模型上公开外键，然后对其进行设置就足够了在新实例上添加到DbSet之前。在SparePart中：

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屋！