SQLite SaveChanges: Use RETURNING clause

[bricelam]

SQLite 3.35.0 added the RETURNING clause to INSERT, UPDATE, and DELETE statements. Currently, the update pipeline uses SQL like this to propagate changes...

INSERT INTO "Sample" ("MaxLengthProperty", "Name", "RowVersion", "AdditionalDetails_Name",
    "AdditionalDetails_Value", "Details_Name", "Details_Value")
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6);

SELECT "Unique_No"
FROM "Sample"
WHERE changes() = 1 AND "rowid" = last_insert_rowid();

...but it could be updated to this after the SQLitePCLRaw bundle is updated.

INSERT INTO "Sample" ("MaxLengthProperty", "Name", "RowVersion", "AdditionalDetails_Name",
    "AdditionalDetails_Value", "Details_Name", "Details_Value")
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6)
RETURNING "Unique_No";

[roji]

This is just like PostgreSQL! 💘

If you want to steal the PG provider's update pipeline customizations: https://github.com/npgsql/efcore.pg/tree/main/src/EFCore.PG/Update/Internal.

[bricelam]

They've been taking a lot of inspiration from PostgreSQL lately. They borrowed their UPSERT syntax too.

[roji]

Oh nice... Hopefully we'll get around to adding UPSERT support too at some point...