InvalidCastException when using DateTime as parameter value for DbType.Time

[natehitze]

I'm not sure if this is a bug or something that would be nice to have documented in the porting advice document. I didn't write this code so I'm not sure if passing DateTime was a requirement for System.Data at some point or not 😄

System.Data.SqlClient worked fine with this:

p.DbType = DbType.Time;
if (val != null)
{
    var v = (LocalTime)val;
    p.Value = new DateTime(1900, 1, 1,
                           v.Hour,
                           v.Minute,
                           v.Second,
                           v.Millisecond,
                           DateTimeKind.Unspecified);
}

In Microsoft.Data.SqlClient, using that parameter gave me this exception:

System.InvalidCastException: Failed to convert parameter value from a DateTime to a TimeSpan.
at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(System.Object value, Microsoft.Data.SqlClient.MetaType destinationType, System.Boolean& coercedToDataFeed, System.Boolean& typeChanged, System.Boolean allowStreaming) in Microsoft.Data.SqlClient.SqlParameter line 745
at Microsoft.Data.SqlClient.SqlParameter.GetCoercedValue() in Microsoft.Data.SqlClient.SqlParameter line 86
at Microsoft.Data.SqlClient.SqlParameter.Validate(System.Int32 index, System.Boolean isCommandProc) in Microsoft.Data.SqlClient.SqlParameter line 139
at Microsoft.Data.SqlClient.SqlCommand.BuildParamList(Microsoft.Data.SqlClient.TdsParser parser, Microsoft.Data.SqlClient.SqlParameterCollection parameters, System.Boolean includeReturnValue) in Microsoft.Data.SqlClient.SqlCommand0
at Microsoft.Data.SqlClient.SqlCommand.BuildExecuteSql(System.Data.CommandBehavior behavior, System.String commandText, Microsoft.Data.SqlClient.SqlParameterCollection parameters, Microsoft.Data.SqlClient._SqlRPC& rpc) in Microsoft.Data.SqlClient.SqlCommand line 132
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(System.Data.CommandBehavior cmdBehavior, Microsoft.Data.SqlClient.RunBehavior runBehavior, System.Boolean returnStream, System.Boolean isAsync, System.Int32 timeout, System.Threading.Tasks.Task& task, System.Boolean asyncWrite, System.Boolean inRetry, Microsoft.Data.SqlClient.SqlDataReader ds, System.Boolean describeParameterEncryptionRequest) in Microsoft.Data.SqlClient.SqlCommand line 786
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(System.Data.CommandBehavior cmdBehavior, Microsoft.Data.SqlClient.RunBehavior runBehavior, System.Boolean returnStream, System.Threading.Tasks.TaskCompletionSource`1[System.Object] completion, System.Int32 timeout, System.Threading.Tasks.Task& task, System.Boolean& usedCache, System.Boolean asyncWrite, System.Boolean inRetry, System.String method) in Microsoft.Data.SqlClient.SqlCommand line 649
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(System.Threading.Tasks.TaskCompletionSource`1[System.Object] completion, System.Boolean sendToPipe, System.Int32 timeout, System.Boolean& usedCache, System.Boolean asyncWrite, System.Boolean inRetry, System.String methodName) in Microsoft.Data.SqlClient.SqlCommand line 197
at Microsoft.Data.SqlClient.SqlCommand.BeginExecuteNonQueryInternal(System.Data.CommandBehavior behavior, System.AsyncCallback callback, System.Object stateObject, System.Int32 timeout, System.Boolean inRetry, System.Boolean asyncWrite) in Microsoft.Data.SqlClient.SqlCommand line 52
at Microsoft.Data.SqlClient.SqlCommand.BeginExecuteNonQueryAsync(System.AsyncCallback callback, System.Object stateObject) in Microsoft.Data.SqlClient.SqlCommand0
at System.Threading.Tasks.TaskFactory`1.FromAsyncImpl(System.Func`3[System.AsyncCallback,System.Object,System.IAsyncResult] beginMethod, System.Func`2[System.IAsyncResult,TResult] endFunction, System.Action`1[System.IAsyncResult] endAction, System.Object state, System.Threading.Tasks.TaskCreationOptions creationOptions) in System.Threading.Tasks.TaskFactory`1 line 164
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQueryAsync(System.Threading.CancellationToken cancellationToken) in Microsoft.Data.SqlClient.SqlCommand0
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() in System.Runtime.ExceptionServices.ExceptionDispatchInfo line 17
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(System.Threading.Tasks.Task task) in System.Runtime.CompilerServices.TaskAwaiter line 55
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(System.Threading.Tasks.Task task) in System.Runtime.CompilerServices.TaskAwaiter line 40
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult() in System.Runtime.CompilerServices.TaskAwaiter`10

... my code truncated

I changed my parameter from a DateTime to a TimeSpan as hinted in the exception message and it works as expected.

[JRahnama]

@natehitze I was able to repro the issue you mentioned. I will get back to you shortly with more information.
The problem is reproduceable in netcore and netfx, in MDS but wont happen in SDS.
providing more detail for others who want to follow:
create the parameter v (as mentioned in the above sample code ) as DateTime add the value to parameters assign the text and try to run the code and you will see the exception

[JRahnama]

@natehitze I have found the source of the issue and I am working to see if I can make a PR to address that. Meantime as a work around try p.DbType = DbType.DateTime; instead of p.DbType = DbType.Time; to see if you can get the same results.

[natehitze]

Thank you for looking into this. I've already solved my issue by using a TimeSpan object for the value:

p.DbType = DbType.Time;
if (val != null)
{
    var v = (LocalTime)val;
    p.Value = new TimeSpan(v.Hour,
        v.Minute,
        v.Second);
}
return p;

[JRahnama]

Looking deeper to this, I noticed it is by design and the way you have solved the problem, by passing TimeSpan, is the correct way. The change started from PR #269.

[JRahnama]

Closing as this is by design.

[natehitze]

@JRahnama Thanks again for looking into it. Do you think this is worth including on the porting document?

[JRahnama]

@natehitze we will talk about this inside our team, but meantime, if you want to, feel free to make a PR and update porting-cheat-sheet.md page. We will review that and when it gets approved it will be merged.

Thanks again for the support.