Insert error - Cannot insert explicit value for identity column in table 'YouTableName' when IDENTITY_INSERT is set to OFF.

[henkmollema]

Insert error - Cannot insert explicit value for identity column in table 'YouTableName' when IDENTITY_INSERT is set to OFF.

[henkmollema]

It should work if you remove the setter (e.g. set;) in your POCO.

[enterpub]

Hello, I'm experiencing same issue. I have a table with Id column, which is primary key and identity,
Id bigint IDENTITY(1, 1) NOT NULL

I have a class Token, works with table Token

public sealed class Token
    {
        public long Id { get; set; }
        public string Token { get; set; }
    }

when I trying to insert new record with method:

public void CreateToken(Token token)
        {
            using (var connection = CreateConnection())
            {
                connection.Insert(token);
            }
        }

I get an the above exception. The query, generated, by Dommel looks like this:

exec sp_executesql N'set nocount on insert into Token (Id, Token) values (@Id, @Token) select cast(scope_identity() as int)',
N'@Id bigint,@Token nvarchar(4000)',
@Id=0,@Token=N'8d539f27-71fd-49b2-897d-0837d85580bbd882a1e3-90f3-46bf-8005-a51e9af65b8f'

The problem is: generated query must not contain Id column and @id parameter. It should look like this:

exec sp_executesql N'set nocount on insert into Token (Token) values (@Token) select cast(scope_identity() as int)',
N'@Token nvarchar(4000)',
@Token=N'8d539f27-71fd-49b2-897d-0837d85580bbd882a1e3-90f3-46bf-8005-a51e9af65b8f'

How to achieve such behaviour? Is there a way to say Dommel that a column is an identity column?

[henkmollema]

@LiveLikeLastDay @enterpub this should be fixed in v1.7.3: https://www.nuget.org/packages/Dommel/1.7.3

FYI: you can use the [Key] attribute to explicitly specify the key property for a type so it will be ignored when building queries.

[ahoka]

Hi,

This change breaks the case when the Id actually comes from the application. This is breaking existing applications using Dommel.

[henkmollema]

I understand @ahoka. Although it was never the intention to set the primary key from the application side. However, I do think it might be a nice feature. I'm not sure yet how to implement both cases correctly without too much need for configuration. If someone of you have any suggestions, please share them.

[henkmollema]

@LiveLikeLastDay @enterpub @ahoka
I've update the IKeyPropertyResolver interface so that it indicates whether a property is a an identity. This behavior can be configured. Dapper.FluentMap.Dommel will provide support for this soon.

See a7653b0.

[henkmollema]

Dapper.FluentMap.Dommel now supports configuring the identity of a property.