0

I'm new to using entity framework. I'm using EF5 to insert new data.

I get the dreaded error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_POSTransactionsKitMemberTaxRaw_POSTransactionsKitMemberSaleReturnRaw_KitMemberSaleReturnRowId".

I understand that to mean I don't have a row in [POSTransactionsKitMemberSaleReturnRaw] with a primary key that matches the insert for table [POSTransactionsKitMemberTaxRaw].

I wanted EF to generate the primary keys for me and expected the missing row to have been generated automatically. Here's the code that's failing:

foreach (POSTransactionsKitMemberSaleReturnRaw posTransactionsKitMemberSaleReturnRaw in posTransactionsKitMemberRaw.POSTransactionsKitMemberSaleReturnRaws)
{
    ++KitMemberSaleReturnRowId;

    // set temporary keys
    posTransactionsKitMemberSaleReturnRaw.KitMemberSaleReturnRowId = KitMemberSaleReturnRowId;
    posTransactionsKitMemberSaleReturnRaw.KitMemberRowId = KitMemberRowId;
    repository.AddPOSTransactionsKitMemberSaleReturnRaw(posTransactionsKitMemberSaleReturnRaw);

    foreach (POSTransactionsKitMemberTaxRaw posTransactionsKitMemberTaxRaw in posTransactionsKitMemberSaleReturnRaw.POSTransactionsKitMemberTaxRaws)
    {
        // set temporary keys
        posTransactionsKitMemberTaxRaw.KitMemberTaxRowId = ++KitMemberTaxRowId;
        posTransactionsKitMemberTaxRaw.KitMemberSaleReturnRowId = KitMemberSaleReturnRowId;
        posTransactionsKitMemberTaxRaw.KitMemberKitMemberSaleReturnRowId = null;
        repository.AddPOSTransactionsKitMemberTaxRaw(posTransactionsKitMemberTaxRaw);
    }
}

I've validated that there are temporary primary key values in the POCO objects at run time. A sql trace of the activity shows the attempted insert value was a generated value (1439630) not the temporary value I set it to.

I've set the Auto detect changes flag off:

_context.Configuration.AutoDetectChangesEnabled = false;

The intent was to improve performance. Since this is a pure insert there should be no way the database rows will change while I am trying to write them.

Any suggestions?

Jay
  • 13,803
  • 4
  • 42
  • 69
  • I see from you're code that you're assigning key values. In your database, are these fields defined as `IDENTITY` fields? If so, your shouldn't be setting them in your code. SQL Server will set them and feed their values back to EF upon insert. – STLDev Aug 18 '17 at 19:35
  • The primary keys on both tables are 'IDENTITY(1,1) NOT NULL'. I recently converted this code from ef4 to ef5 and the performance was destroyed. While searching for an answer I came across this post that lead me to believe I should set temp primary keys https://stackoverflow.com/questions/25275832/unable-to-determine-the-principal-end-of-the-relationship-on-saving-order-ef6 – Jay Aug 18 '17 at 19:51
  • I've done a fair amount of EF, and I don't believe I've ever had to do that. But there could be reasons for that. For instance, I make use of the collection objects on a parent to add new multiple children to a parent. When `SaveChanges()` is called, all the PKs and FKs are initialized properly. – STLDev Aug 18 '17 at 19:56
  • That's what I was doing before but got 'Unable to determine the principle end of the relationship' error. I think I'm down the rabbit hole but unfortunately I don't know which one ;) – Jay Aug 18 '17 at 20:04
  • Commented out 'AutoDetectChangesEnabled = false;' and it runs correctly. Perhaps it's an ordering issue? If the child insert is attempted before the parent it would cause that. – Jay Aug 18 '17 at 20:33
  • Leaving AutoDetectChangesEnabled = false in the code, and adding ChangeTracker.DetectChanges() in strategic spots seems to have fixed the issue. The performance on a test file is now 2:45 instead of 19:50! – Jay Aug 18 '17 at 21:14
  • 1
    EF is usually pretty performant. Happy you found the issues holding you up and that you're getting good performance. – STLDev Aug 18 '17 at 21:15

0 Answers0