Linq2db: MS SQL Server - merge identities with different PK and ID

[LbISS]

Hi there!

It seems i have run into issue similar to #914. Using merge method on ms sql server when trying to update entity (which have different primarykey and identity columns) - i'm getting error "Cannot update identity column 'Id'". That's my entity:
c# [Table("SomeTable")] public partial class SomeEntity: EntityBaseKernel { [PrimaryKey(1), Column("FirstId"), NotNull] public int FirstId{ get; set; } // int [PrimaryKey(2), Column("SecondId"), NotNull] public int SecondId{ get; set; } // int [Identity] public override int Id { get; set; } // int }

I have downloaded source code and then have found interested thing:

MS SQL Server do not allows to update identity columns whatsoever. So it seems that line about updateColumns just copied from line with insertColumns variable.

This idea is supported by code from the same commit, where SqlServerMerge was introduced:

There is a line 'SET IDENTITY_INSERT {tblName} ON' but as it says it's only allows to insert identity, not update.

Environment details

linq2db version: 1.10.1.0
Database Server: SQL Server 2016
Operating system: Windows Server 2016
Framework version: .NET Framework 4.7.1

[LbISS]

Ok, so, i'll go ahead and make PR: https://github.com/linq2db/linq2db/pull/1009
It seems that this is the only logical way to fix only that issue. The columns which are identity will be inserted, but not updated in ms sql.

[MaceWindu]

I will apply your fix, but I want to notice that you use old Merge API implementation, which is not advised for use. I recomment to use new API instead.
We will discuss it internally and maybe remove old API from v2.0

[MaceWindu]

FYI: https://github.com/linq2db/linq2db/wiki/Merge-API-:-Migration-from-Previous-API

[LbISS]

Thx, i'll consider migrating onto new API.