Cannot insert the value NULL into column 'ID' even the column is Identity

I am facing with a issue, SQL server are not updating the Identity and [KEY] marked column automatically.

The column is [KEY and IDENTITY marked, but seems like not updating auto.

Full Error:

System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for
details. —> System.Data.Entity.Core.UpdateException: An error
occurred while updating the entries. See the inner exception for
details. —> System.Data.SqlClient.SqlException (0x80131904):
Cannot insert the value NULL into column ‘ID’, table
‘WexusBot.dbo.wexusUsers’; column does not allow nulls. INSERT
faiUnhandled exception. ls. The statement has been terminated.\

That’s how I add user to context:

  context.Users.Add(new User
        {
            Username = username,
            PremiumExpiresAt = expires,
            RegisteredAt = DateTime.Now,
        });
        await context.SaveChangesAsync();

using System.ComponentModel.DataAnnotations;
...
public class User
{
    [Key] // As seen, it's ID marked
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)] // and even marked with this
    public int ID { get; set; }

    [Index(IsUnique = true)] public long TUID { get; set; }

    [DefaultValue("unknown")] public string Username { get; set; }

    public DateTime RegisteredAt { get; set; }
    public Role Role { get; set; }

// etc...

}

I can set ID to Identity in SSMS, but I must to update the database after every new migration with this method!

There’s no issues about sql connection.

Reproducure

Create a class like this (it’s my own class for ex)

public class User
{

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

    [Index(IsUnique = true)] public long TUID { get; set; }

    [DefaultValue("unknown")] public string Username { get; set; }

    [NotNull] public DateTime PremiumExpiresAt { get; set; }

    public DateTime RegisteredAt { get; set; }
    public Role Role { get; set; }

    public bool IsPremium()
    {
        var user = Database.GetUser(TUID);
        return user.Role >= Role.premium;
    }
}

add-migration redproducible && update-database

add-migration test
update-database

Try to add a new user class to context

context.Users.Add(new WUser
{
            TUID = telegramId,
            Username = username,
            Role = Role.free, 
            // ETC. Just do not touch to ID as it needs to be updated auto
});

Then flush context to database with
await context.SaveChangesAsync();

Extra info: I’m on net 6.0 and using SQL Server.

>Solution :

First, I need to mention that you don’t have to use [Key] attribute if you use property name "ID" or "Id". It will be automatically treated as [Key].

Second, making ID Nullable is not possible because ID should be Unique (and, two records with NULL’s as the ID are not unique). And why ID should be unique? Because SQL Server needs to apply the idea of Clustered Index (i.e. physical records ordering).

If you need some kind of Identifier to be NULLable then it should not be the ID column. ID is particularly important for making linking between tables possible via Foreign Keys. Thus, you just have to tread Id/Key as a "System" level thing. And instead, use some other logical column as something you want to be Nullable.

Now, about migration: there are several ways to implement migrations. There are Entity Framework migration toolkit. There are libraries like DbUpMigrator that allows more transparent SQL way of doing migrations. I have listed some examples in my github Database-migration-examples-using-.NET, hope it will help a bit.