Can not use Entity Framework Core 3.1 when set ENV LC_ALL and LANG in docker container

[uopeydel]

Now i use efcore v 3.1.7 in dotnet core TargetFramework netcoreapp3.1
I try to add this 2 line below into my docker file

    ENV LANG th_TH.UTF-8  
    ENV LANGUAGE th_TH:th 

These 2 line of config make my report can generate correctly language in dotnetcore v 2.1
With lib ReportServer ( Microsoft.VisualStudio.ConnectedService.Wcf v15.0.40203.910)

But when i migrate to dotnetcore 3.1 and set that 2 env it make i can not query entity framework core 3.1.7 with this code

var userLogedIn = await _uow.User.Where(w => w.empNo == dataEmpNo).AsNoTracking().FirstOrDefaultAsync();
It alway show error message like this

    {
        "ex": {
            "ClassName": "Microsoft.Data.SqlClient.SqlException",
            "Message": "Invalid column name '__dataEmpNo_0'.",
            "Data": {
                "HelpLink.ProdName": "Microsoft SQL Server",
                "HelpLink.ProdVer": "14.00.1000",
                "HelpLink.EvtSrc": "MSSQLServer",
                "HelpLink.EvtID": "207",
                "HelpLink.BaseHelpUrl": "http://go.microsoft.com/fwlink",
                "HelpLink.LinkId": "20476",
                "SqlError 1": "Microsoft.Data.SqlClient.SqlError: Invalid column name '__dataEmpNo_0'."
            },
            "InnerException": null,
            "HelpURL": null,
            "StackTraceString": "   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)\n   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()\n   at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)\n   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)\n--- End of stack trace from previous location where exception was thrown ---\n   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)\n   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)\n--- End of stack trace from previous location where exception was thrown ---\n   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)\n   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)\n   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)\n   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()\n   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)\n   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)\n   at ****.Services.UserService.Token(RequestUserLoginModel usermodel) in /*******/Services/UserService.cs:line 256\n   at ****.Controllers.UsersController.Token(RequestUserLoginModel userParam, String q) in /*******/Controllers/UsersController.cs:line 82",
            "RemoteStackTraceString": null,
            "RemoteStackIndex": 0,
            "ExceptionMethod": null,
            "HResult": -2146232060,
            "Source": "Core Microsoft SqlClient Data Provider",
            "WatsonBuckets": null,
            "Errors": null,
            "ClientConnectionId": "5206c80f-d8f8-4b12-81e7-b9c51539b6af"
        }
    }

And when I try to change var dataEmpNo into other name it will change error message

"Message": "Invalid column name '__dataEmpNo_0'."

follow my variable name too. Same variable name is i use to query.
But when i remove config ENV LC_ALL and LANG entity framework it work fine.

Further technical details

Database provider: Microsoft.EntityFrameworkCore.SqlServer 3.1.7
Target framework: netcoreapp3.1
LangVersion 7.1
Microsoft.NET.Sdk.Web
Operating system: docker mcr.microsoft.com/dotnet/core/sdk:3.1-buster
IDE: . Visual Studio 2019 16.6.0
Microsoft.VisualStudio.ConnectedService.Wcf 15.0.40203.910

[smitpatel]

This issue is lacking enough information for us to effectively reproduce. Please post a runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

[uopeydel]

https://github.com/uopeydel/testdb

use this project step test is run command

docker build --pull --rm -f "dockerfile" -t testdb:latest "."

docker run -d -p 1144:443/tcp -p 1188:80/tcp testdb:latest

and go to url

http://localhost:1188/api/Values/GetData

It will show you error message

An unhandled exception occurred while processing the request.
SqlException: Invalid column name '__data_0'.
Microsoft.Data.SqlClient.SqlCommand+<>c.<ExecuteDbDataReaderAsync>b__164_0(Task<SqlDataReader> result)

Then comment line below in "dockerfile"

# ENV LANG th_TH.UTF-8  
# ENV LC_ALL th_TH.UTF-8 

then try to build docker and run again it will be work fine.

** please change ConnectionStrings before test

[uopeydel]

Any ideas update? @smitpatel

For now i think this is not relate just with Entity Framework Core

