Request: `Insert*` methods should return the PK regardless of if the PK was generated by the DB or not

[grofit]

Currently when you call InsertAsync (for example) and the PK is non identity column (i.e UUID with default) or a PK is already provided by the entity you are inserting, the Insert method returns an empty PK value.

This change would make it so you are ALWAYS provided the PK of the entity from the Insert even if you provided it yourself or the DB generated it outside of an identity situation, this provides a more consistent experience for the library consumer. It may also be worth noting that the PK is only returned if it is an identity field run on the DB.

This was discussed in a gitter conversation with @mikependon and he was happy for it to be raised here as a request.

[mikependon]

We have assessed and made a branch changes for this. The extent of the changes for the fix on this request is quite big. It is a bit critical to make such big changes as we are to release the new stable version soon.

We will consider this one as an enhancement and have decided to have this request be a part of our next release.

[mikependon]

This is affecting the entire extensions (SQL Server, PostgreSQL, MySQL/MySQL(Connector), SQLite and also the Bulk Operations).

[mikependon]

This exception has triggered a behavioral-breaking-changes to the library which actually forces us to fix it. Before, RepoDB always consider the identity column as the primary column, and the library is actually failing in the use-case that the identity column is a completely separate than the primary column.

With the upcoming changes, we have updated the behavior that the library will always return and set-back the value only to the identity column. In the case that there is no identity column define in the entity/model, there will be "no" value to be set back to the entity/model.

However, pertaining to the issue where you actually provided an auto-generated column for the Primary column, this kind of behavior is not fix due to the fact that RepoDB is only considering to return the identity column. The scenario of having an auto-generated column for Primary is actually same scenario as the any other column with default value from the database, in which RepoDB is lacking the capability since the beginning and is considered as limitation.

To rectify this problem, you have to explicitly do a round trip call to the database, and it is a little bit hack.

[mikependon]

We will never fix your scenario due to the limitation on the behavior. We also have updated our limitation page by adding the new section named Auto-Generated Primary Column.

However, we will set this issue to "fixed" due to the fact that we have issue a fix to the scenario of identity and primary column value hydration back to the model.

The updates will be available on the next version > RepoDB v1.12.10. Please do not hesitate to revert if you have questions.