Bug: exception on inserting enum array using raw sql

[kbilsted]

Bug Description

When inserting a row using raw sql

Exception Message:

System.Data.SqlClient.SqlException : There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Data:
  HelpLink.ProdName: Microsoft SQL Server
  HelpLink.ProdVer: 15.00.2000
  HelpLink.EvtSrc: MSSQLServer
  HelpLink.EvtID: 110
  HelpLink.BaseHelpUrl: https://go.microsoft.com/fwlink
  HelpLink.LinkId: 20476

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.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)
   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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at RepoDb.DbConnectionExtension.ExecuteNonQueryInternal(IDbConnection connection, String commandText, Object param, Nullable`1 commandType, Nullable`1 commandTimeout, IDbTransaction transaction, Type entityType, IEnumerable`1 dbFields, Boolean skipCommandArrayParametersCheck) in C:\temp\RepoDB-potentialFixFor748\RepoDb.Core\RepoDb\Extensions\DbConnectionExtension.cs:line 1295
   at RepoDb.DbConnectionExtension.ExecuteNonQuery(IDbConnection connection, String commandText, Object param, Nullable`1 commandType, Nullable`1 commandTimeout, IDbTransaction transaction) in C:\temp\RepoDB-potentialFixFor748\RepoDb.Core\RepoDb\Extensions\DbConnectionExtension.cs:line 1251
   at xxx.ttttervice.Database.V1.Repository.Create(DbTransaction tx, R registration) in C:\x.cs:line 77

Schema and Model:

the C# class

class R
{ 
    Guid Id {get;set;}
    CommunicationChannel[] CommunicationChannels {get;set;}
   ...

the sql to insert with

INSERT INTO dbo.R(Id,CommunicationChannels,...)VALUES(@Id, @CommunicationChannels,...)

looking at the sql in a debugger we see the array has been unrolled at the sql layer

   exec sp_executesql N'INSERT INTO [dbo].[R]
           ([Id]
            ,...
           ,[CommunicationChannels]
           ...)
          VALUES
           (@Id, 
           @CommunicationChannels0, @CommunicationChannels1, @CommunicationChannels2, 
            ...

The CommunicationChannel is an enum

I think i May need a typemapper? But I have difficulties registering using our generic json handler we have used in earlier bug reports.

registration:

            PropertyHandlerMapper.Add<CommunicationChannel[], JsonObjectTypeHandler<CommunicationChannel[]>();

and code

    public class JsonObjectTypeHandler<T> : IPropertyHandler<string, T>
    {
        public T Get(string input, ClassProperty property)
        {
            return JsonConvert.DeserializeObject<T>(input);
        }

        public string Set(T input, ClassProperty property)
        {
            return JsonConvert.SerializeObject(input);
        }
    }

Library Version:

RepoDb v1.12.6 and RepoDb.SqlServer v1.1.2

dot net core 3.1

[mikependon]

Hey @kbilsted , this scenario is seems to be an edge scenario. The Execute<Methods> is parsing all the IEnumerable<T> properties as a series-of-parameters by replacing the target parameter names. This is the reason why the parameter is being rolled-out as index-based parameter.

If I am correct, you would like to save the record as an array of enumeration values?

Did that property handler you defined via PropertyHandlerMapper.Add<CommunicationChannel[], JsonObjectTypeHandler<CommunicationChannel[]>(); has been invoked?

I am just afraid if the type level via the CommunicationChannel[] is being resolved properly. If not, can you try setting it via property level like below?

FluentMapper.Entity<R>().PropertyHandler<JsonObjectTypeHandler<CommunicationChannel[]>>(e => e.CommunicationChannels);

This recommendation is not yet tested by me, but if you can check it before I dive down into debugging, then it would be a big help.

[mikependon]

@kbilsted - this is colliding with the existing behavior (array parameters for IN operators). Therefore, we might not fixing this one because of that.