Question: Combining multiple Statements

[Xzelsius]

Hello

I know it is possible to combine multiple SELECT statements, but I have a use case where I could use some sort of combine Query for delete + insert.

In this specific use case, I need to delete all rows (1 + n) that match a certain ID, and then insert new rows again in one transaction. There are alternatives like delete only what should not be there and insert only what is missing. But in a event-sourced system this can get out of hand quickly.

Right now we do this using

Query().AsDelete().Where(new { foreign_id = e.Id })

and then loop over what we have to insert and do

Query().AsInsert(new { .... }) 

Which results in something like

operations[0]: "delete from ... where foreign_id = ..."
operations[1]: "insert into ... values (...)"
operations[2]: "insert into ... values (...)"
operations[3]: "insert into ... values (...)"

But the system underneath which executes the individual statements cannot guarantee the order of queued individual statements. So my idea is to queue one operation which contains the combined individual statements statements.

operations[0]: "delete from ... where foreign_id = ...; insert into ... values (...); insert into ... values (...); insert into ... values (...); insert into ... values (...);"

Is there any support or recommended way to do this?

[Xzelsius]

I can always just combine the individual statements like this:

PostgresCompiler compiler = new();

Query deleteStatement = new Query(...).AsDelete().Where(new { foreign_id = e.Id })
Query insertStatement = new Query(...).AsInsert(new { .... }) 

SqlResult deleteResult = compiler.Compile(deleteStatement);
SqlResult insertResult = compiler.Compile(insertStatement);

string sql = deleteResult.RawSql + ";" + insertResult.RawSql;
object[] parameters = deleteResult.Bindings.Concat(insertResult.Bindings).ToArray() 

But I am not sure if this is safe enough to do.