UpdateException Entity framework bidirectional foreign key


Question

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.

Then, when I generate the Entity Framework Entities from DataBase I get this diagram:

enter image description here

The one that its * (from Check) to 1 (CheckStatusHistory), should be 1 to 1. But this is not possible for a limitation of EF.

Then, in my code, when I want to create a check with the status history, I do:

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,
                };

This should be adding a row in [CheckStatusHistory]. This newCheck.CheckStatusHistory is the CheckStatusHistory related to LastCheckStatusID.

But when I do db.SaveChanges().

I get the following error:

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[]& outputs)
       en System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& 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.<Update>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.<SaveChangesToStore>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.<>c__DisplayClass28.<SaveChanges>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: 

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.

I need some help with this.

I leave the SQL Tables here:

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])
    )

Popular Answer

Yes it is related to two FKs. EF uses the model to create ordering of DB commands. It doesn't check exactly what data you set. It tries to find an ordering which will work in every scenario - such ordering does not exist in your case because your model allows creating two records (one in each table) which will be dependent on each other. In such case it is not possible to insert them in one pass. On DB level you would just insert the first one without the dependency to the second one, the second one with the dependency to the first one and after that update the first one with dependency to the second one. EF doesn't work this way.

Others may disagree but over time I come to conclusion that history tables works best without restricting them by different referential constraints. That for example allows keeping history of data deleted from primary table. Do you need FK from StatusHistory to Check? You can still store CheckID and execute manual Linq query to get history for Check even if you don't have FK but you will loose navigation property property.





Licensed under: CC-BY-SA
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why