Why subquery is generated for UPDATE

[PatryxCShark]

Hi, I want to ask about your example why such UPDATE generates subquery:

			context.Customers
				.Update(x => new Customer {IsActive = false, Description = "Updated"},
					   x => { x.Executing = command => commandText = command.CommandText; });

as follows:

UPDATE A 
SET A.[IsActive] = @zzz_BatchUpdate_0,
A.[Description] = @zzz_BatchUpdate_1
FROM [dbo].[Customers] AS A
INNER JOIN ( SELECT 
    [Extent1].[CustomerID] AS [CustomerID], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Description] AS [Description], 
    [Extent1].[LastLogin] AS [LastLogin], 
    [Extent1].[IsActive] AS [IsActive]
    FROM [dbo].[Customers] AS [Extent1]
           ) AS B ON A.[CustomerID] = B.[CustomerID]

Fiddle:
https://dotnetfiddle.net/a6zJUe

Why not just query (with WHERE added if required):

UPDATE A 
SET A.[IsActive] = @zzz_BatchUpdate_0,
A.[Description] = @zzz_BatchUpdate_1

Any idea what is the purpose?

What's more now I have problem with concurrency: more than one query at the same time can modify the same rows because if there is a WHERE to choose which records to update, it updates them based on old values. Any idea how to reolve this problem?

I use version: Z.EntityFramework.Plus.EFCore 2.2.13

[JonathanMagnan]

Hello @PatryxCShark ,

Improving this is something in our backlog. Unfortunately, time is missing so we never succeed yet to find time to do such a request.

We will eventually do it but we currently have more requests than we can handle so we cannot do it at this moment.

To fix your issue at this moment, the only way to solve it is using the UseTableLock = true option such as:

x => { x.Executing = command => commandText = command.CommandText; x.UseTableLock = true });

Best Regards,

Jon

---

Sponsorship Help us improve this library

Performance Libraries context.BulkInsert(list, options => options.BatchSize = 1000); Entity Framework Extensions • Bulk Operations • Dapper Plus

Runtime Evaluation Eval.Execute("x + y", new {x = 1, y = 2}); // return 3 C# Eval Function • SQL Eval Function

[PatryxCShark]

I understand @JonathanMagnan. No HoldLock and UpdLock enabled for Sql Server, just TableLock?

[JonathanMagnan]

Hello @PatryxCShark ,

The UseTableLock will add a TABLOCK hint.

You can also use the UseRowLock option that will add the ROWLOCK hint.

[PatryxCShark]

UseRowLock doeas not work properly, but TABLOCK seems to be OK. If library generates without subquery: UPDATE A SET A.[IsActive] = @zzz_BatchUpdate_0, A.[Description] = @zzz_BatchUpdate_1 WHERE A.[IsActive] = 0

Will I still need to use Tablock to have guarantee that no one else will change the same records?

[JonathanMagnan]

We cannot guarantee if it will still require it or not. However, there will for sure have less chance of lock/block.

Unfortunately, simplifying the query generation to make an update like this one is currently not in our short-term plan (this request is harder than it looks and we currently have more requests than we can handle).

[PatryxCShark]

Thank you @JonathanMagnan for supporting. I appreciate it.

Good luck!