May be it happen because .netcore 3.1 sdk

I tried to create new docker file pushed today name "dockerfile.v2"

You can run it by command

docker build --pull --rm -f "dockerfile.v2" -t testdb:latest "."

If i have 2 line of this code

 ENV LC_ALL th_TH.UTF-8    
 ENV LANG th_TH.UTF-8   

And run this command

docker run -p 1144:443/tcp -p 1188:80/tcp testdb:latest

It will show this error message

docker run  -p 1144:443/tcp -p 1188:80/tcp   testdb:latest
watch : Polling file watcher is enabled
watch : Started
watch : System.ArgumentException: An item with the same key has already been added. Key: /app/FirstCodeDb/Controllers/
   at System.Collections.Generic.Dictionary`2.TryInsert(TKey key, TValue value, InsertionBehavior behavior)
   at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
   at Microsoft.DotNet.Watcher.Internal.FileWatcher.AddDirectoryWatcher(String directory)
   at Microsoft.DotNet.Watcher.Internal.FileSetWatcher.GetChangedFileAsync(CancellationToken cancellationToken, Action startedWatching)
   at Microsoft.DotNet.Watcher.DotNetWatcher.WatchAsync(ProcessSpec processSpec, IFileSetFactory fileSetFactory, CancellationToken cancellationToken)
   at Microsoft.DotNet.Watcher.Program.MainInternalAsync(IReporter reporter, String project, ICollection`1 args, CancellationToken cancellationToken)
   at Microsoft.DotNet.Watcher.Program.RunAsync(String[] args)
watch : An unexpected error occurred

If i remove this 2 line

 ENV LC_ALL th_TH.UTF-8    
 ENV LANG th_TH.UTF-8   

It will work fine.

[smitpatel]

@uopeydel - The error is certainly not in EF Core as we don't try to use those variables. It is likely limitation in SqlClient. Does your Sql Server installation has same locale settings as the one you are trying to use?

[uopeydel]

No , i just want to use it in backend server. Thoes variables i set it for some lib can work fine with my language with out problem aboute a size and font.

[ajcvickers]

@uopeydel I'm transferring this to the dotnet runtime repo since the stack trace doesn't show anything data related.

[ghost]

Tagging subscribers to this area: @cheenamalhotra, @David-Engel
See info in area-owners.md if you want to be subscribed.

[cheenamalhotra]

Hi @uopeydel

Someone from our team will investigate and get back to you soon.
In the meantime, could you also provide details regarding SQL Server version and any special settings/flags enabled on your DB?
Also what is the collation of your SQL Database?

[uopeydel]

SQL Server Management Studio 15.0.18206.0
Microsoft Analysis Services Client Tools 15.0.1567.0
Microsoft Data Access Components (MDAC) 10.0.18362.1
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 9.11.18362.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.18363

and

Microsoft SQL Server Enterprise (64-bit)
Windows Server 2019 Standard Evaluation (10.0)
version 15.0.2000.5
Server collation SQL_Latin1_General_CP1_CI_AS

For the collation it create by command update migration. I didn't set it.

[karinazhou]

@uopeydel Thank you for the information you provided. Sorry for the delay. I will try your repo locally and keep you updated.

Thanks,

[karinazhou]

Hi @uopeydel ,

I got the chance to test your repro app locally. And yes, I can see the same exception even though my server collation is SQL_Latin1_General_CP1_CI_AS. One thing I noticed is that I didn't get an exception if I changed the locale to something else.

I comment out the following three lines in the docker file:

ENV LC_ALL th_TH.UTF-8    
ENV LANG th_TH.UTF-8 
ENV LANGUAGE th_TH:th

And adding the following three lines:

RUN sed -i -e 's/# zh_CN.UTF-8 UTF-8/zh_CN.UTF-8 UTF-8/' /etc/locale.gen && \
    locale-gen 
ENV LC_ALL zh_CN.UTF-8
ENV LANG zh_CN.UTF-8

I can see my locale is changed for the docker container:

