Update with Exists Causing Performance Issues

[wisedf]

Hello,

I'm experiencing performance issues when using UpdateAsync. The EXISTS clause is being included unnecessarily in the generated query, impacting performance.

Example:

string commandText = string.Empty;

var x2 = await _db.DBWrite
  .AccessLogs
  .Where(t => t.Id == 0)
  .UpdateAsync(t => new AccessLog { Ip = "1" }, x => { x.Executing = command => commandText = command.CommandText; });

Generated query: commandText

UPDATE "EventLogs" AS "e"
SET "Status" = 'INC'
WHERE EXISTS (
    SELECT 1
    FROM "EventLogs" AS "t"
    WHERE t.Id = 0 AND t.CompanyId = @companyId
);

How it should be generated:

UPDATE "EventLogs"
SET "Status" = 'INC'
WHERE Id = 0 AND CompanyId = @companyId;

Is there a way to avoid the unnecessary EXISTS clause or improve the performance of this query? Thank you!

Postgresql 15; Assembly Z.EntityFramework.Plus.EFCore, Version=6.103.6.0

[JonathanMagnan]

Hello @wisedf ,

At this moment, there is no way to improve this.

I cannot promise anything yet, but I will have a discussion with my employees at the beginning of next week to check if we want to simplify the SQL generated.

Best Regards,

Jon