UseOptionTypes for string columns does not appear to work for .NETCoreApp 2.2

[TheJayMann]

Description

When using the UseOptionTypes option with MSSQL, string columns do not properly return when returning the entire entity as is.

Repro steps

1. Create a table on MSSQL server with null varchar.
2. Create a type alias connecting to the database with the created table, specifying UseOptionTypes=true
3. Create a query which returns the table entity type directly
4. Execute the query asynchronously

Expected behavior

It is expected that the results of the query be properly serialized into the return value of executing the query.

Actual behavior

A System.ArgumentException is thrown, stating that there is not a proper mapping type.

No mapping exists from object type Microsoft.FSharp.Core.FSharpOption`1[[System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]] to a known managed provider native type.

   at System.Data.SqlClient.MetaType.GetMetaTypeFromValue(Object value, Boolean inferLen, Boolean streamAllowed)
   at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
   at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
   at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.BeginExecuteReader(CommandBehavior behavior, AsyncCallback callback, Object stateObject)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncImpl[TArg1](Func`4 beginMethod, Func`2 endFunction, Action`1 endAction, TArg1 arg1, Object state, TaskCreationOptions creationOptions)
   at System.Data.SqlClient.SqlCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)

Known workarounds

By not using the UseOptionTypes option and using null for string values work as expected.

Related information

.NETCoreApp 2.2 SqlProvider 1.1.59

[TheJayMann]

After some analysis, it appears that this error occurs no matter which platform I am using, as it occurred with .NETCoreApp 2.1 and 2.0, as well as .NET Framework 4.7.2, both new and old msbuild formats. However, I had other projects which had no problem. I discovered the only difference between the two projects is one is using Sql Server 2016, and the other Sql Server 2010.

Based on this, My assumption is that option types are not supported in Sql Server 2010.

[Thorium]

Can you just check the .IsNone in the query and then use .Value ?

[TheJayMann]

The query would become needlessly complex if I were to use do the check in the query itself, as this is mostly copying data from one table to another after performing some validation, and it involves 25 columns, all of them nullable, two of which are datetime, all others varchar.

Given that Option doesn't work, I simply used the fact that string type can be null, and that a null datetime translates to DateTime.MinValue, which is not a valid value for datetime, and can simply not set the column value in such case.