Skip to content
Advertisement

How to Insert into Identity column with c# code

Yes i know, there is alot of material out there on how to turn on identity insert and turn it off after, which is actually something i want to use. But there is more to my problem, thats why i started this question.

The conventional way to insert something into an identity column is this:

using (var connection = new SqlConnection("Connection String here"))
{
    connection.Open();
    var query = "SET IDENTITY_INSERT dbo.MyTable ON; INSERT INTO dbo.MyTable (IdentityColumn) VALUES (@identityColumnValue); SET IDENTITY_INSERT dbo.MyTable OFF;";
    using (var command = new SqlCommand(query, connection)
    {
        command.Parameters.AddWithValue("@identityColumnValue", 3);
        command.ExecuteNonQuery();
    }
}

I have 2 Questions about this:

  • do i need to use this “dbo.”? Im currently working with the localdb from VS, but the project will be deployed on a real server once its finished

  • MAIN PROBLEM: I want to insert a complete object, not just the value for the identity column. The object has a given ID, which in the db is the PK. And it has a string value, which has to be insert too.

How can i do that? I am looking for something like:

Context.Database.Add(myObject);
*Sql string with c# to turn on identity insert*
context.database.SaveChanges();
*Sql string with c# to turn off identity insert*

This is my Model, so you can understand the question better:

public class myObject

        [Key]
        public int Id { get; set; }

        public string Position { get; set; } 

Advertisement

Answer

So after some conversation (big shoutout to @SteveTodd) i managed to get what i wanted.

Explanation: I wanted to insert my own primary keys (for various reasons). I did know that you could turn off the Identity temporarily, but the solution is to basically turn it off entirely.

Another Question that helped me solve and understand this problem:

Entering keys manually with Entity Framework

In my case it now looks like this:

public class myObject
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)]
    int TokenId { get; set; }
}

TLDR: Add this between the [Key] and your Field:

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)]
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement