Dapper TVP mapping via ordinal instead of by name?

[Ashtonian]

I have a user defined table type in MS SQL Server 2012 and a equivalent class in C#. It seems as though even though the C# Data Table columns are named appropriately - if the class property order does not line up with the TVP order then this can result in a miss match of data. This isn't that big of a deal when it fails loudly - lets say because of a data type mismatch - cause then I know something's wrong, but if the datatypes are the same this can result in badly mapped data that has silently been used.

I've tried to provide enough code to demo it appropriately. I hope there is not an underlying reason why this is the case. I was wondering if there is a way that a developer could enforce mapping via column name? or somehow provide an error when the columns don't match. The test TestInCorrectMapping below fails and illustrates the problem I am describing.

The only extra dependency below is fluent validation library.

SQL:

CREATE TABLE [dbo].[LifeDates](
    [BirthDate] [DATETIME2](7) NOT NULL,
    [DeathDate] [DATETIME2](7) NOT NULL
) ON [PRIMARY]
GO

CREATE TYPE dbo.LifeDatesType AS TABLE
(
    BirthDate DATETIME2,
    DeathDate DATETIME2
)
GO

CREATE PROCEDURE dbo.InsertLifeDates
    @Dates LifeDatesType READONLY
AS
    BEGIN
        INSERT  INTO dbo.LifeDates
                (
                 BirthDate
                ,DeathDate
                )
                SELECT
                    BirthDate
                   ,DeathDate
                FROM
                    @Dates;
    END;

C#

    public class CorrectLifeDatesType
    {
        public DateTime Birthdate { get; set; }
        public DateTime DeathDate { get; set; }
    }

    public class InCorrectLifeDatesType
    {
        public DateTime DeathDate { get; set; }
        public DateTime Birthdate { get; set; }
    }

// PASSES as it should
    public class TVPTest
    {
        [Test]
        public void TestCorrectMapping()
        {
            var lifeDates = new List< CorrectLifeDatesType >()
            {
                new CorrectLifeDatesType()
                {
                    Birthdate = DateTime.Now.AddYears( -100 ).Date,
                    DeathDate = DateTime.Now.Date
                }
            };

            using( var connection = new SqlConnection( @"" ) )
            {
                connection.Execute( "TRUNCATE TABLE LifeDates" );

                connection.Query( "InsertLifeDates",
                                  new
                                  {
                                      @Dates = lifeDates.ToDataTable().AsTableValuedParameter( "LifeDatesType" )
                                  },
                                  commandType : CommandType.StoredProcedure );

                var results = connection.Query< CorrectLifeDatesType >( "SELECT * FROM LifeDates" ).ToList();

                results.First().Birthdate.Should().Be( lifeDates.First().Birthdate );
                results.First().DeathDate.Should().Be( lifeDates.First().DeathDate );
            }
        }

 // FAILS
 // Shows that the data was inserted successfully however was mapped incorrectly, and therefore has silently failed. 
        [Test]
        public void TestInCorrectMapping()
        {
            var lifeDates = new List< InCorrectLifeDatesType >()
            {
                new InCorrectLifeDatesType()
                {
                    Birthdate = DateTime.Now.AddYears( -100 ).Date,
                    DeathDate = DateTime.Now.Date
                }
            };

            using( var connection = new SqlConnection( @"" ) )
            {
                connection.Execute( "TRUNCATE TABLE LifeDates" );

                connection.Query( "InsertLifeDates",
                                  new
                                  {
                                      @Dates = lifeDates.ToDataTable().AsTableValuedParameter( "LifeDatesType" )
                                  },
                                  commandType : CommandType.StoredProcedure );

                var results = connection.Query< InCorrectLifeDatesType >( "SELECT * FROM LifeDates" ).ToList();

                results.First().Birthdate.Should().Be( lifeDates.First().Birthdate );
                results.First().DeathDate.Should().Be( lifeDates.First().DeathDate );
            }
        }


    }

    public static class IEnumberableExtensions
    {
        public static DataTable ToDataTable( this IEnumerable< object > data, IEnumerable< string > ignoredColumns = null )
        {
            var firstRecord = data.FirstOrDefault();

            var props = TypeDescriptor.GetProperties( firstRecord.GetType() );
            var properties = props.Cast< PropertyDescriptor >().ToList();

            if( ignoredColumns != null && !ignoredColumns.Any() )
                properties = properties.Where( p => !ignoredColumns.Contains( p.Name ) ).ToList();

            var table = new DataTable();
            foreach( var prop in properties )
                table.Columns.Add( prop.Name, Nullable.GetUnderlyingType( prop.PropertyType ) ?? prop.PropertyType );

            var values = new object[properties.Count];

            foreach( var item in data )
            {
                for( var i = 0; i < properties.Count; i++ )
                    values[ i ] = properties[ i ].GetValue( item ) ?? DBNull.Value;

                table.Rows.Add( values );
            }
            return table;
        }
    }

