Postgresql: autogeneration of 'uuid' doesn't work with EntityFramework

Question

I work with 'PostgreSql' in .NET using Entity Framework (POCO approach).

My goal is to create a new record in database generating Id (uuid) on the database side and retrieve generated Id to the server (such scenario works perfectly with SQL Server).

My model looks like:

public class MyClass
{
   [Key]
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public Guid Id { get; set; }

   public string Name { get; set; }
}

In PostgreSql Column 'Id' has default value 'uuid_generate_v4()'

When I try to add a new object to the DB using EF, I Get the following error:

A null store-generated value was returned for a non-nullable member 'Id' of type...

I check SQL queries which EF sends to the PostgreSQL:

INSERT INTO "public"."MyClass"("Name") VALUES ("Test");
SELECT currval(pg_get_serial_sequence('"public"."MyClass"', 'Id')) AS "Id"

First one looks good. If I execute it, the new row is created in DB (with newly generated id) Second one returns NULL and the name of the column is 'Id bigint'. It seems, that this feature works only for numeric ids.

Is it possible to make it work for auto-generated column of 'uuid' type?

That is completely expected. The problem exists in PostgreSQL, it seems it isn't generating the a new UUID by default. However asking the database to generate a new UUID each insert defeats the point of using UUID as your primary key. The whole point of UUID is that they can be client generated. — Aron, May 19 '14 at 16:39
I assume of course you are using Database first and not Code First EF. — Aron, May 19 '14 at 16:40
I am using Code-First approach. In some cases I have exists database and I have to adjust my Models according to the DB structure (it is like DB-First approach, but I don't use designer). Some tables contain auto-generated 'uuid' primary key, thus it looks logical to use this feature. Is there workaround or some discussions when it can be fixed? — user3653175, May 19 '14 at 18:28
This question is no longer about Entity Framework and is instead about Postgres. Which i know nothing about. Post a new question on adding that to your tables. — Aron, May 19 '14 at 18:30

score 1 · Answer 1 · answered May 21 '14 at 15:20

You did not write what EF provider are you using. I presume it is npgsql.

According to latest sources it should return the Sql Erwin Brandstetter posted:

INSERT INTO public."MyClass"("Name") VALUES ("Test") RETURNING "Id";

Here are the relevant parts of sql generators: SqlInsertGenerator class and AppendReturning method

I would try the latest version of npgsql (maybe even build it myself from source).

score 0 · Answer 2 · edited May 23 '17 at 10:27

0

Simplify with the RETURNING clause:

INSERT INTO public."MyClass"("Name") VALUES ("Test") RETURNING "Id";

No separate SELECT, so no confusion about the default value of the column matching the function in your SELECT. Simpler, faster, cleaner.

More details:
PostgreSQL next value of the sequences?

edited May 23 '17 at 10:27

Community

1
1

answered May 19 '14 at 18:49

Erwin Brandstetter

605,456
145
1,078
1,228

score 0 · Answer 3 · answered May 31 '14 at 08:26

I'm assuming you are using https://github.com/npgsql/Npgsql

I'm wondering what version are you using because you are probably building your own dll from source code since https://github.com/npgsql/Npgsql/pull/91 is not part of any release yet... If you cherry-picked PR91 you should probably cherry-pick also this PR160 https://github.com/npgsql/Npgsql/pull/160 which fixes exactly your problem.

Postgresql: autogeneration of 'uuid' doesn't work with EntityFramework

3 Answers3