How can I insert objects with my id(s)?

Question

I'm using C# 4.5.1 and Entity Framework 6.1.3.

All I need to do is I want to insert my objects primary id keys like; 1-3-5-7-9-11-13-15 etc...

I don't want to insert them using auto increment feature of ef.

Like;

       public static void SeedCategories(DataContext context)
    {
        context.Categories.AddOrUpdate(new Category
        {
            Id = 1,
            Name = "Haber",
            IsActive = true
        });

        context.SaveChanges();

        context.Categories.AddOrUpdate(new Category
                                       {
                                           Id = 5,
                                           Name = "Basic",
                                           ParentId = 1,
                                           IsActive = true
                                       },
                                       new Category
                                       {
                                           Id = 7,
                                           Name = "Medium",
                                           ParentId = 1,
                                           IsActive = true
                                       },
                                       new Category
                                       {
                                           Id = 9,
                                           Name = "Pro",
                                           ParentId = 1,
                                           IsActive = true
                                       },
                                       new Category
                                       {
                                           Id = 11,
                                           Name = "Advanced",
                                           ParentId = 1,
                                           IsActive = true
                                       },
                                       new Category
                                       {
                                           Id = 13,
                                           Name = "Ultimate",
                                           ParentId = 1,
                                           IsActive = true
                                       }
            );

        context.SaveChanges();
    }

But Entity framework inserting all those objects like;

Id = 1, Name = Haber
Id = 2, Name = Basic
Id = 3, Name = Medium
Id = 4, Name = Pro

So I need your help...

@Update: Why I need this? We are migrating our old database to new one. But for business requirements, some properties and categories will not using in new database. Such as if category property isActive = false. So I took only isActive = true ones. As summary: I have list of categories like id = 3, ( Id = 4 is isActive = false ), id = 5, id = 33, id = 410 etc.

you could just turn off autoincrement, using DatabaseGeneratedOption.None? — DevilSuichiro, Oct 20 '15 at 16:39
Make sure that your Categories table does not have an **auto-incrementing** primary key defined. This will allow you to define your own values. Alternatively you might set the Identity Increment to 2 instead of 1, which should skip every other. (Assuming MSSQL) — Todd Sprang, Oct 20 '15 at 16:40
@DevilSuichiro and I tried to add this data annotion ( DatabaseGeneratedOption.None ) to my generic T Id {get;set;} property. But it still going to auto increment it. What am I missing I don't know. — Lost_In_Library, Oct 20 '15 at 17:03
@Lost_In_Library: with this Data annotation, EF should add the ID value in its insert statements, however your database structure could not be updated yet, it should have no auto increment on this column then. however, you could use Steve Greene's suggestion, just wrap it in a using(var x=datacontext.Database.BeginTransaction()) call — DevilSuichiro, Oct 20 '15 at 17:12
Did you try this http://stackoverflow.com/questions/2793399/implementing-multilingual-web-site-using-asp-net?rq=1 — Eldho, Oct 25 '15 at 10:34

score 1 · Answer 1 · answered Oct 20 '15 at 16:08

Not sure if it is doable as code using Entity Framework or in Visual Studio, but I'm sure it is doable in SQL Server Management Studio. You can alter the Identity Specification for your primary key in SQL Server Management Studio. If you set the Identity Increment value to 2, then the Identity Seed value to 1, I would think that you will end up with your desired result without needing to specify the ID value in the code.

If you have to do it using Entity Framework code, is there no method/property that you can use to enable you to manually dictate what the primary keys are? Something like the "SET IDENTITY_INSERT" command in SQL Server?

score 1 · Answer 2 · edited May 23 '17 at 12:23

1

If this is permanent, go with the comments up above. If it is only for seeding, see this answer: How do I temporarily turn off an IDENTITY column in EF Code First?.

EDIT: Sorry, that was off the top of my head. The actual working code I use is edited.

I went with code like this:

if (!dataContext.Categories.Any())
{
var sqlInsert = @"SET IDENTITY_INSERT [dbo].[Category] ON;
   INSERT INTO Category (Id, Name, ParentId, IsActive) VALUES (5, 'Basic', 1, 1);
   INSERT INTO Category (Id, Name, ParentId, IsActive) VALUES (7, 'Medium', 1, 1);
   SET IDENTITY_INSERT [dbo].[Category] OFF;";
   dataContext.Database.ExecuteSqlCommand(sqlInsert);
}

edited May 23 '17 at 12:23

Community

1
1

answered Oct 20 '15 at 16:53

Steve Greene

12,029
1
33
54

this won't work, as Identity_Insert is only for current transaction and EF wraps every single command in its own transaction. – DevilSuichiro Oct 20 '15 at 17:04
This is not jost for seeding. But your code gave me an idea. I can make string manupulation and execute sql commands directly to create rows in db. – Lost_In_Library Oct 20 '15 at 17:05
@DevilSuichiro Are you sure ? I will try this but if ef wraps my sql commands, it will not usefull then... – Lost_In_Library Oct 20 '15 at 17:07
Sorry, I looked it up in my code and what I used is above. – Steve Greene Oct 20 '15 at 17:27

How can I insert objects with my id(s)?

2 Answers2