How do I pass a table-valued parameter to Dapper in .NET Core?

Question

I am using .NET Core and Dapper. My problem is that .NET Core doesn't have DataTables, and that's what Dapper uses for table-valued parameters (TVP).

I was trying to convert a List<T> to a List<SqlDataRecord>, create a SqlParameter with this list and then convert it to a DynamicParameter:

public static SqlParameter toTVP<T>(this IList<T> enumerable, string name)
{
    List<SqlDataRecord> records = new List<SqlDataRecord>();
    // filter allowed types
    var properties = typeof(T).GetProperties().Where(p => Mapper.TypeToSQLMap.ContainsKey(p.PropertyType)); 
    var definitions = properties.Select(p => Mapper.TypeToMetaData(p.Name,p.PropertyType)).ToArray();
    foreach(var item in enumerable)
    {
        var values = properties.Select(p => p.GetValue(item, null)).ToArray();
        var schema = new SqlDataRecord(definitions);
        schema.SetValues(values);
        records.Add(schema);
    }

    SqlParameter result = new SqlParameter(name, SqlDbType.Structured);
    result.Direction = ParameterDirection.Input;
    result.TypeName = $"{name}Type";
    result.Value = records;
    return result;
}

and then:

var structured = MyList.toTVP("Test");
var p = new DynamicParameters(new { });
p.Add(structured.ParameterName,structured.Value);

var result = con.Query(query, p);

But I got an error:

The member of type Microsoft.SqlServer.Server.SqlDataRecord cannot be used as a parameter value.

Is there a way I can make this work?

Answer 1

As of Dapper 2.0, TVPs are natively supported. There's some sample code is available on GitHub:

https://github.com/yorek/dapper-samples/blob/master/Dapper.Samples.Advanced/SQLServerFeatures.cs

For pre-2.0 TVPs, you would need to use .NET Framework, where you can use the .AsTableValuedParameter extension methods, but you don't have this option in .NET Core (as of Dapper v 1.5). To solve the problem you have to create a class that implements ICustomQueryMapper:

public class TabledValuedParameter: ICustomQueryMapper
{
    public void AddParameter() {...}
}

And then you can use it to wrap your IEnumerable. I've written an article on the subject here:

https://medium.com/dapper-net/sql-server-specific-features-2773d894a6ae

Answer 2

Asker's solution moved to an answer:

After playing a bit with IDynamicParameters, I made it work.

Extension method for IEnumerable

public static DynamicWrapper toTVP<T>(this IEnumerable<T> enumerable, string tableName, string typeName)
{
    List<SqlDataRecord> records = new List<SqlDataRecord>();
    var properties = typeof(T).GetProperties().Where(p => Mapper.TypeToSQLMap.ContainsKey(p.PropertyType));
    var definitions = properties.Select(p => Mapper.TypeToMetaData(p.Name, p.PropertyType)).ToArray();
    foreach (var item in enumerable)
    {
        var values = properties.Select(p => p.GetValue(item, null)).ToArray();
        var schema = new SqlDataRecord(definitions);
        schema.SetValues(values);
        records.Add(schema);
    }

    SqlParameter result = new SqlParameter(tableName, SqlDbType.Structured);
    result.Direction = ParameterDirection.Input;
    result.TypeName = typeName;
    result.Value = records;
    return new DynamicWrapper(result);
}

Wrapper to implement IDynamicParameters

public class DynamicWrapper : IDynamicParameters
{
    private readonly SqlParameter _Parameter;
    public DynamicWrapper(SqlParameter param)
    {
        _Parameter = param;
    }

    public void AddParameters(IDbCommand command, Identity identity)
    {
        command.Parameters.Add(_Parameter);
    }
}

Mapper (not fully tested, only managed string to NVARCHAR because it throws an exception without maxLength)

public class Mapper
{
    public static Dictionary<Type, SqlDbType> TypeToSQLMap = new Dictionary<Type, SqlDbType>()
        {
              {typeof (long),SqlDbType.BigInt},
              {typeof (long?),SqlDbType.BigInt},
              {typeof (byte[]),SqlDbType.Image},
              {typeof (bool),SqlDbType.Bit},
              {typeof (bool?),SqlDbType.Bit},
              {typeof (string),SqlDbType.NVarChar},
              {typeof (DateTime),SqlDbType.DateTime2},
              {typeof (DateTime?),SqlDbType.DateTime2},
              {typeof (decimal),SqlDbType.Money},
              {typeof (decimal?),SqlDbType.Money},
              {typeof (double),SqlDbType.Float},
              {typeof (double?),SqlDbType.Float},
              {typeof (int),SqlDbType.Int},
              {typeof (int?),SqlDbType.Int},
              {typeof (float),SqlDbType.Real},
              {typeof (float?),SqlDbType.Real},
              {typeof (Guid),SqlDbType.UniqueIdentifier},
              {typeof (Guid?),SqlDbType.UniqueIdentifier},
              {typeof (short),SqlDbType.SmallInt},
              {typeof (short?),SqlDbType.SmallInt},
              {typeof (byte),SqlDbType.TinyInt},
              {typeof (byte?),SqlDbType.TinyInt},
              {typeof (object),SqlDbType.Variant},
              {typeof (DataTable),SqlDbType.Structured},
              {typeof (DateTimeOffset),SqlDbType.DateTimeOffset}
        };

    public static SqlMetaData TypeToMetaData(string name, Type type)
    {
        SqlMetaData data = null;

        if (type == typeof(string))
        {
            data = new SqlMetaData(name, SqlDbType.NVarChar, -1);
        }
        else
        {
            data = new SqlMetaData(name, TypeToSQLMap[type]);
        }

        return data;
    }
}

SQL Type for my example:

CREATE TYPE TestType AS TABLE ( 
  FirstName NVARCHAR(255)  
    , GamerID INT 
  , LastName NVARCHAR(255)
  , Salt UNIQUEIDENTIFIER);  
GO  

Using it:

List<Gamer> gamers = new List<Gamer>();

gamers.Add(new Gamer {
                Email = new string[] { "dsadsdsa@dasddas.com" },
                FirstName = "Test_F0",
                LastName = "Test_L0",
                GamerID = 0,
                Salt = Guid.NewGuid()});

            gamers.Add(new Gamer {
                Email = new string[] { "11111@11111.com" },
                FirstName = "Test_F1",
                LastName = "Test_L1",
                GamerID = 1,
                Salt = Guid.NewGuid()});

            var structured = gamers.toTVP("GamerTable", "dbo.TestType");

            using (var con = new SqlConnection(TestConnectionString))
            {
                con.Open();

                string query = @"

              SELECT * 
              FROM @GamerTable t
              WHERE t.GamerID = 1

                ";

var result = con.Query(query, structured);

//var result = con.Query("dbo.DapperTest", structured, commandType: CommandType.StoredProcedure);

As you can see, the model stripped out the array of strings for emails, coz I didn't code it to have nested tvp. (TypeToSQLMap.ContainsKey part), but could be coded, changing the wrapper to accept an enumerable of parameters and AddParameters to foreach and add them. Is more about a problem with the types names, etc. I was thinking to create some generic types named based on the property types. For now, this is enough, feel free to upgrade it if i dont do it.