I'm inserting data into the Receipts table in my database. This table has a structured relationship to Stores linked on Stores.Id = Receipts.StoreId
Occasionally, the insert meets with the following error:
System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Receipts_dbo.Stores_StoreId".
I did 5000 posts to the database and this error only occurred 10 times in that batch. The data for all 5000 posts was identical (see code below).
EDIT
I've run a second batch of 5000 posts and this time all of them went through without error effectively making the chance of an error occurring 1000:1
I've verified that there is a store in the Stores table with the Id value specified in the ajax post data.
Can anyone tell me why only 1/500th of the posts I'm sending experiences this error?
// function gets the receipt number from the loop that calls it
function CreateData(receiptNumber) {
var StoreId = 5;
var ReceiptValue = 100.50;
var Cashback = 10.50;
var Receipt = {
"StoreId": StoreId,
"ReceiptNumber": receiptNumber,
"ReceiptValue": ReceiptValue,
"Cashback": Cashback };
return Receipt;
}
// function initiates the loop for x times
function LoopPostData ( iterations ) {
for ( var i = 1; i <= iterations; i++ ) {
PostData( i );
}
}
function PostData ( iteration ) {
var PostCompleted = false,
ReceiptNumber = "TST-"+ pad( iteration, 4 );
UpdatePostsTable( "- Attempting to post "+ ReceiptNumber );
$.ajax( {
type: "post",
url: "http://localhost:54354/api/Receipt",
data: CreateData( ReceiptNumber ),
success: function () {
var msg = "-"+ ReceiptNumber +" was saved on the API";
UpdateSuccessTable( msg ); // indicate the post was saved
},
statusCode: {
400: function () {
var msg = "-"+ ReceiptNumber +" was sent to API but not saved";
UpdateErrorsTable( msg ); // indicate the post couldn't be saved
PostCompleted = true;
}
},
error: function () {
if ( PostCompleted ) return;
var msg = "-"+ ReceiptNumber +" was not sent to the API";
UpdateErrorsTable( msg ); // indicate the post was sent to the API
}
} );
}
EDIT
Here's some more info.
Here's the code that actually does the insert into the database. It uses Entity Framework
private void PostReceipt(CreateReceiptViewModel Source)
{
PocketPlootoEntities db = new PocketPlootoEntities();
Receipt Receipt = new Receipt
{
Calculated = false,
Cashback = Source.Cashback,
Deleted = false,
ReceiptDate = DateTime.Now,
ReceiptNumber = Source.ReceiptNumber,
ReceiptValue = Source.ReceiptValue,
Redeemed = false,
Store = db.Stores.FirstOrDefault(x => x.Id == Source.StoreId),
StoreId = Source.StoreId,
ValidUntil = DateTime.Now.AddDays(7)
};
db.Receipts.Add(Receipt);
db.SaveChanges();
}
This is hit by the ajax post above.
Here's the Stores table (bear in mind that the ajax is posting to the store with Id 5 (Roman's Pizza)
Based on this, I can't believe that the error is occurring because there is no linked record in the Stores table for the new Receipt.
EDIT 2
I pasted the POCO classes that Entity Framework Code-First used to create the tables as well as the SQL code to create them as well.
To save some time, here's the Foreign Key Constraint in question per the error message:
ALTER TABLE [dbo].[Receipts] WITH CHECK ADD CONSTRAINT [FK_dbo.Receipts_dbo.Stores_StoreId] FOREIGN KEY([StoreId])
REFERENCES [dbo].[Stores] ([Id])
ON DELETE CASCADE
GO
EDIT 3
I've updated my server code only to make EF log its queries to the Output Windows in Visual Studio.
Here's one of the INSERT commands it executed (this command was selected randomly)
INSERT [dbo].[Receipts]([StoreId], [UserId], [ReceiptNumber],
[ReceiptValue], [ReceiptDate], [Cashback], [ValidUntil],
[Calculated], [Redeemed], [Deleted], [Invoice_Id])
VALUES (@0, NULL, @1, @2, @3, @4, @5, @6, @7, @8, NULL)
SELECT [Id]
FROM [dbo].[Receipts]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()
-- @0: '1' (Type = Int32)
-- @1: 'TST-0006' (Type = String, Size = -1)
-- @2: '100.5' (Type = Decimal, Precision = 18, Scale = 2)
-- @3: '2016/07/28 6:38:38 PM' (Type = DateTime2)
-- @4: '10.5' (Type = Decimal, Precision = 18, Scale = 2)
Opened connection at 2016/07/28 6:38:38 PM +02:00
-- @5: '2016/08/04 6:38:38 PM' (Type = DateTime2)
Started transaction at 2016/07/28 6:38:38 PM +02:00
-- @6: 'False' (Type = Boolean)
-- @7: 'False' (Type = Boolean)
-- @8: 'False' (Type = Boolean)