Batch Operations (InsertFromQuery/UpdateFromQuery/DeleteFromQuery) fail SQL translation

[deimastep]

Issue Description

Batch operations (Insert, Update, DeleteFromQuery) fail to translate into SQL. This issue was first observed in EF Core version 5 and persists in EF Core 8 as well.

When using Join() in a query with a temporary table represented as IQueryable<int>, the DeleteFromQuery() operation fails with the following error:

ArgumentException: No mapping exists from object type System.Object[] to a known managed provider native type.

Context

I have implemented a custom solution to use temporary tables in EF Core queries. The temporary table is used to filter records by integer IDs, as shown below:

SortedSet<int> orderIds = [...];
IQueryable<int> ordersIdsQuery = await helper.GetTemporaryTableIdQuery(db, "#orders", orderIds, ct);

// It creates the temporary table as:
// CREATE TABLE [#orders](Id INT NOT NULL PRIMARY KEY CLUSTERED)

The temporary table #orders is created and populated with integer IDs using bulk copy. I then join this ordersIdsQuery with my entity query to filter records by their primary integer ID:

var count = await db.Set<ServiceOrder>()
    .Join(ordersIdsQuery, x => x.Id, x => x, (x, _) => x)
    .SelectMany(x => x.BillingEvents, (_, x) => x)
    .DeleteFromQuery(ct);

This query should translate into SQL similar to:

DELETE FROM [b0]
FROM [Bill_DistributionDocument] AS [b]
INNER JOIN (
    SELECT Id FROM [#orders]
) AS [u] ON [b].[Id] = [u].[Id]
INNER JOIN [Bill_Event] AS [b0] ON [b].[Id] = [b0].[DistributionDocId]

However, when the query is joined with the temporary table, the batch operation fails.

Investigation

I created a gist to demonstrate the issue with a working example in LinqPad. The gist includes a rudimentary implementation of temporary tables in queries.

Interestingly, if I use EF Core's native batch delete operation (ExecuteDelete), the query works as expected.

Based on my investigation, the issue seems to be related to the fact that the temporary table query representation (IQueryable<int>) is created using raw SQL and lacks related table mapping.

Exception stack trace

   at Microsoft.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen, Boolean streamAllowed) in /_/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlClient/SqlEnums.cs:line 405
   at Microsoft.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc) in /_/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlClient/SqlParameter.cs:line 1922
   at Microsoft.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters, Boolean includeReturnValue) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlCommand.cs:line 6134
   at Microsoft.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlCommand.cs:line 6003
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlCommand.cs:line 4922
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlCommand.cs:line 4663
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlCommand.cs:line 4544
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteScalar() in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlCommand.cs:line 1127
   at Z.EntityFramework.Extensions.BatchDelete.<>c.(DbCommand )
   at Z.EntityFramework.Extensions.BatchDelete.Execute[T](IQueryable`1 query)
   at BatchDeleteExtensions.DeleteFromQuery[T](IQueryable`1 query, Action`1 batchDeleteBuilder)
   at BatchDeleteExtensions.`1.()
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)

[JonathanMagnan]

Hello @deimastep,

Could using WhereBulkContains be an alternative solution for you?
This method is compatible with DeleteFromQuery and essentially does what your temporary table solution does.

Best Regards,

Jon

[deimastep]

Hi @JonathanMagnan,

Thank you for your advice!

The WhereBulkContains feature would partially address my case, but:

1. It only partially solves the issue because the actual use case is much broader. The example I provided above is simplified for clarity.
2. WhereBulkContains is a paid feature, and unfortunately, I cannot use it in the current project due to budget constraints.

P.S. I have extensively used the paid version of this library in an EF Classic legacy project, leveraging all its bulk operation features, including WhereBulkContains.

For now, I am facing a blocker with my current solution. I will explore alternative ways to overcome this issue.

Thank you!

[JonathanMagnan]

Hello @deimastep ,

Indeed, this is a paid feature. Unfortunately, in this case, there’s not much we can do on our side.

Maybe the ExecuteDelete method introduced in EF Core 7 could help? It might be worth trying to see if it has the same limitations as ours or not.

Best Regards,

Jon

[deimastep]

Hi,

I mentioned in the issue report that I tested with EF Core 8 using ExecuteDelete, and it works without any issues.

But unfortunately there is no native EF Core support for something like InsertFromQuery(), despite there being Delete/Update analogs for batch operations introduced starting EF Core 7.

Additionally I tested with linq2db Insert()/Delete() and it works as well.

So, it seems that this issue is very specific to the EntityFramework Plus library.

[JonathanMagnan]

Hello @deimastep,

Thank you for the additional information. We are currently looking into the linq2db source to understand what they are doing with this object[] parameter value, to see if we could handle it the same way. If we ignore the parameter, everything works — but that’s not necessarily the right solution.

Best Regards,

Jon

[JonathanMagnan]

Hello @deimastep ,

Have you done something special for linq2db? We are currently trying to see how they handle this special parameter on their side but when trying the following code:

var count = await db.Set<ServiceOrder>()
	.Join(ordersIdsQuery, x => x.Id, x => x, (x, _) => x)
	.SelectMany(x => x.BillingEvents, (_, x) => x)
	.DeleteAsync(ct);

We receive the following error message: LinqToDB.LinqToDBException: 'LinqToDB method 'Delete' called on non-LinqToDB IQueryable.'

Best Regards,

Jon

[JonathanMagnan]

Hello @deimastep,

Since our last conversation, we haven't heard from you.

Let me know if you need more information.

Best regards,

Jon

[deimastep]

Hello @JonathanMagnan,

That is strange, because the exact same query executes successfully in my environment.
I am even able to run more complex code, such as:

var orderIds = [...];
var ordersIdsQuery = await db.GetTempTableIdQuery("#orders", orderIds, ct);
var billingEventIdsQuery = await db.GetTemporaryTableIdQuery("#events", ct);
var count = await db.Set<ServiceOrder>()
    .Join(ordersIdsQuery, x => x.Id, x => x, (x, _) => x)
    .SelectMany(x => x.BillingEvents, (_, x) => new IntegerId { Id = x.Id })
    .InsertAsync(
        db.CreateLinqToDBConnection().GetTable<IntegerId>().TableName("#events"),
        x => x,
        ct);

count = await db.Set<BillingEvent>()
    .Join(billingEventIdsQuery, x => x.Id, x => x, (x, _) => x)
    .DeleteAsync(ct);

public class IntegerId { public int Id { get; set; } }

1. The first database command inserts billing event IDs into the #events table by filtering service orders and joining with the #orders table.
2. The second database command deletes billing events filtered by the #events table.