识别关系和插入子实体导致“无法在表中插入身份列的显式值” [英] Identifying Relationship and inserting child entities causes "Cannot insert explicit value for identity column in table"

问题描述

我已经创建了一个如下测试：

我正在尝试获取身份识别关系以在Entity Framework中工作， p>

  public class OrderLine {
 [Key，Column（Order = 0）] 
 public int OrderLineId {get;组; } 
 
 [Key，ForeignKey（Order），Column（Order = 1）] 
 public int OrderId {get;组; } 
 
 public virtual Order Order {get;组; } 
 
 [ForeignKey（Product）] 
 public int ProductId {get;组; } 
 
 public virtual Product Product {get;组; } 
 
 public int Number {get;组; $} 
 
 
当我尝试添加一个OrderLine，如：
  var order = _orderRepository.Find（1）; 
 var NewOrderLine = new OrderLine（）
 {
 Number = 1，ProductId = 2 
}; 
 order.OrderLines.Add（NewOrderLine）; 
 _orderRepository.InsertOrUpdate（order）; 
 _orderRepository.Save（）; 
  
我收到错误
 
 
当IDENTITY_INSERT设置为OFF时，无法在表
'OrderLines'中插入标识列的显式值。
 
 
当我查看表时，SQL有一个主键超过OrderId和OrderLineId。也只有OrderLineId被设置为身份。所以它一定是试图插入一个值，但它似乎试图插入的唯一值是0。
 
 
 
任何想法，我可以做一个插入到表使用： 
  INSERT INTO [OrderLines] 
（[ProductId] 
，[Number] 
， [OrderId]）
 VALUES 
（2 
，1 
，1）
  
 
 
解决方案
我相信异常意味着表 OrderLines 中的复合主键列之一是一个自动生成的身份在数据库中（我猜列 OrderLineId ），但EF确实发送一个值（ 0 ，因为你没有指定当您将INSERT语句中的此列创建到数据库的新的 OrderLine ）的属性，因为该属性在EF模型中未标记为身份。默认情况下（=  IDENTITY_INSERT 是 OFF ）SQL Server不允许显式地将一个值插入到一个标识列中。 / p> 
 
 
确实在你的映射中，键列不是标识，因为默认情况下，在复合键中没有属性是一个标识。
 
 
 
我认为，解决方案是关闭数据库中的身份规范，或将该属性标记为模型中的身份：
  public class OrderLine 
 {
 [Key，Column（Order = 0），DatabaseGenerated（DatabaseGeneratedOption.Identity）] 
 public int OrderLineId {get;组; } 
 
 [Key，ForeignKey（Order），Column（Order = 1）] 
 public int OrderId {get;组; } 
 
 public virtual Order Order {get;组; } 
 
 // ... 
} 
  
  DatabaseGeneratedOption.Identity 表示EF希望列值在数据库中生成，因此在尝试插入新实体时不会将值发送到DB。
 
I am trying to get identifiying relationships to work in Entity Framework so I can remove items from their collection using Remove.

I have created a test like:
public class OrderLine{   
    [Key, Column(Order = 0)]
    public int OrderLineId { get; set; }

    [Key, ForeignKey("Order"), Column(Order = 1)]
    public int OrderId{ get; set; }

    public virtual Order Order{ get; set; }

    [ForeignKey("Product")]
    public int ProductId { get; set; }

    public virtual Product Product { get; set; }

    public int Number { get; set; }}
When I attempt to add a OrderLine like:
        var order=_orderRepository.Find(1);
        var NewOrderLine= new OrderLine()
                                            {
                                                Number = 1,ProductId= 2
                                            };
        order.OrderLines.Add(NewOrderLine);
        _orderRepository.InsertOrUpdate(order);
        _orderRepository.Save();
I get the error 

  
Cannot insert explicit value for identity column in table
  'OrderLines' when IDENTITY_INSERT is set to OFF.
When I look at the table SQL has a primary key over OrderId and OrderLineId. Also only OrderLineId is set as identity. So it must be trying to insert a value but the only value it seems to be trying to insert is 0.

Any ideas I can do an insert to the table using:
INSERT INTO [OrderLines]
           ([ProductId ]
           ,[Number]
           ,[OrderId])
     VALUES
           (2
           ,1
           ,1)

解决方案
I believe the exception means that one of the composite primary key columns in table OrderLines is an autogenerated identity in the database (I guess the column OrderLineId) but EF does send a value (0 because you don't specify the property when you create the new OrderLine) for this column in the INSERT statement to the database because the property is not marked as identity in the EF model. By default (= IDENTITY_INSERT is OFF) SQL Server does not allow to insert a value into an identity column explicitly.

Indeed in your mapping the key columns are not identities because by default in a composite key no property is an identity.

I think, the solution is to either turn off the identity specification in the database or mark the property as identity in the model:
public class OrderLine
{
    [Key, Column(Order = 0), DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int OrderLineId { get; set; }

    [Key, ForeignKey("Order"), Column(Order = 1)]
    public int OrderId { get; set; }

    public virtual Order Order { get; set; }

    // ...
}
DatabaseGeneratedOption.Identity means that EF expects that the column value is generated in the database and therefore does not send the value to the DB when you try to insert an new entity.

                        
这篇关于识别关系和插入子实体导致“无法在表中插入身份列的显式值”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！