Save entity not return primary key in transaction

Posts   
 
    
Posts: 14
Joined: 10-Dec-2015
# Posted on: 24-Jan-2017 08:54:45   

Hello. I'm use LLBLGen 5.0, with PostgreSQL 9.4. Self-Servicing. Example entity:

CREATE TABLE test (
  id bigint PRIMARY KEY NOT NULL DEFAULT new_id()
);

new_id() generate bigint unique ID. When i'm create entity:

...
var entity = new TestEntity();
...
entity.Save(transaction);
// after save entity.Id = 0

After save entity.Id not set. Any ideas how to solve this promblem? Today, I'm use ActionProcedure to solve.


long id = 0;
ActionProcedures.NewId(ref id);
..
entity.Id = id;
..
entity.Save(transaction);

But this way generate one extra query

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 24-Jan-2017 09:18:54   

You need to use a sequence, not a stored proc. So if you define a sequence in postgres, and then assign that sequence to the mapping of the Id field in the entity editor, regenerate the code you'll see it will assign the proper new ID and the value to the entity field as well.

Frans Bouma | Lead developer LLBLGen Pro
Posts: 14
Joined: 10-Dec-2015
# Posted on: 24-Jan-2017 10:24:29   

Otis wrote:

You need to use a sequence, not a stored proc. So if you define a sequence in postgres, and then assign that sequence to the mapping of the Id field in the entity editor, regenerate the code you'll see it will assign the proper new ID and the value to the entity field as well.

Why you do not use RETURNING in query generation primary key?

INSERT INTO .... RETURNING id;

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 24-Jan-2017 16:10:26   

In theory it could do that, but it doesn't take defaults into account, e.g. new_id() is a default it has to know about and then create a returning clause. This isn't supported by our system (same thing is true for newid() on sqlserver, it won't return that value). Defaults are obtained in an optional refetch of the row later on, not during insert.

So the supported way is through a sequence.

Frans Bouma | Lead developer LLBLGen Pro