Nullable DateTimeOffset field of TVP stopped working after upgrade to v.8.0.1

[martinolivares]

I'm sending a List of ValueType to a Stored Procedure, the ValueType definition contains a nullable DateTimeOffset field. It works as expected on v6.3.11 but after upgrade I get following exception no matter the value passed on nullable DaeTimeOffset:

Microsoft.Data.SqlClient.SqlException (0x80131904): The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter 0 (""), row 1, column 1: The supplied value is not a valid instance of data type datetimeoffset. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision. The data for table-valued parameter "@datesTable" doesn't conform to the table type of the parameter

Steps to reproduce

Db Objects

CREATE TYPE dbo.DatesTable AS TABLE (
 CreatedAt datetimeoffset(0) NULL,
 ModifiedAt datetimeoffset(0) NULL
)
GO

CREATE PROCEDURE dbo.UpdateDates
(@DatesTable dbo.DatesTable READONLY)
AS
BEGIN

-- SELECT TOP 1 D.[CreatedAt]
-- FROM @DatesTable D   

END
GO

C#

 public class UpdateDate
 {
  public DateTimeOffset CreatedAt { get; set; }
  public DateTimeOffset? ModifiedAt { get; set; }
 }

// Calling code
var dates = new List<UpdateDate>
{
new UpdateDate
{
CreatedAt = DateTimeOffset.Now,
ModifiedAt = null
}
};

return _repo.UpdateDates(dates);

Expected behavior

SP call should not fail after upgrade to 8.0.1

• Dotnet version: [dotne8]
• Database: [SQL Server]
• Library version: [8.0.1]

[jonwagner]

Thanks for the repro case. I'll take a look.

[jonwagner]

I added the following test case, which passes.

Am I missing an important element? If not, then it's likely a SQL version issue.

    #region Issue 516 Tests
    [TestFixture]
    public class Issue516Tests : BaseTest
    {
        public class UpdateDate
        {
            public DateTimeOffset CreatedAt { get; set; }
            public DateTimeOffset? ModifiedAt { get; set; }
        }

        public interface Issue516TestsIConnection : IDbConnection
        {
            [Sql("UpdateDates")]
            void UpdateDates(IEnumerable<UpdateDate> dates);
        }

        [Test]
        public void TestIssue516()
        {
            try
            {
                Connection().ExecuteSql(
                    @"CREATE TYPE DatesTable AS TABLE (
                        CreatedAt datetimeoffset(0) NULL,
                        ModifiedAt datetimeoffset(0) NULL
                    )");

                Connection().ExecuteSql(
                    @"CREATE PROCEDURE UpdateDates
                        (@DatesTable DatesTable READONLY)
                        AS
                        BEGIN
                            SELECT NULL
                        END
                    ");

                    // Calling code
                    var dates = new List<UpdateDate>
                    {
                        new UpdateDate
                        {
                            CreatedAt = DateTimeOffset.Now,
                            ModifiedAt = null
                        }
                    };

                    Connection().As<Issue516TestsIConnection>().UpdateDates(dates);
            }
            finally
            {
                Connection().ExecuteSql("DROP PROC [UpdateDates]");
                Connection().ExecuteSql("DROP TYPE [DatesTable]");
            }
        }
    }
    #endregion

[martinolivares]

Test looks good. SQL version is Azure 12. Could you post the generated SQL?

[jonwagner]

Looks like a difference with the Microsoft.Data.SqlClient driver. I can reproduce it now.

[jonwagner]

narrowing it down.

this works...

                    @"CREATE TYPE DatesTable AS TABLE (
                        CreatedAt datetimeoffset NULL,
                        ModifiedAt datetimeoffset NULL
                    )");

so it has something to do with the precision:

datetimeoffset(0)

[jonwagner]

aha - looks related to:

https://github.com/dotnet/SqlClient/issues/2423

[jonwagner]

Confirmed that this is caused by Microsoft.Data.SqlClient 5.2.0.

I recommend updating to 5.2.1, where the bug is fixed. I bumped the version that Insight uses, but I don't think I'll do a build just for that.

LMK if 5.2.1 works for you.

[martinolivares]

Confirmed that works with 5.2.1

Many thanks!