Repository navigation
Batch Operations (InsertFromQuery/UpdateFromQuery/DeleteFromQuery) fail SQL translation #830
Description
Activity
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
Hi @JonathanMagnan,
Thank you for your advice!
The
WhereBulkContainsfeature would partially address my case, but:- It only partially solves the issue because the actual use case is much broader. The example I provided above is simplified for clarity.
WhereBulkContainsis 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!
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
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.
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
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
Hello @deimastep,
Since our last conversation, we haven't heard from you.
Let me know if you need more information.
Best regards,
Jon
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; } }
- The first database command inserts billing event IDs into the
#eventstable by filtering service orders and joining with the#orderstable. - The second database command deletes billing events filtered by the
#eventstable.
- The first database command inserts billing event IDs into the
I'm experiencing the same problem, and was trying to debug the code locally to troubleshoot it. But the master branch doesn't compile. 14 compile errors, among them:
The type or namespace name 'PublicExtensions' does not exist in the namespace 'Z.EntityFramework.Extensions' (are you missing an assembly reference?)
I tried both latest commit (from 2025-10-08) tag 9.104.0.1 and tag 8.103.6.2.Or that is perhaps intentional? You are perhaps not allowing everyone full access to the code. Understandably in such case.
Hello @marnilss,
The full source on GitHub has been updated. We only host there a copy of the real source, and it hadn’t been fully updated for a while, which is why this happened.
I just tested it, and everything now builds correctly if you want to troubleshoot this locally.
Best Regards,
Jon
Hello @marnilss,
The full source on GitHub has been updated. We only host there a copy of the real source, and it hadn’t been fully updated for a while, which is why this happened.
I just tested it, and everything now builds correctly if you want to troubleshoot this locally.
Best Regards,
Jon
Thank you very much!
I was able to compile locally and debug.
AND I didn't get any exception anymore! It worked flawlessly.I'm don't know if it was solved by any change in your code, because I changed a lot in my code in between experiencing the problem and when trying with the locally built package.
Hello @marnilss ,
I don't think it was fixed but I could be wrong on this 😅 Sometimes we forget we fixed something hehe
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 asIQueryable<int>, theDeleteFromQuery()operation fails with the following error: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:
The temporary table
#ordersis created and populated with integer IDs using bulk copy. I then join thisordersIdsQuerywith my entity query to filter records by their primary integer ID:This query should translate into SQL similar to:
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