Foreign key violation trying to BulkInsert deep owned object graph into SqlServer

[h0lg]

I banged my head against this one today and the wrong think cracked:

I'm trying to insert deep object graphs with a lot of owned relations and root entities for which I want to insert the ID (because it is externally generated and meaningful).

The scenario: I'm downloading these object graphs from a web API and save them into a SQL Server DB for local aggregation. I want to map the model returned by that API straight to the DB without mapping to an entity model first, so that I don't have to update DTOs when the web API changes, but merely generate a new EF migration. Using the default EF core AddRange() in combination with change tracking and SaveChanges() is not an option because it doesn't perform; I'm able to download thousands of entity graphs in seconds - but saving them to DB takes minutes and is a real memory hog. I want to be able to sync multiple times a day, each time potentially deleting and re-inserting fresh copies of hundreds of thousands of graphs. So change tracking and merging is not required, as long as I can delete and insert efficiently in bulk.

With version 5 I managed to get something performant running using this work-around, which stopped working with EF Core 6 and BulkExtensions v6:

db.BulkInsert(roots);
db.BulkUpdate(roots, o => o.IncludeGraph = true);

Now, seeing that https://github.com/borisdj/EFCore.BulkExtensions/issues/695 was merged, I'm trying to simply use db.BulkInsert(roots, o => o.IncludeGraph = true) but run into this error:

Microsoft.Data.SqlClient.SqlException : The MERGE statement conflicted with the FOREIGN KEY constraint "FK_GraphRoot_Items_RootEntities_OwnedComponentGraphRootId". The conflict occurred in database "EFCoreBulkTest_OwnedGraph", table "dbo.RootEntities", column 'Id'.

I tried a few alternatives for mapping and inserting, added a test and debugged a bunch, but can't seem to put my finger on where the issue is.

My gut feeling is that GrapUtil.SetDependencies() may need some massaging, but I haven't managed to wrap my head around it yet. The weird thing about the object graph is that there is an owned component that shares the table with the root entity and I'm not sure that the GrapUtil knows to 'navigate across' that - so to speak.

I'll attach a PR with a red test reproducing the error.

Any help with either

• fixing my mapping to make it work with BulkInsert(),
• my BulkConfig or
• a hint at where in the code this issue may be solved

would be greatly appreciated!

Also, a fat stack of kudos for this library. EF Core would be almost useless for my scenario without it!

[borisdj]

Glad it's useful. Graph feature was a PR, so am not completely into it. Try to use without Graph, call BulkInsert for each table, prior just set FKs

[h0lg]

@borisdj I see and thank you :) I just thought I'd fish for inspiration because I was stuck on this. I'll continue to improve my understanding of the BulkInsert() implementation to try and find a solution for this scenario as well when I get back to it. For now, I'm thinking about going back to the work-around with .NET / EF Core 5 as a temporary measure.

[dazinator]

I'm seeing the same kind of error when working with this data model described here: https://github.com/dotnet/efcore/issues/29183

In the following scenario, the database state prior to running this code is that there is one existing office record that I am fetching with EF, then I am setting its "Person" as well as adding this Person to it's "Persons" list, then attempting to persist those changes. if I call SaveChanges() on the DbContext it does work, where as using this library it throws. Once persisted, I am expecting the Office.PersonId in the database to be set to the ID of the newly inserted Person, implying that I am also expecting the new Person (which doesn't already exist in the database) to be inserted. Both entities use an Identity column as the PK in SQL server that auto increments by 1.

             
             var offices =
                   dbContext.Offices
                   .Include(a => a.Person)
                   .Where(i => i.Person != null )
                   .ToList();
                   
             foreach (var office in offices )
             {
                   var person = new Person 
                   {           
                      Office= "System"
                   };
                   
                   office.Person = person; // one to one - i.e the special person of the office.
                   // office.PersonId = null; // tried this,, same error.
                   office.Persons.Add(person);   // one to many.                   
            }      
           
            var bulkConfig = new BulkConfig
            {
                IncludeGraph = true,
                SetOutputIdentity = true,
                EnableShadowProperties = true,
                DoNotUpdateIfTimeStampChanged = true,
                OnSaveChangesSetFK =true,        
                TrackingEntities = true,
                ShadowPropertyValue = ((entity, property) =>
                {
                    if (property == OfficeDbContext.TenantIdPropertyName)
                    {
                        return tenant.Id;
                    }
                    return property;
                })
            };
            
            var strategy = dbContext.Database.CreateExecutionStrategy();

            await strategy.ExecuteAsync(async () =>
            {
                using var transaction = dbContext.Database.BeginTransaction();
                await  dbContext.BulkInsertOrUpdateAsync(offices, bulkConfig, cancellationToken: default);
                transaction.Commit();
            });           

           

This yields the following stack trace:

Message: 
    Microsoft.Data.SqlClient.SqlException : The MERGE statement conflicted with the FOREIGN KEY constraint "FK_Office_Person_PersonId". The conflict occurred in database "dummy", table "FRC.Person", column 'Id'.
    The statement has been terminated.

  Stack Trace: 
    SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
    SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
    SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
    TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
    --- End of stack trace from previous location ---
    RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
    <16 more frames...>
    --- End of stack trace from previous location ---
    ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
    ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
    ExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
    DbContextBulkExtensions.SaveWithExplicitTransactionAsync(DbContext dbContext, Func`2 saveMethod, CancellationToken cancellationToken) line 12
    ImportBulkService.SaveStuffAsync(List`1 invoices, CancellationToken cancellationToken) line 47
    --- End of stack trace from previous location ---

I would be greatful for any pointers.

[dazinator]

@borisdj did you find a solution?

[borisdj]

Graph was for simple relationship, instead try using 2 BulkOps calls, first with SetOutputIdentity and then set FK before calling second. Check in ReadMe:

Example of SetOutputIdentity with parent-child FK related tables // Option 1