title: UpdateException Entity framework bidirectional foreign key
body: <p>I´m having some trouble with a mapping in entity framework. I have a table Check, that has a foreign key to the table CheckStatusHistory where I store all the check status changes over time. As you can see, Check has a column LastCheckStatusID, that its a FK to CheckStatusHistoryID, but the table CheckStatusHistory also has CheckID as a FK to CheckID column of Check.
  The idea is to store the last CheckStatusHistoryID in the check so as to get more easily and with better performance the last check status. But also, to have all the historical information as well.</p>
  
  <p>Then, when I generate the Entity Framework Entities from DataBase I get this diagram:</p>
  
  <p><img src="https://i.stack.imgur.com/bdarV.png" alt="enter image description here"></p>
  
  <p>The one that its * (from Check) to 1 (CheckStatusHistory), should be 1 to 1. But this is not possible for a limitation of EF.</p>
  
  <p>Then, in my code, when I want to create a check with the status history, I do:</p>
  
  <pre><code>Check newCheck = new Check
                  {
                      Amount = c.Amount,
                      CheckDate = c.CheckDate,
                      CheckNumber = c.CheckNumber,
                      CheckPrefix = c.CheckPrefix,
                      Days = c.Days,
                      Expenses = c.Expenses,
                      RoutingNumbers = c.RoutingNumbers,
                      TradeHours = c.TradeHours,
                      TotalInterest = c.TotalInterest,
                      TentativeDepositDate = c.TentativeDepositDate,
                      TentativeAccreditationDate = c.TentativeAccreditationDate,
                      MonthlyRate = c.MonthlyRate
                  };
  
  newCheck.CheckStatusHistory = new CheckStatusHistory
                  {
                      CheckStatusID = CheckStatusIDs.Nuevo,
                      StatusDateTime = DateTime.Now,
                  };
  </code></pre>
  
  <p>This should be adding a row in [CheckStatusHistory]. This newCheck.CheckStatusHistory is the CheckStatusHistory related to LastCheckStatusID.</p>
  
  <p>But when I do db.SaveChanges().</p>
  
  <p>I get the following error:</p>
  
  <pre><code>System.Data.Entity.Infrastructure.DbUpdateException was unhandled by user code
    HResult=-2146233087
    Message=Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values.
    Source=EntityFramework
    StackTrace:
         en System.Data.Entity.Internal.InternalContext.SaveChanges()
         en System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
         en System.Data.Entity.DbContext.SaveChanges()
         en Plutus.Services.Check.CreateOperationHandler.Handle(CreateOperationRequest request) en e:\Plutus\Plutus.Services\Plutus.Services\Check\CreateOperationHandler.cs:línea 169
         en Plutus.Services.RequestResponse.RequestResponseFactory.Handle[TRequest,TResponse](TRequest request) en e:\Plutus\Plutus.Services\Plutus.Services\RequestResponse\RequestResponseFactory.cs:línea 48
         en Plutus.Services.Host.CheckService.CreateOperation(CreateOperationRequest request) en e:\Plutus\Plutus.Services\Plutus.Services.Host\CheckService.svc.cs:línea 50
         en SyncInvokeCreateOperation(Object , Object[] , Object[] )
         en System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]&amp; outputs)
         en System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc&amp; rpc)
    InnerException: System.Data.Entity.Core.UpdateException
         HResult=-2146233087
         Message=Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values.
         Source=EntityFramework
         StackTrace:
              en System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.DependencyOrderingError(IEnumerable`1 remainder)
              en System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.ProduceCommands()
              en System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
              en System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.&lt;Update&gt;b__2(UpdateTranslator ut)
              en System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func`2 updateFunction, Boolean throwOnClosedConnection)
              en System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update(Boolean throwOnClosedConnection)
              en System.Data.Entity.Core.Objects.ObjectContext.&lt;SaveChangesToStore&gt;b__33()
              en System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
              en System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy)
              en System.Data.Entity.Core.Objects.ObjectContext.&lt;&gt;c__DisplayClass28.&lt;SaveChanges&gt;b__25()
              en System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
              en System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
              en System.Data.Entity.Internal.InternalContext.SaveChanges()
         InnerException: 
  </code></pre>
  
  <p>I think this might be something related with having 2 FK, one from one table to other, and another from the last table to the first.</p>
  
  <p>I need some help with this.</p>
  
  <p>I leave the SQL Tables here:</p>
  
  <pre><code>CREATE TABLE [dbo].[Check](
          [CheckID] INT PRIMARY KEY IDENTITY NOT NULL,
          [RoutingNumbers] NCHAR(29) NOT NULL,
          [BankID] INT NOT NULL, --FK
          [BankBranchOfficeID] INT NOT NULL, -- FK
          [BankAccountID] INT NOT NULL,
          [CheckPrefix] NVARCHAR(3) NOT NULL,
          [CheckNumber] NCHAR(7) NOT NULL,
          [CheckDate] DATE NOT NULL,
          [Amount] MONEY NOT NULL,
          [TentativeDepositDate] DATE NOT NULL,
          [TradeHours] INT NOT NULL,
          [TentativeAccreditationDate] DATE NOT NULL,
          [Expenses] MONEY NULL,
          [MonthlyRate] DECIMAL (6,2) NOT NULL,
          [TotalInterest] MONEY NOT NULL,
          [Days] INT NOT NULL,
          [LastCheckStatusID] INT NOT NULL,
          [OperationID] INT NOT NULL,--FK
          CONSTRAINT FK_Check_BankID_Bank FOREIGN KEY ([BankID]) REFERENCES [dbo].[Bank]([BankID]),
          CONSTRAINT FK_Check_BankBranchOfficeID_BankBranchOffice FOREIGN KEY ([BankBranchOfficeID]) REFERENCES [dbo].[BankBranchOffice]([BankBranchOfficeID]),
          CONSTRAINT FK_Check_BankAccountID_BankAccount FOREIGN KEY ([BankAccountID]) REFERENCES [dbo].[BankAccount]([BankAccountID]),
          CONSTRAINT FK_Check_CheckStatusHistoryID_CheckStatusHistory FOREIGN KEY (LastCheckStatusID) REFERENCES [dbo].[CheckStatusHistory]([CheckStatusHistoryID]),
          CONSTRAINT FK_Check_OperationID_Operation FOREIGN KEY ([OperationID]) REFERENCES [dbo].[Operation]([OperationID])
          )
  
      /*---------------------------------------------------------------
          ESTADO DE CHEQUES
      */---------------------------------------------------------------
      CREATE TABLE [dbo].CheckStatus(
          [CheckStatusID] TINYINT PRIMARY KEY,
          [Name] NVARCHAR(30) NOT NULL 
      )
  
      /*---------------------------------------------------------------
          RELACION ESTADO - CHEQUE
      */---------------------------------------------------------------
  
          CREATE TABLE [dbo].[CheckStatusHistory](
          [CheckStatusHistoryID] INT PRIMARY KEY IDENTITY,
          [CheckStatusID] TINYINT NOT NULL, --FK
          [CheckID] INT NOT NULL,
          [StatusDateTime] DATETIME NOT NULL
          CONSTRAINT FK_CheckStatusHistory_CheckID_Check FOREIGN KEY ([CheckID]) REFERENCES [dbo].[Check]([CheckID]),
          CONSTRAINT FK_CheckStatusHistory_CheckStatusID_CheckStatus FOREIGN KEY ([CheckStatusID]) REFERENCES [dbo].[CheckStatus]([CheckStatusID])
      )
  </code></pre>