locked
datatype for keys(primal and foreign) in EntityFramework RRS feed

  • Question

  • User1655654435 posted

    I'm developing an application in .Net Core and using code first.

    When I set the primal key as int, whenever I create a new entity in my app the number automatically increases from 1,2,3(maybe 4-9) then suddenly to 1001, 1002, etc

    Why is this happening? If I set my keys to long would this change the effect (i've seen someone using long instead)?

    Another question: I have two tables that I don't want to have equal numbers as primary keys. Meaning, one table has 1,2,3 etc, then the other now has the same. But I want the other table to have different numbers than the first for primal keys because in my code I need to use asp-route-id for each table at the same page, so they must not have equal values. Any ideas on this?

    thanks for any answersSmile

    Monday, June 5, 2017 8:06 AM

Answers

  • User753101303 posted

    Hi,

    More likely SQL Server restarted. The doc for https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property explicitely states that you can have gaps. Scroll down to "Consecutive values after server restart or other failures"

    Not 100% sure but I believe SQL Server 2016 or 2017 have something to mitigate this (though it shouldn't be a real problem).

    What do they have in common? A possible approach would be to have a single table for keys and common data and specialized tables reusing those keys and exposing additional data. See perhaps https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/implementing-inheritance-with-the-entity-framework-in-an-asp-net-mvc-application

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 6, 2017 8:09 AM
  • User-271186128 posted

    Hi bluMarmalade,

    When I set the primal key as int, whenever I create a new entity in my app the number automatically increases from 1,2,3(maybe 4-9) then suddenly to 1001, 1002, etc

    I haven’t met your problem, could you please post your Model classes or other relative code? Therefore, I could help you further and effectively.

    But I want the other table to have different numbers than the first for primal keys because in my code I need to use asp-route-id for each table at the same page, so they must not have equal values. Any ideas on this?

    Please add the following code to the Configure method of Startup.cs:

    using (var context = (DbContext)app.ApplicationServices.GetService<DbContext>())
                {
                    if (env.IsDevelopment())
                    {
                        context.Database.ExecuteSqlCommand("DBCC CHECKIDENT('[your Table name]', RESEED, 1000);"); //the primary key will be start from 1000
    
                        context.SaveChanges();
                    }
                }
    

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 6, 2017 9:29 AM

All replies

  • User753101303 posted

    Hi,

    More likely SQL Server restarted. The doc for https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property explicitely states that you can have gaps. Scroll down to "Consecutive values after server restart or other failures"

    Not 100% sure but I believe SQL Server 2016 or 2017 have something to mitigate this (though it shouldn't be a real problem).

    What do they have in common? A possible approach would be to have a single table for keys and common data and specialized tables reusing those keys and exposing additional data. See perhaps https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/implementing-inheritance-with-the-entity-framework-in-an-asp-net-mvc-application

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 6, 2017 8:09 AM
  • User-271186128 posted

    Hi bluMarmalade,

    When I set the primal key as int, whenever I create a new entity in my app the number automatically increases from 1,2,3(maybe 4-9) then suddenly to 1001, 1002, etc

    I haven’t met your problem, could you please post your Model classes or other relative code? Therefore, I could help you further and effectively.

    But I want the other table to have different numbers than the first for primal keys because in my code I need to use asp-route-id for each table at the same page, so they must not have equal values. Any ideas on this?

    Please add the following code to the Configure method of Startup.cs:

    using (var context = (DbContext)app.ApplicationServices.GetService<DbContext>())
                {
                    if (env.IsDevelopment())
                    {
                        context.Database.ExecuteSqlCommand("DBCC CHECKIDENT('[your Table name]', RESEED, 1000);"); //the primary key will be start from 1000
    
                        context.SaveChanges();
                    }
                }
    

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 6, 2017 9:29 AM