Pomelo.entityframeworkcore.mysql: Parameter type StringValues (DbType: String) not currently supported.

[tiberhealth]

Steps to reproduce

```C#
var certKey = context.HttpContext.Request.Headers
.SingleOrDefault(item => item.Key.Equals("*", StringComparison.OrdinalIgnoreCase));

var apiPass = context.HttpContext.Request.Headers
.SingleOrDefault(item => item.Key.Equals("*", StringComparison.OrdinalIgnoreCase));

if (string.IsNullOrWhiteSpace(certKey.Value) || string.IsNullOrWhiteSpace(apiPass.Value))
{
context.Result = new UnauthorizedResult();
return;
}

using var attributeScope = context.HttpContext.RequestServices.CreateScope();
using var dbContext = attributeScope.ServiceProvider.GetRequiredService();

var partner = dbContext.marketingPartnerDim.SingleOrDefault(field => field.PartnerKey == certKey.Value);
if (partner == null || apiPass.Value != partner.PartnerSecret)
{
context.Result = new NotFoundResult();
}

### The issue
Currently, the Pomelo library does not serialize type StringValues and requires an explicit ToString(). I did not know if this was by design since StringValues can contain multiple values and the ToString() concatenates the values with a comma (,).  This did cause an issue when we migrated from the Oracle library to the Pomelo library. The Oracle library did the ToString conversion for us. 

Exception message: System.NotSupportedException: Parameter type StringValues (DbType: String) not currently supported. Value: Postbacc

System.NotSupportedException: Parameter type StringValues (DbType: String) not currently supported. Value: Postbacc
at MySqlConnector.MySqlParameter.AppendSqlString(ByteBufferWriter writer, StatementPreparerOptions options) in /_/src/MySqlConnector/MySqlParameter.cs:line 432
at MySqlConnector.Core.StatementPreparer.ParameterSqlParser.DoAppendParameter(Int32 parameterIndex, Int32 textIndex, Int32 textLength) in /_/src/MySqlConnector/Core/StatementPreparer.cs:line 86
at MySqlConnector.Core.SqlParser.Parse(String sql) in /_/src/MySqlConnector/Core/SqlParser.cs:line 198
at MySqlConnector.Core.StatementPreparer.ParseAndBindParameters(ByteBufferWriter writer) in /_/src/MySqlConnector/Core/StatementPreparer.cs:line 37
at MySqlConnector.Core.SingleCommandPayloadCreator.WriteCommand(IMySqlCommand command, ByteBufferWriter writer) in /_/src/MySqlConnector/Core/SingleCommandPayloadCreator.cs:line 205
at MySqlConnector.Core.SingleCommandPayloadCreator.WriteQueryCommand(CommandListPosition& commandListPosition, IDictionary2 cachedProcedures, ByteBufferWriter writer) in /_/src/MySqlConnector/Core/SingleCommandPayloadCreator.cs:line 32 at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 49
at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 310
at MySqlConnector.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in /_/src/MySqlConnector/MySqlCommand.cs:line 255
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.InitializeReader(DbContext _, Boolean result) at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.MoveNext()
at System.Linq.Enumerable.SingleOrDefaultTSource
at AdminPortal.Attributes.ApiAuthorizeFilter.OnAuthorization(AuthorizationFilterContext context) in D:\a\1\s\AdminPortal\Attributes\ApiAuthorizeAttribute.cs:line 41
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.StatusCodePagesMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
at NewRelic.Providers.Wrapper.AspNetCore.WrapPipelineMiddleware.Invoke(HttpContext context)

```

Further technical details

MySQL version: 8.0.22 (Percona)
Operating system: Ubuntu 20.04.1 LTS
Pomelo.EntityFrameworkCore.MySql version: 5.0.0-alpha.2
Microsoft.AspNetCore.App version: net5.0

[mguinness]

