Unique constraints not evaluating on Entity.Add

Question

The following table works in SQL, by that i mean i can add rows and if there is an duplicate it prompts me that it can not be inserted. When trying to insert in Entity Framework the data does get added to the local store, with the command:

 tblOrderDetail ord = new tblOrderDetail();
                        ord.FK_ServiceID = cboService.ToSelectedIntValue();
                        ord.FK_QuoteID = primKey;
                        ord.Quantity = txtServiceQuant.ToInt();
                        ord.LineTotal = txtServiceQuant.ToInt() * txtServiceUnit.ToDecimal();                      
contx.tblOrderDetails.Add(ord); 

If i try to save the changes,like below, if it is an unique row everything works perfectly. Also if i try to save an row that already exists i get the error of unique constraint as expected.

Directly after this if i try to insert another order detail row that is unique i get an error of unique constraint of the PREVIOUS row that didn't commit to the DB.

Is this because there is still a copy in the Local store? or am i missing something ?

contx.SaveChanges();

to save the changes an the following error occurs:

CREATE TABLE [dbo].[tblOrderDetail](
    [PK_OrderDetailID] [int] IDENTITY(1,1) Primary Key NOT NULL,
    [FK_ServiceID] [int] NULL,
    [FK_ProductID] [int] NULL,
    [FK_QuoteID] [int] NULL,
    [FK_InvoiceID] [int] NULL,
    [Quantity] [money] NOT NULL,
    [LineTotal] [money] NOT NULL,
    [Deleted] [bit] NULL,
 CONSTRAINT [uc_InvoiceQuoteProductService] UNIQUE NONCLUSTERED 
(
    [FK_InvoiceID] ASC,
    [FK_QuoteID] ASC,
    [FK_ServiceID] ASC,
    [FK_ProductID] ASC
))
GO
    Go

ALTER TABLE [dbo].[tblOrderDetail] ADD  CONSTRAINT [DF_tblOrderDetail_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO

ALTER TABLE [dbo].[tblOrderDetail]  WITH CHECK ADD FOREIGN KEY([FK_InvoiceID])
REFERENCES [dbo].[tblInvoice] ([PK_InvoiceID])
GO

ALTER TABLE [dbo].[tblOrderDetail]  WITH CHECK ADD FOREIGN KEY([FK_QuoteID])
REFERENCES [dbo].[tblQuote] ([PK_QuoteID])
GO

ALTER TABLE [dbo].[tblOrderDetail]  WITH CHECK ADD FOREIGN KEY([FK_ServiceID])
REFERENCES [dbo].[tblService] ([PK_ServiceID])
GO


SET IDENTITY_INSERT [dbo].[tblOrderDetail] ON 
INSERT [dbo].[tblOrderDetail] ([PK_OrderDetailID], [FK_ServiceID], [FK_ProductID], [FK_QuoteID], [FK_InvoiceID], [Quantity], [LineTotal], [Deleted]) VALUES (1,1, NULL, 1, NULL, 12.0000, 3000.0000, NULL)
INSERT [dbo].[tblOrderDetail] ([PK_OrderDetailID], [FK_ServiceID], [FK_ProductID], [FK_QuoteID], [FK_InvoiceID], [Quantity], [LineTotal], [Deleted]) VALUES (2,2, NULL, 2, NULL, 1.0000, 300.0000, NULL)
INSERT [dbo].[tblOrderDetail] ([PK_OrderDetailID], [FK_ServiceID], [FK_ProductID], [FK_QuoteID], [FK_InvoiceID], [Quantity], [LineTotal], [Deleted]) VALUES (3,3, NULL, 5, NULL, 4.0000, 600.0000, NULL)
INSERT [dbo].[tblOrderDetail] ([PK_OrderDetailID], [FK_ServiceID], [FK_ProductID], [FK_QuoteID], [FK_InvoiceID], [Quantity], [LineTotal], [Deleted]) VALUES (4,4, NULL, 5, NULL, 8.0000, 4000.0000, NULL)
INSERT [dbo].[tblOrderDetail] ([PK_OrderDetailID], [FK_ServiceID], [FK_ProductID], [FK_QuoteID], [FK_InvoiceID], [Quantity], [LineTotal], [Deleted]) VALUES (5,5, NULL, 5, NULL, 6.0000, 2700.0000, NULL)
SET IDENTITY_INSERT [dbo].[tblOrderDetail] OFF

The Inner Inner Message is as follows:

{"Violation of UNIQUE KEY constraint 'uc_InvoiceQuoteProductService'. Cannot insert duplicate key in object 'dbo.tblOrderDetail'. The duplicate key value is (, 4, 2, ).\r\nThe statement has been terminated."}

StackTrace:

   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   at System.Data.Entity.DbContext.SaveChanges()
   at WWITIQ.PresentationLayer.Windows.pgeQuotes.btnAddServices_Click_1(Object sender, RoutedEventArgs e) in c:\Users\...

Answer 1

If you're still in the "same" context, you'll have to first detach the problematic entity from context before trying to save again.

If you don't, it will still be attached to context, and the SaveChanges will be applied to all attached entities.

So try to do something like that :

contx.Entry(<theEntityWhichBreaksUnicityConstraint>).State = EntityState.Detached;