SqlException: Cannot insert explicit value for identity column in table 'AspNetUsers' when IDENTITY_INSERT is set to OFF

Question

I have changed aspnetuser table column datatype string to long using enable-migration of Microsoft.AspNet.Identity.

Table successfully created using following script of migration :

CreateTable(
"dbo.AspNetUsers",
c => new
{
Id = c.Long(nullable:false,identity:true),
Email = c.String(maxLength: 256),
EmailConfirmed = c.Boolean(nullable: false),
PasswordHash = c.String(),
SecurityStamp = c.String(),
PhoneNumber = c.String(),
PhoneNumberConfirmed = c.Boolean(nullable: false),
TwoFactorEnabled = c.Boolean(nullable: false),
LockoutEndDateUtc = c.DateTime(),
LockoutEnabled = c.Boolean(nullable: false),
AccessFailedCount = c.Int(nullable: false),
UserName = c.String(nullable: false, maxLength: 256),
FullName = c.String(nullable: false, maxLength: 50),
CreatedBy = c.Long(nullable: true),
CreatedDate = c.DateTime(),
ModifiedDate = c.DateTime(nullable: false, defaultValue: null, defaultValueSql: null),
ModifiedBy = c.Long(nullable: true),
IsDeleted = c.Boolean(nullable: false, defaultValue: false, defaultValueSql: "0")
})
.PrimaryKey(t => t.Id)
.Index(t => t.UserName, unique: true, name: "UserNameIndex");

But when i try to create user using following code getting error as "SqlException: Cannot insert explicit value for identity column in table 'AspNetUsers' when IDENTITY_INSERT is set to OFF".

// first we create Admin rool
var role = new Microsoft.AspNet.Identity.EntityFramework.IdentityRole();
role.Name = SuperAdminRole;
roleManager.Create(role);

//Here we create a Admin super user who will maintain the website               

var user = new ApplicationUser();
user.UserName = "Admin";
user.Email = "[email protected]";
user.FullName = "xyz";
user.IsDeleted = false;
user.ModifiedBy = null;
user.ModifiedDate = null;
user.CreatedBy = string.Empty;
user.CreatedDate = DateTime.Now;

string userPWD = "admin_123";
var chkUser = UserManager.Create(user, userPWD);

If anybody have solution for this please share.

Thanks for quick response!

Can you verify what is the datatype of "Id" property in ApplicationUser class? — Chetan
@ChetanRanpariya Sorry for late response, Id property is string in application user even though created column int datatype in database. — Decimal

giri-webdev giri-webdev · Accepted Answer · 2018-05-15T06:24:59

I believe that IDENTITY_INSERT is auto increment functionality and it is set to OFF. So, verify the field 'Id' is identity column in the sql database and add the below attribute in the 'Id' column in the code first entity framework

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }

SqlException: Cannot insert explicit value for identity column in table 'AspNetUsers' when IDENTITY_INSERT is set to OFF

1 Answers