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

Question

I am facing an issue, SQL Server is 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 failed

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 with the SQL connection.

To reproduce: 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;
    }
}

Run add-migration & 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 save data to the database with:

await context.SaveChangesAsync();

Extra info: I'm on .NET 6.0 and using SQL Server.

Answer 1

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.