InvalidCastException thrown when using BulkSyncronize with class hierarchies

[hgGeorg]

Description

When trying to BulkSyncronize a table from one database to another, InvalidCastException is thrown. The table is created using code-first and is mapped to a class hierarchie. I've linked a minimal project that can reproduce this issue down below.

Exception

Exception message:
Unable to cast object of type 'BulkSyncTest.InheritEmpty' to type 'BulkSyncTest.InheritB'.
Stack trace:
   at System.Runtime.CompilerServices.CastHelpers.ChkCastAny(Void* toTypeHnd, Object obj)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.ClrPropertyGetter`2.GetClrValue(Object entity)
   at .`1.( , String )
   at .GetValue(Int32 i)
   at Microsoft.Data.SqlClient.SqlBulkCopy.GetValueFromSourceRow(Int32 destRowIndex, Boolean& isSqlType, Boolean& isDataFeed, Boolean& isNull)
   at Microsoft.Data.SqlClient.SqlBulkCopy.ReadWriteColumnValueAsync(Int32 col)
   at Microsoft.Data.SqlClient.SqlBulkCopy.CopyColumnsAsync(Int32 col, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
   at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid2[T0,T1](CallSite site, T0 arg0, T1 arg1)
   at .( , DbConnection , DbTransaction , Int32 )
   at .( )
   at .( )
   at .Execute(List`1 actions)
   at .(List`1 )
   at Z.BulkOperations.BulkOperation.Execute()
   at .BulkSynchronize[T](DbContext this, IEntityType entityType, IEnumerable`1 list, Action`1 options, Boolean forceSpecificTypeMapping)
   at .BulkSynchronize[T](DbContext this, IEnumerable`1 entities, Action`1 options)
   at BulkSyncTest.Program.Main(String[] args) in C:\source\repos\BulkSyncTest\BulkSyncTest\Program.cs:line 98

Fiddle or Project (Optional)

https://github.com/hgGeorg/bulk-sync-test

Further technical details

• EF version: 5.0.5
• EF Extensions version: 5.1.30
• Database Provider: Microsoft.EntityFrameworkCore.SqlServer

[JonathanMagnan]

Hello @hgGeorg ,

Thank you for reporting,

We have been able to reproduce the issue with your project, we will look if that's possible to support this scenario.

Best Regards,

Jon

[hgGeorg]

Hello @JonathanMagnan ,

do you have any updates on this issue? We'd like to have a bit more information or a rough estimate to plan our own development and business.

Best regards, Georg

[JonathanMagnan]

Hello @hgGeorg ,

I will ask my developer tomorrow and give you an update probably next Monday.

We currently try to skip most development during the summer but we will check if we can make an exception here.

Best Regards,

Jon

[JonathanMagnan]

Hello @hgGeorg ,

Unfortunately we still have a few problem here. But that's already possible to make something very similar in 2 steps:

context.TPHs.WhereBulkNotContains(list.Select(x => x.ID)).DeleteFromQuery();
context.BulkMerge(list);

Let me know if that could be an acceptable solution on your side.

We will re-look at this one after the support since it would be nice to support this scenario by default.

Best Regards,

Jon

[hgGeorg]

Hello @JonathanMagnan ,

using your suggested solution causes the following error to be thrown:

Oops! The 'BulkRead' and 'WhereBulkContains' method doesn't support the Entity Type 'TPHBaseclass' because the same property name is repeated more than once. We are currently improving this feature.

I did find a workaround without using WhereBulkNotContains though

var toKeep = sourceContext.Table.Select(e => e.Id).ToList();
var toDelete = await targetContext.Table.Where(x => !toKeep.Contains(x.Id)).ToListAsync(cancellation);
await targetContext.BulkDeleteAsync(toDelete, cancellation);

[JonathanMagnan]

Hello @hgGeorg ,

We are currently making some releases today. The fix for this issue was already coded so should be available very soon.

I will let you know when the new version will be ready.

[JonathanMagnan]

Hello @hgGeorg ,

Unfortunately, the fix was for another issue that I thought was similar.

We tried to make a fix during the weekend but unsuccessfully. So I guess you will need to keep using your workaround until we find out how to fix it.

Best Regards,

Jon

[hgGeorg]

Hello @JonathanMagnan,

do you have any update regarding this issue?

My workaround is starting to hit its limits.

var toKeep = sourceContext.Table.Select(e => e.Id).ToList();
var toDelete = await targetContext.Table.Where(x => !toKeep.Contains(x.Id)).ToListAsync(cancellation);
await targetContext.BulkDeleteAsync(toDelete, cancellation);
// or
var toKeep = sourceContext.Table.Select(e => e.Id).ToList();
await targetContext.Table.Where(x => !toKeep.Contains(x.Id)).DeleteFromQueryAsync(cancellation);

The toKeep list is getting larger. Our largest sample is sitting currently at only 25k entries and is causing performance issues. This amount is projected to only get higher in the future.

We are currently using version 6.13.16 with EFCore 6.

[JonathanMagnan]

Hello @hgGeorg ,

Unfortunately, there is no progress on this issue. We currently focus mainly on re-writing our library core to better support EF Core 7 and all inheritances (such as maybe making requests like this one easier).

How long does it currently take? We have some solution that will allow you to insert the ToKeep id list in a temporary table but then you will need to create the SQL for the DeleteFromQuery instead of using the method which could be an alternative solution to solve the performance issue.