时间戳

首页 > 解决方案 > 直接在表中插入行时出现 EF6 错误

c# - 直接在表中插入行时出现 EF6 错误

问题描述

使用 EF6、.NET Core 5、SQL Server

使用下面的 2 个类，我使用包管理器控制台和实体框架在 VS2019 中创建了 2 个相关表。

    public class ReservFile
    {
       [Key]
       [DatabaseGenerated(DatabaseGeneratedOption.None)]      // This value retrieved from a counter.
       public int IdNo { get; set; }
       public string Name { get; set; }
       [ForeignKey("ReFileIdNo")]
       public List<ReservItem> ReservItems { get; set; }  
    } 
    
    public class ReservItem
    {
    [Key]
    public int IdNo { get; set; }
    public int ReFileIdNo { get; set; }
    public string Code { get; set; }
    public string Description { get; set; }
    } 
    
    
    // This works if I insert using the ReservFiles table
    ...
    newRow = new ReservFile);
    newRow.IdNo = getValueFromCounter();
    newRow.Name = 'Joe';
    newRow.ReservItems.Add( new Item { RaFileIdNo = newRow.IdNo, Code = 'ggg', Description = 'kkkk'}); 
    db.ReservFiles.Add(newRow)
    db.SaveChanges();
    ...
    
    
    // But if I try to insert just items for an existing row directly in ReservItems This gives error:
    InnerException = {"Cannot insert explicit value for identity column in table 'ReservItems' when IDENTITY_INSERT is set to OFF."}
    
    newItem = new ReservItem();
    newItem.RaFileIdNo = newRow.IdNo;
    newItem.Code = 'ggg';
    newItem.Description = 'kkkk';
    db..Add(newItem);
    db.SaveChanges();
    
    
    // Then I try this and gives the error:
    InnerException = {"Explicit value must be specified for identity column in table 'ReservItems' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column."}
    
    db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [dbo].[ReservItems] ON");
    db..Add(newItem);
    db.SaveChanges();
    db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [dbo].[ReservItems] OFF");
    
    
    // Then I try this and gives the error:
    InnerException = {"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_dbo.ReservAdtlCharges_dbo.ReservFiles_ReservFile_IdNo\". The conflict occurred in database \"RPRentalPlus\", table \"dbo.ReservFiles\", column 'IdNo'.\r\nThe statement has been terminate...
    
    newItem.IdNo = 3316  (This is one more than the last one in the table)
    newItem.RaFileIdNo = newRow.IdNo;
    newItem.Code = 'ggg';
    newItem.Description = 'kkkk';

   db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [dbo].[ReservItems] ON");
    db..Add(newItem);
    db.SaveChanges();
    db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [dbo].[ReservItems] OFF");

我不知道还能做什么。

标签： c#sql-server.net-coreentity-framework-core

解决方案

您需要获取 ReserveItem 主键的方法。而且您在上一个示例中没有分配 RaFileIdNo。而且您不需要将该项目添加到整个 dbcontext。为此使用 db Set

尝试这个

newItem = new ReserveItem();
newItem.IdNo = 3316 //or how you get primary key, 3316 could be used already
newItem.RaFileIdNo = newRow.IdNo; 
newItem.Code = 'ggg';
newItem.Description = 'kkkk';
db.Set<ReserveItem>().Add(newItem);
db.SaveChanges();

推荐阅读