Insert issue when key column is not identity

[rkiguti]

I have a table where the key is not auto generated. After upgrade to v2 the INSERT command fails.

I did a SQL Server profile to get the command and the key columns are ommited from my SQL.

I used the method SetGeneratedOption to DatabaseGeneratedOption.None.

My map:

ToTable("TABLE_NAME"); Map(p => p.Key1).ToColumn("KEY1").IsKey().SetGeneratedOption(DatabaseGeneratedOption.None); Map(p => p.Key2).ToColumn("KEY2").IsKey().SetGeneratedOption(DatabaseGeneratedOption.None); Map(p => p.Column1).ToColumn("COL1"); Map(p => p.Column2).ToColumn("COL2");

[henkmollema]

Can you show the generated SQL?

[rkiguti]

I changed the code to use pure Dapper and I write my own insert command. But I can back my code to get the real SQL if necessary.

But the generated SQL in Profiler was like this: "INSERT INTO TABLE_NAME(COL1, COL2) VALUES(@COL1, @COL2)"

When I removed the IsKey() method from Key2 for example the SQL changed to: "INSERT INTO TABLE_NAME(KEY2, COL1, COL2) VALUES(@KEY2, @COL1, @COL2)"

And when I removed the two methods IsKey() it raises an error that my table has no key.

I tried all three types of DatabaseGeneratedOption (None, Identity and Computed) but it doesn't make difference.

[rkiguti]

To complement my issue my project uses .NET Framework 4.8 and my nuget packages are: Dommel (v2.1), Dapper.FluentMap.Dommel (v2.0), Dapper.FluentMap (v2.0), Dapper (v2.0.35).

[rkiguti]

My real code:

public PessoaVinculoMapper()
{
    ToTable("PESSOA_VINCULO");
    Map(p => p.CodigoDependente).ToColumn("IDFK_PESSOA_VINCULADA").IsKey();
    Map(p => p.CodigoPessoaTipoVinculo).ToColumn("IDFK_PESSOA_TIPO_VINCULO");
    Map(p => p.CodigoResponsavel).ToColumn("IDFK_PESSOA_VINCULO");
    Map(p => p.PodeRetirarAluno).ToColumn("PODE_RETIRAR_ALUNO");
    Map(p => p.Dependente).Ignore();
    Map(p => p.PessoaTipoVinculo).Ignore();
    Map(p => p.Responsavel).Ignore();
}

FluentMapper.Initialize(config =>
{
    config.AddMap(new PessoaVinculoMapper()); 
    config.ForDommel();
});

The SQL command:

exec sp_executesql N'set nocount on insert into [PESSOA_VINCULO] ([IDFK_PESSOA_VINCULO], [PODE_RETIRAR_ALUNO], [IDFK_PESSOA_TIPO_VINCULO]) values (@CodigoResponsavel, @PodeRetirarAluno, @CodigoPessoaTipoVinculo); select scope_identity()',N'@CodigoPessoaTipoVinculo int,@CodigoResponsavel bigint,@PodeRetirarAluno bit',@CodigoPessoaTipoVinculo=1,@CodigoResponsavel=364,@PodeRetirarAluno=1

[rsfurlan90]

I can confirm that this issue is still occurring. It seems that calling the method SetGeneratedOption(DatabaseGeneratedOption.None) for a given property, as pointed by @rkiguti, has no effect when Dommel generates the query, because it will try to get the DatabaseGeneratedAttribute, thus ignoring the value set inside the DommelEntityMap. It only works when the Attribute is present.

Also, the insert query generated later will still try to get the scope_identity(), but in such case it will return null because no key was generated.