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

[neil...@gmail.com]

Hi,

Stumbled into another error. I have a model that looks like this:

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

[MaxLength(128)]
public string CreatedBy { get; set; }

[MaxLength(40)]
public string DisplayName { get; set; }

public LinkedAccountType AccountType { get; set; }
}

That translated into the following SQL after migrations:

CREATE TABLE dbo."LinkedAccounts"
(
"Id" uuid NOT NULL DEFAULT uuid_generate_v4(),
"CreatedBy" character varying(128),
"DisplayName" character varying(40),
"AccountType" integer NOT NULL DEFAULT 0,
CONSTRAINT "PK_dbo.LinkedAccounts" PRIMARY KEY ("Id")
)
WITH (
OIDS=FALSE
);

When I try to add a new row like this:

ApplicationDbContext.LinkedAccounts.Add(new LinkedAccount
{
CreatedBy = User.Identity.GetUserId(),
AccountType = LinkedAccountType.Store,
DisplayName = accountInfo.DisplayName
});

ApplicationDbContext.SaveChanges();

An exception is thrown:

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

I've been hammering at this for 5 hours now. At first, I was not able to update-database (with error "uuid_generate_v4() was not found") despite the fact that uuid-ossp was installed with 'CREATE EXTENSION "uuid-ossp" WITH SCHEMA dbo;'. Only after re-creating the "public" schema and then doing 'CREATE EXTENSION "uuid-ossp";' did the uuid_generate_v4() function start to work. Doing a "SELECT uuid_generate_v4();" shows that proper UUIDs are being generated, but when inserting a row through Entity Framework, only 0's are produced for the Id column. Getting rid of the [DatabaseGenerated(...)] attribute allows one row to be filled in with a UUID of all 0's.

I can maybe get around this by producing GUIDs in code (Guid.NewGuid()), but I would much rather prefer letting the database handle it. Any ideas?

[neil...@gmail.com]

When I get rid of [DatabaseGenerated(...)] attribute and generate the UUID in code like so:

ApplicationDbContext.LinkedAccounts.Add(new LinkedAccount
{
Id = Guid.NewGuid(),
CreatedBy = User.Identity.GetUserId(),
AccountType = LinkedAccountType.Amazon,
DisplayName = accountInfo.DisplayName
});

I get the error: "The model backing the 'ApplicationDbContext' context has changed since the database was created. Consider using Code First Migrations to update the database."

I am already using Code First migrations with -ContextTypeName set to StoreFront.Models.ApplicationDbContext (I even enabled migrations again with the -force argument). When I add-migrations, it shows that nothing has changed with my database (meaning ApplicationDbContext should be the same?) Anyway, I got around this by adding the following line to Application_Start():

Database.SetInitializer<ApplicationDbContext>(null);

And now I can insert rows into the database. However, this patchy/hacky solution is far from ideal. Any ideas?

[Francisco Figueiredo Jr.]

Hi, Neil!

Which Npgsql version are you using? Master from git or 2.1.3?

David added support for guid in database migrations with this pull request: https://github.com/npgsql/Npgsql/pull/91

Note that when the field is identity and of type guid, it will use the uuid_generate function:

AddStatment("select * from uuid_generate_v4()");

sql.Append("uuid_generate_v4()");

Can you check the sql used in the insert? It should contain something like "returning Id" or something like that with the guid created. 

I will need to check the code, because I remember I talked with David about this uuid_generate_v4() not being available in all servers and this could give problems for users trying to use it.

I just don't remember right now if we decided to let the value returning an empty guid (which would explain why you are receiving only 0's) or if we let the function call and the user would handle the installation of the uuid_generate_v4() function.

I hope it helps.

[neil...@gmail.com]

Hi Francisco,

I am using Npgsql.EntityFramework from git and Npgsql 2.1.3 from Nuget. The uuid_generate_v4() caused problems on our machine, so what I did was remove the function dependency for uuid_generate_v4() and generated the UUID using the .NET framework. The System.Guid.NewGuid() function implements RFC 4122 standards (except .NET trades in little-endian bytes for the first three Guid parts). It generates v4 UUIDs by calling CoCreateGuid() on ole32.dll which calls the UuidCreate RPC function, which generates a v4 UUID.

"Use CoCreateGuid when you need an absolutely unique number that you will use as a persistent identifier in a distributed environment. To a very high degree of certainty, this function returns a unique value – no other invocation, on the same or any other system (networked or not), should return the same value."

Thus, seeing how System.Guid.NewGuid() uses CoCreateGuid(), it may be reasonable to remove the uuid_generate_v4() dependency and use .NET's GUID generating function in its place. Some links:

SQL Server's NEWID() function itself uses CoCreateGuid():
http://blogs.msdn.com/b/sqlprogrammability/archive/2006/03/23/559061.aspx

A helpful comparison:
http://www.postgresonline.com/journal/archives/179-Universal-Unique-Identifiers-PostgreSQL-SQL-Server-Compare.html

Here's a great article explaining the concepts:
http://blogs.msdn.com/b/oldnewthing/archive/2008/06/27/8659071.aspx

MSDN:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms688568(v=vs.85).aspx

In short: it is possible to use System.Guid.NewGuid() in place of uuid_generate_v4() if uuid_generate_v4() is not available on the system.