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