Enhancement: Identity/Primary key confusion.

[Swoorup]

Given the following schema in SQLITE:

create table if not exists [es_events]
(
  -- id of the event
  id              text                        not null,
  -- sequence/position of the event in the entire event store
  position        integer primary key AUTOINCREMENT,
  correlation_id  text                        not null,
  causation_id    text                        not null,
  stream_key      varchar(64)                 not null,
  version         integer                     not null check (version >= 0),
  name            varchar(40)                 not null,
  payload         text                       not null,
  metadata        text,
  schema_revision integer                     not null check (schema_revision >= 0),
  timestamp       timestamptz default CURRENT_TIMESTAMP not null,
  constraint pk_es_events_stream_and_version unique (stream_key, version),
  constraint pk_es_events_id_unique unique (id)
);

And if the write POCO is as follows:

    { id: Guid
      correlation_id: Guid
      causation_id: Guid
      stream_key: string
      version: int
      name: string
      payload: string
      metadata: string
      schema_revision: int }

Note that the position is omitted, after v1.12.0 it appears that Insert op using the above write model no longer works. The user is presented with the message:

The non-identity primary field must be present during insert operation

Upon adding position such as position: Nullable<int64> to the write model, we can write again. And if the autoincrement is desired, the value should simply be set to null. However nothing is written back to the write model for the position field as it is not an identity field.

This seems bit counterintuitive to simply pass a Null value and also not have anything returned back to the model for that specific property?

Seeing as not all database support identity fields including some older version of postgres (<10). I think we should rather let the user specify which fields must be returned after insertion?

I am unsure what should the default behaviour be, hence thinking instead always update the primary key? and other fields to update are specified instead?