Cannot insert the value NULL into column 'Id' (Database first)

Question

Database-first solution.

I'm using two tables called User and Profile. They are both using uniqueidentifier (SQL Server) as their primary key. The way I let User to automatically receive it's own guid id was from:

Open my edmx file.
Right click the Guid column for User -> Properties -> change the StoreGeneratedPattern to Identity.

Worked like a charm. Now I tried to do same with Review, but I'm getting the following error every time I'm trying to create a new row:

Cannot insert the value NULL into column 'Id', table 'xxxx.dbo.Profile'; column does not allow nulls. INSERT fails. The statement has been terminated.

If I don't use Identity for StoreGeneratedPattern for Profile, it will not generate a unique ID, instead just bunch of zeros which lead to duplicated error if I try to create a new row again.

Why is User working fine but not Profile?

I followed this guide guide for both tables.

Your error message references a table named "Profil", not "Profile". Is it possible you misspelled/named the table or class that it references? — ssis_ssiSucks
You CANNOT use DatabaseGeneratedOption.Identity for uniqueidentifier (Guid) columns. Guid columns in DBMS will never be autoincrement, and since you changed StoreGeneratedPattern to Identity, EF won't pass it as a value either, since it expects it to be given by DBMS. — DevilSuichiro
in order to have it work, change StoreGeneratedPattern to None and set valid Guid values, or change the datatype. — DevilSuichiro
How do I set valid Guid values -> modelInstance.GuidIdProperty = System.Guid.NewGuid(); — Igor
@DevilSuichiro Sure you can use DatabaseGeneratedOption.Identity. Skylake, I think the database table Profile doesn't have the default (DEFAULT newsequentialid()) — Gert Arnold

skylake skylake · Accepted Answer · 2016-09-26T23:06:59

As @Gert Arnold mentioned in comments, I forgot to add newsequentialid() for Profiletable in SQL Server. Once I did that, I were finally able to generate unique Guids with DatabaseGeneratedOption.Identity.

You can read more about this method here. This also include techniques for Code-First.

Cannot insert the value NULL into column 'Id' (Database first)

1 Answers