0

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:

  1. Open my edmx file.

  2. 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.

skylake
  • 409
  • 2
  • 9
  • 24
  • 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 Sep 26 '16 at 20:37
  • 1
    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 Sep 26 '16 at 20:41
  • in order to have it work, change StoreGeneratedPattern to None and set valid Guid values, or change the datatype. – DevilSuichiro Sep 26 '16 at 20:43
  • @JimMcKeon Unfortunately I misspelled it in this post. Corrected it now. – skylake Sep 26 '16 at 20:45
  • @DevilSuichiro How do I set valid Guid values? I barely have any experience with Guid. EDIT: How come User table could get unique Guid values every time? – skylake Sep 26 '16 at 20:47
  • 1
    `How do I set valid Guid values` -> `modelInstance.GuidIdProperty = System.Guid.NewGuid();` – Igor Sep 26 '16 at 20:51
  • @igor Where do I put that code? – skylake Sep 26 '16 at 20:54
  • 1
    @DevilSuichiro Sure you can use `DatabaseGeneratedOption.Identity`. Skylake, I think the database table `Profile` doesn't have the default (`DEFAULT newsequentialid()`) – Gert Arnold Sep 26 '16 at 21:17
  • Do not add "Solved" in the question. Either add an answer explained how you solved it so its useful to others, or delete the question. –  Sep 26 '16 at 22:10
  • @StephenMuecke Aah.. Will do, thanks. – skylake Sep 26 '16 at 22:53

1 Answers1

1

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.

skylake
  • 409
  • 2
  • 9
  • 24