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)]