Auto increment primary key with Entity Framework

user_10018690 0
Viewed 2752

I have a table and I want to insert items into it without specifying the UserID. I want this ID to be generated automatically.

CREATE TABLE [dbo].[UserMaster] 
(
    [UserID]       INT           NOT NULL ,
    [UserName]     VARCHAR (50)  NULL,
    [UserPassword] VARCHAR (50)  NULL,
    [UserRoles]    VARCHAR (500) NULL,
    [UserEmailID]  VARCHAR (100) NULL,

    PRIMARY KEY CLUSTERED ([UserID] ASC)
);

Here I try to insert row via C#:

using (LessonesDBEntities context = new LessonesDBEntities())
{
    var u = new UserMaster//Make sure you have a table called UserMaster in DB
    {
        UserName = "test username",
        UserPassword = "test password",
        UserEmailID = "test email",
        UserRoles = "User"
    };

    context.UserMaster.Add(u);
    context.SaveChanges();
}

But I get exception:

Violation of PRIMARY KEY constraint 'PK__tmp_ms_x__1788CCACDB8355D2'. Cannot insert duplicate key in object 'dbo.UserMaster'. The duplicate key value is (0). The statement has been terminated.

entity-framework sql-server c#
3 Answers

Add [DatabaseGenerated(DatabaseGeneratedOption.Identity)] attribute to your identity field UserId.

public class UserMaster
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string UserPassword { get; set; }

    public string UserEmailID { get; set; }
    public string UserRoles { get; set; }
}

When your create migration and table based on that model, EF creates identity field with auto increment.

user_57898640

To Make auto-increment at Database level you should include IDENTITY in the DDL: UserID int identity(1,1) not null

(1,1) stands for UserID will start at 1 and increment by 1 for each INSERT.

user_100171820

Entity Framework doesn't know your whole database, it only knows the records you've already fetched into its context. Therefore it cannot determine the maximum used id in the given table.

You have 3 options here:

Use a feature of your db to automatically set the id, an auto-incremented identity column

or

Write a function in your db that returns the max id and set it yourself in EF

or

Get the whole table in your EF (load it completely) and then select the max id.

I strongly advise against option 2 and 3 and for option 1 to use a auto-increment-feature of your database. You should insert them via EF with a id of null and the DB will set the id. You need to mark the column accordingly.

user_93386450
Related