It appears that this exception is being thrown in the MySqlParameter class of the underlying MySqlConnector library, so you should open an issue there for further help. See https://github.com/mysql-net/MySqlConnector/issues/844 for a similar issue.

[lauxjpn]

This issue could be fixed in a relative simple fashion in either MySqlConnector or Pomelo.EFCore.MySql.

However, I am not convinced that calling the ToString() method for any String parameter value (and if that would be the case, then also for any inline String value; for them, ToString() is not called as well, but they are merely cast to String instead) is the way to go here, as this encourages users to supply non-string values to string parameters.

I took a look at how the SQL Server provider handles cases like this and it throws as well:

System.ArgumentException: No mapping exists from object type Microsoft.Extensions.Primitives.StringValues to a known managed provider native type.

So since MySqlConnector throws an exception that points to the underlying issue in a well understandable way, I think neither MySqlConnector nor Pomelo should support this scenario, even if Oracle's implementation does.

@bgrainger What do you think?

---

@tiberhealth In case you need this fixed for your project, a simple way to fix it yourself should be to just use a value converter.

[tiberhealth]

@lauxjpn - thank you for the reminder about the Value Converter. I did not think about that solution (kind of forgot about it). I also agree that it is best not to fix it best on your comments and really thinking about it. It just kind of frustrated the team as it actually took them a little time to resolve the issue and researching why the error was happening.

I do not know how many are using the StringValues, but I am assuming we are not the only ones :) and hopefully, this will act as documentation when they do a Google/Bing search.

I personally regret not going with my gut and starting with Pomelo and using the Oracle libraries first and having to go through this migration.

Thank you!

[bgrainger]

The MySqlConnector behaviour (not calling ToString() on any arbitrary parameter value) is by design: https://github.com/mysql-net/MySqlConnector/issues/410#issuecomment-353170516. The recommended workaround is to explicitly call .ToString() (which is potentially culture-sensitive) on any value before adding it to a parameter. This may eventually be solved with some kind of type-mapping plugin system: https://github.com/mysql-net/MySqlConnector/issues/793

A value converter (as suggested above) sounds like the best approach to me.

[lauxjpn]

Value converters usually convert in two directions.

Here is a sample program, that applies a custom value converter AnyTypeToStringValueConverter to all string properties of all entities.
It will call object.ToString() on all model/input values, if those are not already a string, but assumes when string values are read back from the database, that those values are being stored in a string property.

It should be mentioned, that this value converter deliberately circumvents some type checks that are usually done by the default EF Core implementation. It should therefore only be used for its intended purpose.

```c#
using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Primitives;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure;

namespace IssueConsoleTemplate
{
public class IceCream
{
public int IceCreamId { get; set; }
public string Name { get; set; }
}

public class AnyTypeToStringValueConverter : ValueConverter<string, string>
{
    public AnyTypeToStringValueConverter()
        : base(v => v, v => v)
    {
    }

    public override Func<object, object> ConvertToProvider => value => value switch
    {
        null => null,
        string stringValue => stringValue,
        _ => value.ToString()
    };
}

public class Context : DbContext
{
    public virtual DbSet<IceCream> IceCreams { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1287";

        optionsBuilder.UseMySql(
                connectionString,
                ServerVersion.AutoDetect(connectionString),
                options => options.CharSetBehavior(CharSetBehavior.NeverAppend))
        // optionsBuilder.UseSqlServer(@"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=Issue1287")
            .UseLoggerFactory(
                LoggerFactory.Create(
                    configure => configure
                        .AddConsole()
                        .AddFilter(level => level >= LogLevel.Information)))
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Apply our custom AnyTypeToStringValueConverter to all string properties of all entities.
        foreach (var entityType in modelBuilder.Model.GetEntityTypes())
        {
            foreach (var property in entityType.GetProperties())
            {
                if (property.ClrType == typeof(string))
                {
                    property.SetValueConverter(new AnyTypeToStringValueConverter());
                }
            }
        }

        // Add some sample data.
        modelBuilder.Entity<IceCream>()
            .HasData(
                new IceCream {IceCreamId = 1, Name = new StringValues(new[] {"Vanilla", "Vaniglia"}).ToString()},
                new IceCream {IceCreamId = 2, Name = new StringValues(new[] {"Chocolate", "Cioccolato"}).ToString()});
    }
}

internal static class Program
{
    private static void Main(string[] args)
    {
        using var context = new Context();

        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        var vanillaName = new StringValues(new[] {"Vanilla", "Vaniglia"});

        var iceCreams = context.IceCreams
            .Where(i => i.Name == vanillaName)
            .ToList();

        Trace.Assert(iceCreams.Count == 1);
        Trace.Assert(iceCreams[0].Name == "Vanilla,Vaniglia");
    }
}

}
```

