search

jonwagner / Insight.Database

Fast, lightweight .NET micro-ORM
Other
861 stars 145 forks source link

Metadata for field 'DateTime' of record '2' did not match the original record's metadata. #390

Closed radleta closed 3 years ago

radleta commented 5 years ago

Describe the bug

I updated from 5.2.8 to 6.2.8 and now get this error on calling a stored procedure with a table type with a list.

System.ArgumentException: Metadata for field 'DateTime' of record '2' did not match the original record's metadata.
   at System.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Insight.Database.DBConnectionExtensions.<>c__DisplayClass164_0.<Execute>b__1(IDbCommand _, IDataReader __)
   at Insight.Database.DBConnectionExtensions.ExecuteAndAutoClose[T](IDbConnection connection, Func`2 getCommand, Func`3 translate, CommandBehavior commandBehavior)
   at Insight.Database.DBConnectionExtensions.Execute(IDbConnection connection, String sql, Object parameters, CommandType commandType, Boolean closeConnection, Nullable`1 commandTimeout, IDbTransaction transaction, Object outputParameters)

Steps to reproduce

The C# looks like this:

ConnectionStringManager.CreateSqlConnection(ConnectionStringName)
                        .Execute("MyProc", new { Input = rows }, commandTimeout: 60);

The signature of the procedure looks like this:

ALTER PROCEDURE [dbo].[MyProc]
    @Input MyTableType READONLY
AS

The table type looks like this:

CREATE TYPE [dbo].[MyTableType] AS TABLE(
    [Col1] [varchar](100) NULL,
    [Col2] [varchar](100) NULL,
    [DateTime] [datetime] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [bigint] NULL,
    [Col6] [bigint] NOT NULL DEFAULT ((0)),
    [Col7] [bigint] NOT NULL DEFAULT ((0))
)

Expected behavior

I saw this issue or something like it was fixed in 6.2.8. I wanted to report it just in case some case was overlooked or my case was new.

radleta commented 5 years ago

I can confirm that rolling back to 6.2.5 resolves the issue.

ellerbus commented 5 years ago

I can confirm this is an issue with the 451 version of Insight whereas it is working with netstandard. I have two projects one written in netstandard and the other in net47. Issue #386 resolved my netstandard project but when I upgraded from 6.2.6 to 6.2.8 for my net47 project I get the meta-data error mentioned above.

The C# DateTime property is being mapped to the MS-SQL DataType of datetime2 using the 451 version of Insight instead of the datetime that is listed on my TableType.

After some digging I found that this stems from a DataType mapping issue in SqlDataRecordAdapter Line 58 when it is calling GetSqlDbType at Line 105 with a NULL for dataTypeName thereby relying on the static map at Line 21

I was able to resolve this particular issue by changing Line 38 as follows:

    { typeof(DateTime), SqlDbType.DateTime },    <-- removed the "2"
    { typeof(DateTime?), SqlDbType.DateTime2 },  <-- added for nullable date

Unfortunately, I don't know the library well enough to make that simple of a code recommendation.

My previous update was invalid based on this comment.

Digging more into the code base it looks like the root cause is the construction of the Column Info object as part of the FromDataReader method at Line 117 in that it is not setting the "DataTypeName" but is for #if !NO_COLUMN_SCHEMA at Line 55

falvarador commented 5 years ago

Is there a fix for this problem for version 6.2.8? For now I solved it by going back to version 6.2.5, but I would like to have more information about it.

alanbucknum commented 5 years ago

Having the same problem, only when there are more than one items in the datetime list.

jonwagner commented 5 years ago

@ellerbus - good catch. This seems to be the missing test case I was looking for. I do most of my dev now on NETCORE/OSX.

The fix should be in 6.2.9 now. Thanks for all of the help everyone.

heinrichdude commented 3 years ago

Was working in the latest stable version 6.3.4 and this issue seems to have resurfaced. Rolled back to 6.2.9 and it is fixed. My project is targeting .NET Core 3.1. Can anyone confirm that this issue is back?

lawrencek76 commented 3 years ago

Yes, I ran into this the other day also but have not had a chance to look into it.

heinrichdude commented 3 years ago

I also noted a datetime2 related error when issuing a Find like the following ... var addresses = addressRepository.Find(new Address { AddressTypeId = 2 }); // This version fails var addresses = addressRepository.Find(new { AddressTypeId = 2 }); // This version works

... using version 6.2.9