DapperLib / DapperLib/Dapper

Dapper TVP mapping via ordinal instead of by name?

Open
#306 17 comments 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C#
Stars
18.4k
Forks
3.7k
Avg merge
5h 8m
Merged PRs (30d)
1

Description

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;
        }
    }

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the TestCorrectMapping and TestInCorrectMapping examples and inspect the IEnumberableExtensions.ToDataTable method shown in the report. Determine whether the ordinal behavior comes from the DataTable construction, Dapper, or SQL Server TVP handling. Done should mean the requested name-based mapping or a clear mismatch error is demonstrated by a regression test.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
backend, database
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.