Cannot UPDATE ... RETURNING or call stored procedure * in .FromSqlRaw() when entity has owned properties

[yinzara]

I would like to make a batch update of a set of rows from PostgreSQL and return the updated rows.

This is done in PostgreSQL with the UPDATE table_name SET ..... RETURNING *; clause.

I tried to use this directly in .FromSqlRaw("..."). This works great until I have an entity with owned properties, then the generated SQL breaks and it's unusable:

UPDATE scheduled_event
  SET state = 2
WHERE state = 1 AND scheduled_time < CURRENT_TIMESTAMP
RETURNING *;

And got the error:
42601: Syntax error at or near SET

I found the generated SQL was:

      SELECT p.id, p.key, p.parent, p.repeat_after, p.scheduled_time, p.state, p.topic, p.version, t.id, t.audit_info_create_time, t.audit_info_created_by, t.audit_info_update_time, t.audit_info_updated_by, t0.id, t0.value_bytes, t0.value_type_url
      FROM (
          UPDATE "scheduled_event" SET "state" = 1, "scheduled_time" = "scheduled_time" + "repeat_after" WHERE "state" = 2 AND "repeat_after" > INTERVAL '0 MINUTES' RETURNING *;
      ) AS p
      LEFT JOIN (
          SELECT s.id, s.audit_info_create_time, s.audit_info_created_by, s.audit_info_update_time, s.audit_info_updated_by
          FROM scheduled_event AS s
          INNER JOIN scheduled_event AS s0 ON s.id = s0.id
      ) AS t ON p.id = t.id
      LEFT JOIN (
          SELECT s1.id, s1.value_bytes, s1.value_type_url
          FROM scheduled_event AS s1
          INNER JOIN scheduled_event AS s2 ON s1.id = s2.id
      ) AS t0 ON p.id = t0.id

which it's creating some kind of join expression on owned properties even though no join is required (they're owned and on the same table)

If I create a stored procedure of the same action:

CREATE OR REPLACE PROCEDURE ScheduledEventsTrigger()
LANGUAGE sql
AS $proc$
  UPDATE scheduled_event
  SET state = 2
  WHERE state = 1 AND scheduled_time < CURRENT_TIMESTAMP
  RETURNING *;
$proc$;

Then execute it with .FromSqlRaw("CALL ScheduledEventsTrigger()"), I get the error:
42601: Syntax error at or near (

The generated SQL from it was:

SELECT p.id, p.key, p.parent, p.repeat_after, p.scheduled_time, p.state, p.topic, p.version, t.id, t.audit_info_create_time, t.audit_info_created_by, t.audit_info_update_time, t.audit_info_updated_by, t0.id, t0.value_bytes, t0.value_type_url
      FROM (
          CALL ScheduledEventReschedule()
      ) AS p
      LEFT JOIN (
          SELECT s.id, s.audit_info_create_time, s.audit_info_created_by, s.audit_info_update_time, s.audit_info_updated_by
          FROM scheduled_event AS s
          INNER JOIN scheduled_event AS s0 ON s.id = s0.id
      ) AS t ON p.id = t.id
      LEFT JOIN (
          SELECT s1.id, s1.value_bytes, s1.value_type_url
          FROM scheduled_event AS s1
          INNER JOIN scheduled_event AS s2 ON s1.id = s2.id
      ) AS t0 ON p.id = t0.id

This is related to both https://github.com/dotnet/efcore/issues/18299 and https://github.com/dotnet/efcore/issues/23169.

[smitpatel]

#14525

[yinzara]

I figured out a work around for my specific use case with PostgreSQL, however this issue is still valid for Stored Procedures and any raw SQL.

Instead of using a Stored Procedure, I used a Function that returns a Table. I needed to declare the same structure as the primary table, but at least its a workaround for this type of operation. (i.e. update a batch and return all changed).

I could then use it in a normal SQL expression which did properly compile and function with FromSqlRaw.

CREATE OR REPLACE FUNCTION ScheduledEventsTrigger() RETURNS TABLE (
  	id uuid,    
    state integer,
    scheduled_time timestamp with time zone,
    repeat_after interval)
  AS $$
  UPDATE scheduled_event SET state = 2 WHERE state = 1 AND scheduled_time < CURRENT_TIMESTAMP RETURNING * $$
  LANGUAGE SQL;

var results = myDbContext.Set<ScheduledEvent>().FromSqlRaw("SELECT * FROM ScheduledEventsTrigger()").ToList()

[yinzara]

For now I'm going to close this issue as a duplicate of #14525 and #21888