Collation is not supported for Traditional_Spanish_CI_AS

[mobius127]

Describe the bug

I´m trying to map an entire database with Traditional_Spanish_CI_AS collation with Entity Framework Core 6 with .net6.

When I try to execute the command for scaffold the database drops this error:

Exception message:
Stack trace:

SQL Server 2019
.net 6
Visual Studio 2022
EF Core 6

[mobius127]

Sorry but I Tried to solved with this packages

Still not working.

PD: I closed the issue previously because I thought was SqlClient version installed with EF Core

[JRahnama]

@mobius127 have you tried testing with net 5 or netcoreapp 3.1?

[mobius127]

@JRahnama hi!!!

I`m just tried with .net5 and EF core 5 versions and it works

Dependencies with EF Core 5 and SQLClient

In EF core 6 the dependencies with SqlClient

Maybe it's the version that includes in EF Core 6 that produces this error of collation?

[JRahnama]

@mobius127 the version can be overwritten in your csproj. just add

<ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="4.0.0" />
</ItemGroup>

to the csproj and the latest version will be used. My concern is .net 6 has something to do with this. Using latest version of MDS and net 5.0 will give us a clear answer.

Regards.

[mobius127]

@JRahnama in .net6 i tried to overwrite with the latest version of MDS and failed with dotnet dbcontext scaffold:

This is the example:
dotnet ef dbcontext scaffold "Data Source=[SQLServer];Initial Catalog=[Database];Integrated Security=True;TrustServerCertificate=True;MultipleActiveResultSets=true;Enlist=true" Microsoft.EntityFrameworkCore.SqlServer --context ContextName --context-dir "Context" --output-dir "Entities" --force
With .net5 and the latest version of MDS it works correctly but with .net6 no.

Regards

[roji]

@mobius127 what exact exception are you seeing when trying to use Microsoft.EntityFrameworkCore.SqlServer 6.0.0 with Microsoft.Data.SqlClient 4.0.0? I've just done a quick test application and it works fine for me - can you post a sample app which fails etc?

[mobius127]

Hi @roji have you tried with sql server configured with this collation "Traditional_Spanish_CI_AS"?

This is the error that drops

Build started...
Build succeeded.
To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
Microsoft.Data.SqlClient.SqlException (0x80131904): The Collation specified by SQL Server is not supported.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.TdsParser.DrainData(TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.TdsParser.ThrowUnsupportedCollationEncountered(TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp, String& value)
   at Microsoft.Data.SqlClient.TdsParser.TryReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.TdsParser.TryReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj, SqlCommandColumnEncryptionSetting columnEncryptionOverride, String columnName, SqlCommand command)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadColumnInternal(Int32 i, Boolean readHeaderOnly, Boolean forStreaming)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn, Boolean forStreaming)
   at Microsoft.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn)
   at Microsoft.Data.SqlClient.SqlDataReader.GetString(Int32 i)
   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.GetTables(DbConnection connection, DatabaseModel databaseModel, Func`3 tableFilter, IReadOnlyDictionary`2 typeAliases, String databaseCollation)
   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(DbConnection connection, DatabaseModelFactoryOptions options)
   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(String connectionString, DatabaseModelFactoryOptions options)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluarlize)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:78327ee0-1282-4c41-990c-330519ff22a9
The Collation specified by SQL Server is not supported.`

Packages installed:

<ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="4.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.0">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.0">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Swashbuckle.AspNetCore" Version="6.2.3" />
  </ItemGroup>`

I can share the project but i'ts a web api project with those packages nothing more and dunno if it's make sense to share.

The point is, automatic scaffold fails whatever the version of MDS are installed with EF core 6. In the other hand with .net5 and ef core 5 it works

My enviroment has:

Visual Studio 2022
SQL Server 2019
.NET SDK 6.0.100
EF Core command-line tools 6.0.0

ConnectionString = "Data Source=[SQLServer];Initial Catalog=[Database];Integrated Security=True;TrustServerCertificate=True;MultipleActiveResultSets=true;Enlist=true"

I hope this helps

Thanks for your time

[roji]

Hi @roji have you tried with sql server configured with this collation "Traditional_Spanish_CI_AS"?

Yes, I have - with SQL Server 2019, on both Windows and Linux, I can't get the error to reproduce with EF Core 6.0.0 and Microsoft.Data.SqlClient 4.0.0, when trying to scaffold a database created as follows:

CREATE DATABASE test COLLATE Traditional_Spanish_CI_AS;

The scaffolded EF Core model contains the following in its OnModelCreating:

modelBuilder.UseCollation("Traditional_Spanish_CI_AS");

Is it possible you have a specially-tweaked SQL Server? Could you try on brand new installation just to make sure?

[mobius127]

@roji mmm my SQL Server it's the developers edition

Here's some captures

[roji]

@JRahnama have you been able to repro this?

[mobius127]

@roji for more info my PC is configured with this language options:

And if you install SQL Server, the installer selects "Traditional_Spanish_CI_AS" by default.

Today I try a brand new installation and I've selected this collation "SQL_Latin1_General_CP1_CI_AS" and it works the command dbcontext scaffold.