Sulieman Mansouri Sulieman Mansouri - 4 months ago 19
Vb.net Question

Entity Framework Many-to-Many Insert throws error Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

I created a created a code-first model from database, so I have my entities and context all setup.
Here is a diagram of the problematic tables.
Many to Many relation diagram
The relation is there to know which Payment is covering which orders, if that makes sense.
I am attempting to add a record to the PaymentSalesOrder by adding an instance of SalesOrder to payment.SalesOrders in EF.
When calling SaveChanges() I get this error:


ErrorCode



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


Here is the code, basically I query the database for the current customer and include his payments and orders.
I assign payment to sales order and change the payment and the salesOrder to reflect their availability.
Finally I add the salesOrder to the payment and call SaveChanges().

I have a customer with one order and one payment in the database so all entities in this code are filled with proper data.


Code




Using db As New eRestaurantEF.Context
If _customer Is Nothing Then
Exit Sub
End If
Dim tmpCustomer As eRestaurantEF.Customer
tmpCustomer = (From tmp In db.Customers.Include("Payments").Include("SalesOrders")
Where tmp.customerID = _customer.customerID
Select tmp).FirstOrDefault

For Each paymentRow As eRestaurantEF.Payment In tmpCustomer.Payments
If paymentRow.PaymentRemainder > 0 Then

For Each orderRow As eRestaurantEF.SalesOrder In tmpCustomer.SalesOrders
db.SalesOrders.Attach(orderRow)
db.Entry(orderRow).State = Entity.EntityState.Unchanged
If paymentRow.PaymentRemainder > 0 Then

If Not orderRow.FullyPaid Then

If paymentRow.PaymentRemainder >= orderRow.Remainder Then
Dim remainder As Decimal
remainder = paymentRow.PaymentRemainder
remainder -= orderRow.Remainder

paymentRow.PaymentRemainder = remainder
orderRow.Remainder = 0
orderRow.IsPaid = True
orderRow.IsCredit = True
orderRow.FullyPaid = True
paymentRow.SalesOrders.Add(orderRow)

ElseIf paymentRow.PaymentRemainder <= orderRow.Total Then
Dim remainder As Decimal
remainder = paymentRow.PaymentRemainder
remainder -= orderRow.Remainder

If remainder = 0 Then
paymentRow.PaymentRemainder = 0
orderRow.Remainder = 0
orderRow.IsPaid = True
orderRow.IsCredit = True
orderRow.FullyPaid = True
paymentRow.SalesOrders.Add(orderRow)
Exit For

ElseIf remainder < 0 Then
paymentRow.PaymentRemainder = 0
orderRow.Remainder = Math.Abs(remainder)
orderRow.IsPaid = False
orderRow.IsCredit = True
orderRow.FullyPaid = False
orderRow.Payments.Add(paymentRow)
Exit For

End If
End If
End If
End If
Next 'OrdersList
End If
Next 'PaymentsList

db.SaveChanges()
End Using


here is the Fluent Api for the relation table:

modelBuilder.Entity(Of Payment)() _
.HasMany(Function(e) e.SalesOrders) _
.WithMany(Function(e) e.Payments) _
.Map(Function(m) m.ToTable("PaymentSalesOrder").MapLeftKey("Payment_ID").MapRightKey("SalesOrder_No"))


Both FKs at the PaymentSalesOrder table are part of a composite key created at the database to insure that there are no duplicate rows.
I even tried removing the key and changing it to a unique index but the problem persisted.
Here is the table script from Sql:

CREATE TABLE [dbo].[PaymentSalesOrder](
[Payment_ID] [int] IDENTITY(1,1) NOT NULL,
[SalesOrder_No] [int] NOT NULL,
CONSTRAINT [PK_PaymentSalesOrder] PRIMARY KEY CLUSTERED
(
[Payment_ID] ASC,
[SalesOrder_No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PaymentSalesOrder] WITH CHECK ADD CONSTRAINT [FK_PaymentSalesOrder_Payment] FOREIGN KEY([Payment_ID])
REFERENCES [dbo].[Payment] ([PaymentID])
GO
ALTER TABLE [dbo].[PaymentSalesOrder] CHECK CONSTRAINT [FK_PaymentSalesOrder_Payment]
GO
ALTER TABLE [dbo].[PaymentSalesOrder] WITH CHECK ADD CONSTRAINT [FK_PaymentSalesOrder_SalesOrder] FOREIGN KEY([SalesOrder_No])
REFERENCES [dbo].[SalesOrder] ([SalesOrderNo])
GO

ALTER TABLE [dbo].[PaymentSalesOrder] CHECK CONSTRAINT [FK_PaymentSalesOrder_SalesOrder]
GO


My apologies for the long post but this is really maddening and I can't see where the problem is.
I am considering removing the many-to-many relation alltogether, but giving up without knowing the cause is not easy for me, what if I come across it again.

Answer

The error clearly states that You are trying to insert a value in the IDENTITY Column but you cannot insert value to an IDENTITY Column which in your case is Payment_ID of PaymentSalesOrder table. IDENTITY Column autogenerates its value when you insert a new row by incrementing by INCREMENT value provided, in your case is 1. What I mean to say is whenever you insert a new row the value of Payment_ID will insert automatically starting from 1 and will keep incrementing by 1 for each new row.

You can get more informatin on IDENTITY here.

I hope now you are clear