Dapper: .Net Core 3.1 DataTable DynamicParameter throws InvalidCastException

[spencermayne]

Hi

I have recently upgraded my .NetCore 2.2 WebApi to .NetCore 3.1, using Dapper 2.0.30.

Almost everything works as before the upgrade, except for trying to pass a TVP to my stored procs with DynamicParameters.

Take this example code:

public async Task<IEnumerable<int>> GetSomeData(IEnumerable<int> idFilter)
{
        using (var con = _connectionFactory.GetCoreConnection())
    {
            var dataTable = GetDataTable(idFilter);

            var dynamicParams = new DynamicParameters(new { IdFilter = dataTable });

            return await con.QueryAsync<int>("[dbo].[GetDataWithTVP]", dynamicParams, commandType: CommandType.StoredProcedure);
    }
}

private static DataTable GetDataTable(IEnumerable<int> idFilter)
{
    var dt = new DataTable();
    dt.Columns.Add("Id", typeof(int));

    if (idFilter == null) return dt;

    foreach (var id in idFilter)
    {
        dt.Rows.Add(id);
    }

    return dt;
}

Calling this example stored proc:

CREATE PROC [dbo].[GetDataWithTVP] (@IdFilter [dbo].[IdFilter] READONLY)
AS
BEGIN

    SET NOCOUNT ON;

    SELECT Id
      FROM @IdFilter;
END;

GO

At runtime, the code throws the following exception
System.InvalidCastException: 'Failed to convert parameter value from a DataTable to a IEnumerable'1.'

Any ideas as to why this is happening? I have loads of different proc calls with the same, and different TVPs, all throwing the same error.

Any help is greatly appreciated. Thanks!

[mgravell]

I will investigate.

On Thu, 2 Apr 2020, 09:00 Spencer Mayne, [e-mail] wrote:

Hi

I have recently upgraded my .NetCore 2.2 WebApi to .NetCore 3.1, using
Dapper 2.0.30.

Almost everything works as before the upgrade, except for trying to pass a
TVP to my stored procs with DynamicParameters.

Take this example code:

public async Task> GetSomeData(IEnumerable idFilter)
{
using (var con = _connectionFactory.GetCoreConnection())
{
var dataTable = GetDataTable(idFilter);

      var dynamicParams = new DynamicParameters(new { IdFilter = dataTable });

      return await con.QueryAsync<int>("[dbo].[GetDataWithTVP]", dynamicParams, commandType: CommandType.StoredProcedure);

}
}

private static DataTable GetDataTable(IEnumerable idFilter)
{
var dt = new DataTable();
dt.Columns.Add("Id", typeof(int));

if (idFilter == null) return dt;

foreach (var id in idFilter)
{
dt.Rows.Add(id);
}

return dt;
}

Calling this example stored proc:

CREATE PROC [dbo].[GetDataWithTVP] (@IdFilter [dbo].[IdFilter] READONLY)
AS
BEGIN

SET NOCOUNT ON;

SELECT Id
  FROM @IdFilter;

END;

GO

At runtime, the code throws the following exception
System.InvalidCastException: 'Failed to convert parameter value from a
DataTable to a IEnumerable'1.'

Any ideas as to why this is happening? I have loads of different proc
calls with the same, and different TVPs, all throwing the same error.

Any help is greatly appreciated. Thanks!

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/StackExchange/Dapper/issues/1431, or unsubscribe
https://github.com/notifications/unsubscribe-auth/AAAEHMARRKPWOFW5HYFHV7LRKRAZHANCNFSM4L2DGPTQ
.

[mgravell]

Hi; I put together a test in the branch above ^^^ - I cannot repro - it is coming back green on all TFMs, on both System.Data.SqlClient and Microsoft.Data.SqlClient

Can you help me get a minimal repro?

As a side note: for conveying a list of integers, you may be interested in Dapper's custom in support:

... where Id in @ids ...

and in the C#:

``` c#
int[] ids = {...};
var users = connection.Query(@"
select * from Users
where Id in @ids", new { ids }).AsList();

Dapper can rewrite this in a range of ways, including (if you opt into it, because of server compatibility) using `string_split`; the following tells dapper to use multiple parameters for 0-4 values, and `string_split` for 5 or more:

``` c#
Dapper.SqlMapper.Settings.InListStringSplitCount = 5;

[mgravell]

Additional question: is there any particular reason you're using DynamicParameters here? it doesn't seem necessary in this case, and I'm wondering if simply not doing that fixes it

[spencermayne]

@mgravell thanks so much for the quick turnaround on this! Having looked at your test, I noticed you're referencing System.Data.SqlClient, which I wasn't aware was required, I certainly wasn't referencing prior to the framework upgrade. Adding this solves this for me :D

I had no idea about the custom in functionality, I will definitely start using this to save some dev time, thanks for the suggestion!

[mgravell]

K, in that case it sounds like this is simply a SqlClient version issue, i.e. the feature you're using requires a particular level of SqlClient; we're using the current version (4.8.1) in production quite happily, FWIW.