BulkInsert doesn't use navigation property?

[Bouke]

Description

When using BulkInsert, I want to add as little as possible to the change tracker (preferably nothing). However when working with related items, I cannot get it to work.

Exception

System.Data.SqlClient.SqlException: 'The MERGE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Targets_dbo.RelatedAs_RelatedAId". The conflict occurred in database "XXX", table "dbo.RelatedAs", column 'Id'.'
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at System.Data.SqlClient.SqlDataReader.Read()
   at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at .(DbCommand , BulkOperation , Int32 )
   at .( , DbCommand )
   at .Execute(List`1 actions)
   at Z.BulkOperations.BulkOperation.Execute()
   at Z.EntityFramework.Extensions.EntityBulkOperation`1.BulkInsert()
   at .BulkInsert[T](BulkOperation`1 this, DbContext context, List`1 list, Boolean isManager, List`1 entitiesToUpdate, Type type, String typeName)
   at .`1.(SchemaEntityType )
   at System.Collections.Generic.List`1.ForEach(Action`1 action)
   at .BulkInsert[T](DbContext this, BulkOperation`1 bulkOperation, IEnumerable`1 entities2, List`1 entitiesToUpdate)
   at DbContextExtensions.BulkInsert[T](DbContext this, IEnumerable`1 entities, Action`1 bulkOperationFactory)
   at ...

Fiddle or Project (Optional)

class Target {
    [Key]
    int Id { get; set; }

    int RelatedAId { get; set; }
    virtual RelatedA RelatedA { get; set; }
}
class RelatedA {
    [Key]
    int Id { get; set; }
}

var a = new RelatedA();
context.RelatedAs.BulkInsert(new[] { a });

// a.Id is now set to the identity generated by the DB
var t = new Target {
    RelatedA = a,
};

// I would expect the following to work, but
// it fails as it tries to insert with RelatedAId=0
context.Targets.BulkInsert(new[] { t });

Further technical details

• EF version: 6.4.4
• EF Extensions version: 4.0.104
• Database Provider: SqlClient (Azure SQL)

[JonathanMagnan]

Hello @Bouke ,

Thank you for reporting,

I will ask one of my developers to look at it as I tried both of your issues and I got no problem so I might have missed something.

It is possible to provide us your both table creation script for Target and RelatedA? In our case, this is the Target which have a relation to the table RelatedA so we can insert a RelatedA which doesn't have any Id correspondence to Target.

[JonathanMagnan]

Hello @Bouke ,

Unfortunately, there is nothing we can do here if your entity has both the navigation property and the property at this moment.

You can either set the value on your side such as:

var t = new Target {
   RelatedAId = a.Id;
    RelatedA = a,
};

or use the IncludeGraph to insert both items:

var a = new RelatedA();
var t = new Target {
    RelatedA = a,
};

context.Targets.BulkInsert(new[] { t }, options => options.IncludeGraph = true);

Let me know if one of these two solutions could work for you.

Best Regards,

Jon

[NicoKno]

I've got the same problem. I use the IncludeGraph, but it seems that the newly created Id is not propagated to the second BulkOperation. Is there a possibility to do that?

[NicoKno]

I resolved my issue by using tOptions => tOptions.ForceOutputFromUnmodifiedRow for the dependent Entity