[lauxjpn]

I do not know how many are using the StringValues, but I am assuming we are not the only ones :) and hopefully, this will act as documentation when they do a Google/Bing search.

@tiberhealth This really applies to _all_ non-standard types (see MySqlParameter.cs for the supported types).

It just kind of frustrated the team as it actually took them a little time to resolve the issue and researching why the error was happening.

@bgrainger Maybe it's worth to explicitly add some pointers à la Consider calling 'ToString()' before using the value as part of a parameter. or something similar to the exception message, when a non-string value is used for a MySqlDbType.String/MySqlDbType.VarChar typed parameter, if this a compatibility issue between Oracle's provider and MySqlConnector when switching libraries.

[mguinness]

As an aside, why not just use [FromHeader] source instead? Allows you to avoid dealing with the StringValues struct.

```C#
public IActionResult Test([FromHeader(Name = "X-CertKey")]string certKey, [FromHeader(Name = "X-ApiPass")]string apiPass)
{
if (certKey == null || apiPass.Value == null)
return Unauthorized();
...
}

Or if you're not able to use model binding then just assign the variables directly.  Values will be null if header not present.

```C#
string certKey = context.HttpContext.Request.Headers["X-CertKey"];
string apiPass = context.HttpContext.Request.Headers["X-ApiPass"];

[tiberhealth]

We cannot use the Model binding as this is placed in a common code library as an Authorize Attribute. I would have to look at the requirements as to why we did not use the index for headers. I am assuming that there were some case-sensitivity issues from calling systems.

We also use StringValues in other places in our code that had this problem - the above was just one of the many segments.

[mguinness]

The internal store of HeaderDictionary is initialized w/ StringComparer.OrdinalIgnoreCase that follows RFC 7230,

[lauxjpn]

Let's keep this issue open until a decision has been made, whether the exception message should be made more explicit or not.

[lauxjpn]

Maybe it's worth to explicitly add some pointers à la Consider calling 'ToString()' before using the value as part of a parameter. or something similar to the exception message, when a non-string value is used for a MySqlDbType.String/MySqlDbType.VarChar typed parameter, if this a compatibility issue between Oracle's provider and MySqlConnector when switching libraries.

@bgrainger What do you think?

[bgrainger]

Yes, I'm generally in favour of exception messages that help the user resolve the problem themselves (if there are good reasons for the library not to automatically resolve it for the user), e.g.,

• https://github.com/mysql-net/MySqlConnector/blob/6413d139f4c8aa601d67b9b6b017fc0177246d9f/src/MySqlConnector/Core/StatementPreparer.cs#L47
• https://github.com/mysql-net/MySqlConnector/blob/6413d139f4c8aa601d67b9b6b017fc0177246d9f/src/MySqlConnector/MySqlBulkLoader.cs#L210

Opened a new issue: https://github.com/mysql-net/MySqlConnector/issues/925

[bgrainger]

Relevant recent tweet: https://twitter.com/JamesNK/status/1348857470811193344

[lauxjpn]

@bgrainger Nice one!