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

Question

I have asp.net mvc4 application, SQL Server 2012 database, EF & Linq by connect to database. I was changed in my database some tables from one schema to another, then give the following error, when to try to add new object into database:

Cannot insert explicit value for identity column in table 'Students' when IDENTITY_INSERT is set to OFF.

I found the solution for this< is to set by code following lines:

SET IDENTITY_INSERT Students ON

Insert Into Students(StudentId,StudentName) values(4, “TestName″)

SET IDENTITY_INSERT Students OFF

But I dont know how can I do this by linq. My StudentId property is identity and auto increment. I didnt use .edmx and on the register method only context, smth like next:

Context.Students.Add(NewUser);
Context.SaveChanges();

I was try to add next line before StudentId but have no changes

[System.Data.Linq.Mapping.Column(Name = "StudentId", IsPrimaryKey = true, IsDbGenerated = true)]

Does anybody have any ideas what I should to do, because I spent a lot of time and still didnt found an solution. Or does have any property for unbind the StudentId by Inserting from my context?

Edit 1

using (Context Context = new Context(ConfigurationManager.ConnectionStrings[0].ConnectionString))
        {
            if (Context.Students.Where(Usr => Usr.Username == username).Any())
            {
                status = MembershipCreateStatus.DuplicateUserName;
                return null;
            }

            if (Context.Students.Where(Usr => Usr.Email == email).Any())
            {
                status = MembershipCreateStatus.DuplicateEmail;
                return null;
            }

            Student NewUser = new Student
            {
                StudentId = null,
                Username = username,
                Password = HashedPassword,
                IsApproved = isApproved,
                Email = email,
                CreateDate = DateTime.UtcNow,
                LastPasswordChangedDate = DateTime.UtcNow,
                PasswordFailuresSinceLastSuccess = 0,
                LastLoginDate = DateTime.UtcNow,
                LastActivityDate = DateTime.UtcNow,
                LastLockoutDate = DateTime.UtcNow,
                IsLockedOut = false,
                LastPasswordFailureDate = DateTime.UtcNow
            };

            Context.Students.Add(NewUser);
            //Context.ExecuteStoreCommand
            Context.SaveChanges();
            status = MembershipCreateStatus.Success;
            return new MembershipUser(System.Web.Security.Membership.Provider.Name, NewUser.Username, NewUser.StudentId, NewUser.Email, null, null, NewUser.IsApproved, NewUser.IsLockedOut, NewUser.CreateDate.Value, NewUser.LastLoginDate.Value, NewUser.LastActivityDate.Value, NewUser.LastPasswordChangedDate.Value, NewUser.LastLockoutDate.Value);
        }

Why are you wanting to manually insert/generate your primary keys? — Tommy
Just a guess, but are you defining your "NewUser" object with an Id? If so that's the problem. As long as you leave your NewUser.StudentId = null (likely the default behaviour) it should "just work". — user773423
Yes, by default sendind 0, and i was tryed to set up null value, it give me an error, because in some part of code I need non-nullable values — BorHunter
@BorHunter - I guess I am saying I don't know what your issue is. It looks like you want to create your own primary key and send that, but you have the database set to generate if for you (a good thing). So, I am trying to determine why you need to send the primary key over on your own. You should be able to (in LINQ), say Context.Students.Add(New Student{StudentName = "Tommy"}); without having to send a primary key. — Tommy

Tommy Tommy · Accepted Answer · 2013-12-05T22:28:18

Get rid of trying to set the StudentId when you create your new entity. You do not need to reference this during creation of the entity.

Student NewUser = new Student
            {
                Username = username,
                Password = HashedPassword,
                IsApproved = isApproved,
                Email = email,
                CreateDate = DateTime.UtcNow,
                LastPasswordChangedDate = DateTime.UtcNow,
                PasswordFailuresSinceLastSuccess = 0,
                LastLoginDate = DateTime.UtcNow,
                LastActivityDate = DateTime.UtcNow,
                LastLockoutDate = DateTime.UtcNow,
                IsLockedOut = false,
                LastPasswordFailureDate = DateTime.UtcNow
            };

            Context.Students.Add(NewUser);
            //Context.ExecuteStoreCommand
            Context.SaveChanges();

If you need the Id of the newly created object, you can get that by asking for it AFTER you have called Context.SaveChanges();

Context.SaveChanges();
var newStudentId = NewUser.StudentId;

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

1 Answers