DatabaseGeneratedOption.Identity not generating an Id

Question

Using EntityFramework code-first, I've created a simple Foo table. Here's my entity:

public class Foo
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public virtual string Id { get; set; }

    public virtual string Name { get; set; }
}

However whenever I try to insert a new row, I get a Cannot insert the value NULL into column 'Id'. Why is this happening when I've added a DatabaseGenerated attribute? Deleting and recreating my table makes no difference.

Answer 1

I know that is an old thread, but for anyone in the future:

DatabaseGenerated(DatabaseGeneratedOption.Identity)

does not generate any values, it simply let EF know that DB suppose to generate the value as described in the MS documentation. Therefore you must either set a default value in the database, in your model or assign an actual value in the controller.

Answer 2

As Treadmeister stated, identity is working fine with string Id with at least Entity Framework Core 3+. My problem was passing an object from Web App with Id: "" which is string.Empty not null so EF Core was not generating new Id

Answer 3

Identities for string column types are not supported with SQL Server. (How do you expect such a string to look like?) To get this working you could - for example - add a computed column/user defined function in SQL Server that formats a string from an ordinary int identity column - as shown here.

Answer 4

1. you forgot the Key attribute. and there is no need to use virtual for primary key.
2. as mentioned by Slauma you can't use Identity for string datatype.

Try this code:

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

    public virtual string Name { get; set; }
}

Answer 5

For anybody reading this in 2020, 'Identity' attributes for string column types are supported using Entity Framework. For example, decorating a string type property in your C# class with the [DatabaseGenerated(DatabaseGeneratedOption.Identity)] attribute tag, then letting Entity Framework create the table through a database-first migration, and inserting that record through your program with Entity Framework methods (AddAsync(), SaveAsync()), auto-generates a string that looks like e.g. 'b652daab-9bb9-5143-a1ae-97a89232ea38'.

MS SQL Server will not auto generate this value however (I trialled it with an Insert SQL statement). Instead, the program / Entity Framework seems to generate it.