[johandanforth]

I've never used TVP myself but I had a look at this a bit and it seems that it's the SqlCommand/IdbCommand messing things up, right? I get the same results/problems without Dapper with this code if I switch the order of the props:

var param = new SqlParameter
{
    ParameterName = "Dates",
    Value = lifeDates.ToDataTable(),
    SqlDbType = SqlDbType.Structured
};

var cmd = new SqlCommand("InsertLifeDates", connection as SqlConnection)
{
    CommandType = CommandType.StoredProcedure
};
cmd.Parameters.Add(param);

cmd.ExecuteNonQuery();

As you said - perhaps there's another way of doing it, but something in the way dbcommand is handling the tabledata value is weird. It doesn't seem Dapper has anything to do with this anyway.

[Ashtonian]

Sorry for the late response. You are correct, this a huge bummer with tvp.

[Ashtonian]

So would there be anyway to add an extension so that this could validate the data table column names + order with the table TVP?

I'm not sure how you would get the columns of the TVP out of the db in C# land?

[DarekDan]

Actually it gets easier, this code uses DMVs to get what you need (ordered)

SELECT c.name FROM sys.table_types tt INNER JOIN sys.columns c ON c.object_id = tt.type_table_object_id WHERE tt.name = 'AUDITRECORD' ORDER BY c.column_id;

where AUDITRECORD is the TVP type you are after.

[drusellers]

I just ran into this. 👎

[concubicycle]

Sucks

[dniggeler]

I just run into it, too. :-1:

[surenderssm]

It's hard to believe that we are having these set of issues in 2019.
Same issue is observed in classical System.Data.SqlClient as well.

[JohnnyFun]

To populate the @tvp parameter and run that sql, dapper is generating:

declare @tvp sometabletype-- or whatever type you're using
insert into @tvp
values (col1, col2, col3),
(col1, col2, col3),
(col1, col2, col3) -- repeats for every row in the tvp

insert into [table] (col1, col2, col3)
select col1, col2, col3 from @tvp

I was expecting it to generate:

declare @tvp sometabletype -- or whatever type you're using
insert into @tvp (col1, col2, col3) -- explicit declaration of columns being inserted
values (col1, col2, col3),
(col1, col2, col3),
(col1, col2, col3) -- repeat for every row in the tvp

insert into [table] (col1, col2, col3)
select col1, col2, col3 from @tvp

Seems like it wouldn't be too difficult to just add to the insert statement the csv of columns surrounded by parenthesis, correct? Or am I missing some gotcha?

[mgiacone]

I ran into this too, we use a method to transform a class into a tvp and pass it to a stored procedure and if the property are not in the same order of the tvp declaration it simply won't work. This is easy to fix in simple class but when class inherit from other class this become complex

[DMEvans]

