Simple insert fails when key col has an explicit collation

[clement911]

Minimum repro: https://github.com/clement911/EfCollationBugRepro

EF Core version :6.0.6
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0

Scenario:

1. Create an entity with a leading key that has a collation different from the default DB collation.
2. Attempt to insert 4 entities in one batch
3. Observe Collation error being throw

Please see attached repo.

When EF use batching for inserts (e.g. inserting more than 4 entities, by default), EF issues a MERGE statement which results in a collation conflict error.

Here is the sql generated by EF. I highlighted the part that causes the error.

The error occurs because of a collation conflict.
The physical column MyEntity.KeyCol1 has collation Latin1_General_100_BIN2
However, the table variable KeyCol1 column has no explicit collation so it defaults to the DB's default collation (SQL_Latin1_General_CP1_CI_AS in our case).
As a result, the equality predicate fails.

Fix:
The generated sql should assign the correct collation to the table variable columns.

See below. We have verified manually that this fixes the issues (i.e. the statement runs succesfully)

[roji]

The above specifically should no longer happen in EF 7, since we no longer use a temporary table variable and select back from it (instead we just do MERGE with OUTPUT directly). You can use the latest preview to check.

But the above is still relevant for when the table has triggers - the user must then tell EF to use the older, less efficient technique above, where the collation issue would exist.

As a workaround, you can disable the MERGE statement by setting your max batch size to 1 (though this will degrade performance by adding more roundtrips).

[clement911]

That's great for EF7.
Will there be no fix for EF6?

[clement911]

Besides the suggested work around, but we'd rather keep the batching functionality to get optimal perf.

[roji]

Will there be no fix for EF6?

It's unlikely that this would meet the bar for a patch, but we'll discuss this in triage.

[AndriySvyryd]

We discussed this in triage and fixing this in 6 would be too risky.

[AndriySvyryd]

Covered by #7172

[roji]

Thanks @AndriySvyryd, did not realize we had another issue.