Issue with "Id" property when using upsert with SqlServer dbcontext

[theeheng]

Here is my entity class:

public class UserProgramRoleEntity { public Guid Id { get; set; } public Guid UserId { get; set; } public Guid ProgramId { get; set; } public ProgramRole ProgramRole { get; set;} public bool Deleted { get; set; } }

and I try to call upsert with the following statement:

var userProgramRoleEntities = new List<UserProgramRoleEntity> { new UserProgramRoleEntity { Id = Guid.NewGuid(), UserId = 1, ProgramId = 2, ProgramRole = "test", Deleted = false }, new UserProgramRoleEntity { Id = Guid.NewGuid(), UserId = 1, ProgramId = 2, ProgramRole = "test2", Deleted = false } };

await context.UserProgramRoleEntity.UpsertRange(userProgramRoleEntities) .On(c => new { c.UserId, c.ProgramId, c.ProgramRole }) .WhenMatched(x => new ReportingUserProgramRoleEntity { Deleted = true } ) .RunAsync();

It throws an error to say that : Microsoft.Data.SqlClient.SqlException : Cannot insert the value NULL into column 'Id', table 'dbo.UserProgramRoleEntities'; column does not allow nulls. UPDATE fails. The statement has been terminated.

Here is the sql its generated : MERGE INTO [UserProgramRoleEntities] WITH (HOLDLOCK) AS [T] USING ( VALUES (@p0, @p1, @p2, @p3) ) AS [S] ([Deleted], [ProgramId], [ProgramRole], [UserId]) ON [T].[UserId] = [S].[UserId] AND [T].[ProgramId] = [S].[ProgramId] AND [T].[ProgramRole] = [S].[ProgramRole] WHEN NOT MATCHED BY TARGET THEN INSERT ([Deleted], [ProgramId], [ProgramRole], [UserId]) VALUES ([Deleted], [ProgramId], [ProgramRole], [UserId]) WHEN MATCHED THEN UPDATE SET [Deleted] = [S].[Deleted];

Any idea why its missing the 'Id' property in the insert statement since 'Id' property is not an auto generated column? My unit test is running fine when I have the dbContextOption calling build.UseInMemoryDatabase but failed when using builder.UseSqlServer. So it seems something wrong with the SqlServer driver

[wassim-k]

I'm having the same issue and I believe it's because FlexLabs.Upsert library excludes properties where ValueGenerated == ValueGenerated.OnAdd from being inserted, since those properties will be populated by the database automatically on insertion. Except, according to this comment, EF Core generates the Id client-side when it's a GUID key, thus the Id is never set and it's why you're seeing that error.

By convention values for GUID keys are generated on the client rather than in the database. (For SQL Server, this uses a sequential GUID algorithm, similar to "newsequentialid" on SQL Server.) Client-side key generation has the advantage that new keys are available immediately rather than only after a round-trip to the database.

@theeheng , as the comment suggests, one work around is enforce Id generation on the server:

modelBuilder
    .Entity<UserProgramRoleEntity>()
    .Property(role => role.Id)
    .HasDefaultValueSql("newsequentialid()");

@artiomchi do you think there's a way to detect if the value will be generated client-side or server side and generate the MERGE statement accordingly for SQL server? Maybe the check can be:

    var allowInserts = p.ValueGenerated == ValueGenerated.Never
        || p.IsGuid()
        || p.GetAfterSaveBehavior() == PropertySaveBehavior.Save;

[erikrenaud]

I've also just hit this issue, using a GUID ID.

[Fgruntjes]

Not 100% sure, but I think EF core assumes the Id property is database generated. I have added the [DatabaseGenerated(DatabaseGeneratedOption.None)] attribute so that this code will no longer return false.

var allowInserts = p.ValueGenerated == ValueGenerated.Never || p.GetAfterSaveBehavior() == PropertySaveBehavior.Save;

Also, not sure if this is different for MsSQL since I am using PostgreSQL.

The entity

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class SomeEntity
{
	[Key]
	[DatabaseGenerated(DatabaseGeneratedOption.None)]
	public Guid Id { get; set; }

	public SomeEntity()
	{
		Id = Guid.NewGuid();
	}
}

Hope it helps.