root@aa98ed8173cd:/app# locale
LANG=zh_CN.UTF-8
LANGUAGE=
LC_CTYPE="zh_CN.UTF-8"
LC_NUMERIC="zh_CN.UTF-8"
LC_TIME="zh_CN.UTF-8"
LC_COLLATE="zh_CN.UTF-8"
LC_MONETARY="zh_CN.UTF-8"
LC_MESSAGES="zh_CN.UTF-8"
LC_PAPER="zh_CN.UTF-8"
LC_NAME="zh_CN.UTF-8"
LC_ADDRESS="zh_CN.UTF-8"
LC_TELEPHONE="zh_CN.UTF-8"
LC_MEASUREMENT="zh_CN.UTF-8"
LC_IDENTIFICATION="zh_CN.UTF-8"
LC_ALL=zh_CN.UTF-8

And if I build, run the docker file, and go to the url, the data can be returned without error. I also tried with other locales such as ko_KR.UTF-8 which doesn't show me the exception either. You can also play with different locales on your side to see whether you get the same behavior. I only get the exception when I set locale to th_TH.UTF-8.

Have you tried your application with other docker images or any Linux vm?

Thanks,

[uopeydel]

@karinazhou Aye, I ever tried. If I run without build( watch run ) it will show error after start like image below. When have those two variables. And if I run after build like above. It will show same thing with my locale th_TH.UTF-8 It can't query. Target for me is use th_TH.UTF-8. My language is so poor right ?

[karinazhou]

@uopeydel That is strange if the application cannot work well with some locales. I will do more testing to dig this deeper.

[karinazhou]

Hi @uopeydel ,

I did more tests on this one. Based on your repro app, I added two more routes to get the data directly using Microsoft.Data.SqlClient driver. You can find new ValuesController.cs here.

Both GetDataSync and GetDataAsync can retrieve the data from the server successfully when using Thai locale (same docker file you are using). You can see the returned data printed in the Visual Studio output window like this:

1	DC1	abcx	 CreateDateTime: 15/10/2563 07:25:38	  UpdateDateTime: 15/10/2563 07:25:38
2	DC2	abcy	 CreateDateTime: 15/10/2563 07:25:38	  UpdateDateTime: 15/10/2563 07:25:38
3	DC3	abcz	 CreateDateTime: 15/10/2563 07:25:38	  UpdateDateTime: 15/10/2563 07:25:38

From our EventSource tracing log, I find that the command texts passing to SqlCommand are different when using Thai locale versus other working locale:

// when using Thai locale

Event Trace (<sc.SqlCommand.set_CommandText|API> 11, String Value = 'SELECT [t].[Id], [t].[CreateDateTime], [t].[Key], [t].[RecordStatus], [t].[UpdateDateTime], [t].[Value]
FROM [Taxonomy] AS [t]
WHERE ([t].[Key] <> __data_0) OR [t].[Key] IS NULL'). 

// when using other working locale

 Event Trace (<sc.SqlCommand.set_CommandText|API> 11, String Value = 'SELECT [t].[Id], [t].[CreateDateTime], [t].[Key], [t].[RecordStatus], [t].[UpdateDateTime], [t].[Value]
FROM [Taxonomy] AS [t]
WHERE ([t].[Key] <> @__data_0) OR [t].[Key] IS NULL').

You can see that @ sign is missing which causes the SqlException you get.
Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name '__data_0'.

I think some locale issue happens when passing the parameters to the driver.

Hi @ajcvickers , can someone from efcore side take a look at this issue to see why we are missing @ when using Thai locale?

Thanks,

[cheenamalhotra]

@roji @ajcvickers

Does it make sense to transfer the issue to EF Core repo based on @karinazhou 's response above?

[roji]

This look very similar to npgsql/efcore.pg#1508 (comment)

[roji]

Duplicate of #18831

[roji]

This is indeed a bug in EF Core, which has already been fixed for 5.0 - it's recommended that anyone hitting this try out 5.0.0-rc2, which is go-live ready. #18831 contains a workaround for this for 3.1 (by replacing the RelationalSqlGenerationHelper service).

At the time the decision was made to not patch 3.1 for this, am leaving this issue open to discuss this again in triage.

[ajcvickers]

Note from triage: we will prepare this for a 3.1 patch since it impacts all machines with Thai locale.

[roji]

Submitted #23052 to to patch 3.1.

Closing this as a duplicate of #18831.