BatchUpdate column does not exist

[GeorgyRezchikov]

Here is what to include in your request to make sure we implement a solution as quickly as possible.

Description

I am trying to update multiple fields using a condition

await _context.AgentReportDocuments
                        .Where(ard=>ard.Id==agentReportDocumentId)
                        .UpdateAsync(ard => new AgentReportDocument(){
                            AgentReportDocumentStateId = state,
                            DateUpdate = DateTimeOffset.Now,
                            CommonError = error},cancellationToken: ct);`

Generated SQL query:

UPDATE "agent_report_document"
SET "agent_report_document_state_id" = @zzz_BatchUpdate_0,
"date_update" = @zzz_BatchUpdate_1,
"common_error" = @zzz_BatchUpdate_2
WHERE EXISTS ( SELECT 1 FROM (SELECT a.id, a.agent_document_report_schedule_info_id, a.agent_report_document_state_id, a.common_error, a.contract_id, a.date_create, a.date_from, a.date_to, a.date_update, a.document_period, a.document_type_id, a.is_send_to_m
etazon, a.metazon_send_date, a.metazon_send_error, a.nds, a.nds_fee, a.total_fee, a.total_price
FROM agent_report_document AS a
WHERE a.id = @__agentReportDocumentId_0) B
               WHERE "agent_report_document"."id" = B."id"
           )

I am trying to do a projection to reduce data reading

await _context.AgentReportDocuments
                        .Select(ard => new AgentReportDocument{Id=ard.Id  })
                        .Where(ard=>ard.Id==agentReportDocumentId)
                        .UpdateAsync(ard => new AgentReportDocument{
                            AgentReportDocumentStateId = state,
                            DateUpdate = DateTimeOffset.Now,
                            CommonError = error},cancellationToken: ct);

Generated SQL query:

UPDATE "agent_report_document"
SET "agent_report_document_state_id" = @zzz_BatchUpdate_0,
"date_update" = @zzz_BatchUpdate_1,
"common_error" = @zzz_BatchUpdate_2
WHERE EXISTS ( SELECT 1 FROM (SELECT a.id AS "Id"
FROM agent_report_document AS a
WHERE a.id = @__agentReportDocumentId_0) B
               WHERE "agent_report_document"."id" = B."id"
           )

Exception

I receive an error like below.

42703: column b.id does not exist

Is it possible to somehow reduce the reading of data?

Further technical details

• EF version: Microsoft.EntityFrameworkCore 5.0.1
• Z version: Z.EntityFramework.Plus.EFCore 5.1.33
• Database Provider: Npgsql.EntityFrameworkCore.PostgreSQL 5.0.1

[JonathanMagnan]

Hello @GeorgyRezchikov ,

Thank you for reporting,

We will look if that's possible to do something or not.

At first sight, the issue happens due to case sensibility when selecting the column. The alias "Id" is used instead of the column name id. I'm not sure if we are aware of the alias name used for the primary key but we will look at it.

Meanwhile, you already know how to make it works.

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

[JonathanMagnan]

Hello @GeorgyRezchikov ,

Unfortunately, for now, we choose to not yet support this scenario due to the complexity (we didn't find any good solution in the delay we had)

However, we will certainly try again in the future since that's a scenario we would like to support.