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.

Have you marked the column as an identity column in code somehow? https://stackoverflow.com/q/33946212/34092 — mjwills, Jun 29 '23 at 22:23
`[AllowNull]` literally doesn't make sense on a column that you *know* doesn't allow null in it. — mjwills, Jun 29 '23 at 22:24
@mjwills hey, thanks for response. Yes i have also tried `[DatabaseGenerated(DatabaseGeneratedOption.Identity)]` but not worked. Even it is broked the `Primary` feature in the column. When i add that, `ID` column not being primary anymore... https://imgur.com/a/2amiD9L — itsReinsy, Jun 29 '23 at 22:31
Please update your question with a [mcve] where you specified identity. — mjwills, Jun 29 '23 at 22:34

score 0 · Accepted Answer · edited Jun 29 '23 at 22:45

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.

> Second, making ID Nullable is not possible because ID should be Unique (and, two records with NULL's as the ID are not unique) that made me check if SQL is running well... and i noticed my changes are not mirroring to database fully. I had to delete-database and re-create it, and it's fixed. I was saying why SQL server is that dumbass thinking im gonna put a null value to identity column... and that was the answer. If anyone getting this error, same as me. its probably a broken database issue. Thanks yall. — itsReinsy, Jun 29 '23 at 22:59

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

1 Answers1