Cannot insert explicit value for identity column in table 'table_name' when IDENTITY_INSERT is set to OFF RRS feed

  • Question

  • I'm trying to create a new record on the database using LINQ TO SQL, and I get the following error: Cannot insert explicit value for identity column in table 'cad_local' when IDENTITY_INSERT is set to OFF.

    I been googling arround about this issue for some time now, tried some suggestions including the ones i see on this forum and nothing seems to work.

    Here is how my identity col is declared

    [Column(Name = "id_local", IsPrimaryKey = true, IsDbGenerated = true, DbType = "int IDENTITY(1,1) NOT NULL", AutoSync = AutoSync.OnInsert)]
    public int ID { get;set; }

    I didn't have the DbType specifiied, I tried this based on suggestions I saw, but it doesn't work at all.

    I tried to set IsDbGenerated column to false, and it gives me an SQL Exception saying that I can't have two SET commands on the sql query. So im guessing that its creating a wrong query, but I'm creating the entity classes manually so I don't know whats wrong.

    My database has the column called id_local, and its set as an identity and primary key.

    Does anyone know what else I could try?


    Friday, December 11, 2009 1:46 PM


All replies

  • Turn on the SQL logging to see what SQL it is creating.

    This should let us see what's actually happening.

    • Marked as answer by Thiago N Leite Friday, December 11, 2009 5:04 PM
    Friday, December 11, 2009 4:15 PM
  • this is the query that linq is creating:

    INSERT INTO [cad_local]([name_local], [obs_local], [dim_local], [id_local])
    VALUES (@p0, @p1, @p2, @p3)

    This is the method on my repository responsible for saving

    public void SaveLocal(Local local)
                if (local.ID == 0)

                    tableLocal.Context.Refresh(RefreshMode.KeepCurrentValues, local);

                tableLocal.Context.Log = new DebugTextWriter();

    Have no clue why its including the id_local!
    Friday, December 11, 2009 4:31 PM
  • Can you post the DBML fragment for the whole of Local?

    Friday, December 11, 2009 4:44 PM
  • Hey Damien.
    I was able to find the error. After check the logs I noticed that one of the fields was not on the query, so when I was going to post my entity class i noticed that I had another field that was a fk with the same name as my id col lol. I was afraid that it would be something really stupid i was doing kkk.
    It did not even imagine that it was related to another field, i thought the whole problem was with my entity class.

    well anyway, thank you very much for you help!

    Friday, December 11, 2009 5:04 PM