nullable on, no update is inserting new value

[rsr-maersk]

Hi. Thanks for the great lib.

ServicePlanLegId in the scenario can be null. When we upsert, it will create new rows in the DB, even though both ServicePlanLegId are null and TransportPlanId is the same,.

await _dbContext.Set<TransportOrder>()
.Upsert(transportOrder).On(x => new { x.ServicePlanLegId, x.TransportPlanId }
.NoUpdate()
.RunAsync(cancellationToken);

Shouldn't the lib be able to handle null, it seams to be inserting each time

[artiomchi]

Hey @rsr-maersk - I believe this is related to how databases handle nulls.. At least in MS SQL Server, two columns that are both null are not considered equal, so when trying to find a match when your values are null will end up with this kind of behavior.

I'd recommend changing your unique constraint or the columns to not include values. In theory the query could be updated to use something like COALESCE or ISNULL, but these come with their own caveats, and assumptions

[ivanmem]

In my case, it's even worse:

await db.ConversationStatistics.UpsertRange(convStats)
                    .On(x => new { x.ConversationItem, x.Date })
                    .NoUpdate()
                    .RunAsync();

Always creates a new entry, even if the values are completely identical.
Both values ​​are required.

SystemL Pomelo.EntityFrameworkCore.MySql 8.0.2. mysql 5.7.

upd. The problem was the lack of a similar index, I added it and everything worked. But it works extremely slowly...