I have just come across this same problem. To fix it on my side I was able to change the column order in the SQL table type. I was lucky in the fact that one of my TVP columns was non-nullable and a null value intended for a different column caused the error, which made the problem apparent very quickly.

It's a shame that this seems to be an issue in the underlying System.Data.SqlClient (if I've understood correctly), since I guess it means we won't see a fix?

[JohnnyFun]

@DMEvans I think it's just a matter of providing the column names with the insert--see my comment above. Looks like dapper might just need to be updated to explicitly provide column names in the insert.

[DMEvans]

@JohnnyFun I did see your comment. I believe what @johandanforth and @surenderssm are saying is that Dapper does not control this part of the query generation, amd that it is handled by .NET's SQL client. If there is a way to hijack that part of the query generation then please share it with us.

[JohnnyFun]

I think ToDataTable is a dapper function. This SO example uses a datatable. I'm going to try some stuff later today and see if I can come up with some options and a proof of concept to make it so your c# props don't have to be in the same order as your db table type.

https://stackoverflow.com/questions/6989560/how-to-pass-a-table-value-parameter

[JohnnyFun]

Ok, it works! You can query the table type like so:

SELECT *
FROM sys.columns
WHERE object_id IN (
  SELECT type_table_object_id
  FROM sys.table_types
  WHERE name = 'LifeDatesType'
);

There is a column "column_id" that seems to determine the order of the columns.

So that could be used to build up the metadata for a SqlDataRecord. Here is sql client inserting using the InCorrectLifeDatesType but ordering the metadata correctly:

private static async Task InsertTableValuedParameter() {
    var connectionString = $"Data Source=.\\SQLExpress;Initial Catalog=tvptesting;Integrated Security=True;";
    using (var connection = new SqlConnection(connectionString)) {
        await connection.OpenAsync();
        var lifeDates = new List<InCorrectLifeDatesType>()
        {
            new InCorrectLifeDatesType()
            {
                Birthdate = DateTime.Now.AddYears( -100 ).Date,
                DeathDate = DateTime.Now.Date
            }
        };
        var sqlDataRecords = lifeDates.Select(ld => {
            var meta = new SqlMetaData[2];
            // TODO: pull the table type column info from sql (query above) and use that to build the metadata below. And insert the metadata info into cache in dapper like they do with other reflection-built goodies
            meta[0] = new SqlMetaData("Birthdate", SqlDbType.DateTime2);
            meta[1] = new SqlMetaData("Deathdate", SqlDbType.DateTime2);
            var record = new SqlDataRecord(meta);
            record.SetDateTime(0, ld.Birthdate);
            record.SetDateTime(1, ld.DeathDate);
            return record;
        });
        var param = new SqlParameter {
            ParameterName = "Dates",
            Value = sqlDataRecords,
            SqlDbType = SqlDbType.Structured
        };
        var cmd = new SqlCommand("InsertLifeDates", connection as SqlConnection) {
            CommandType = CommandType.StoredProcedure
        };
        cmd.Parameters.Add(param);
        await cmd.ExecuteNonQueryAsync();
    }
}

I confirmed with sql profiler that sqlclient indeed doesn't supply the column values for the resulting insert, but as long as dapper uses the table type to put the values in the correct order, should be good. I suppose maybe they want to keep the query size down, but would probably reduce a lot of confusion like this if they put the column names in the insert. So in that way, it's _kinda_ sqlclient's fault, but there is definitely a reasonably sane way around it, as shown above.

declare @p1 dbo.LifeDatesType
insert into @p1 values('1919-11-21 00:00:00','2019-11-21 00:00:00')

exec InsertLifeDates @Dates=@p1

I think that'd be enough to go on if someone has time to do the PR (I'm going to be away for a week, otherwise I'd look into it). I'm not sure of all the gotchas, but I think that's the meat of it? Someone who knows dapper more deeply might be able to provide more insight though.

[MarkFleet]

I have also just ran into this. Did anyone manage to do the PR?