How to use Sql server UTF-8 collation with varchar data type

[valeriob]

Hi,
i have a database with the Latin1_General_100_CI_AS_SC_UTF8 collation and the schema has all text column as varchar.
We would like to suport unicode, and we read that since sql server 2019 and azure db it's possible with -UTF8 collations (default in azure).
If we configure EF6 with nvarchar datatype (the default) we incurr in many conversion at query time, and problem with indexes since the query parameters are passed as N'string_value'.

So we tried to configure the EF with the varchar type,
modelBuilder.Properties<string>().Configure(c => c.HasColumnType("varchar"));
we correctly read the UTF-8 text, but when we write it, we corrupt it.

Then i tried to configure the column as .IsUnicode(true)
modelBuilder.Properties<string>().Configure(c => c.HasColumnType("varchar").IsUnicode(true));
now EF thows exception wile updating the text :

Invalid data for UTF8-encoded characters
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()

What am i missing ? What is the correct way to handle this scenario ?

Thanks

EF version: 6.1.3
Database Provider: EntityFramework.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2022

[valeriob]

probably the same issue has been solved in ef core ? dotnet/efcore#25798

[ajcvickers]

This issue has been closed because EF6 is no longer being actively developed. We are instead focusing on stability of the codebase, which means we will only make changes to address security issues. See the repo README for more information.