DateTime not correctly handled in insert and update

[linmic30]

Describe your issue

When using DbType.DateTime as a DataType for a column, for at least sql server, the mapping to the datetime sql type does not seem to work correctly. The error that rises is that a datetime cannot be converted to timespan. This happens with an insert and an update statement.

 ---> System.InvalidCastException: Invalid cast from 'System.DateTime' to 'System.TimeSpan'.
   at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)
   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
   at System.Data.SqlClient.SqlParameter.GetCoercedValue()
   at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
   at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.BeginExecuteNonQuery(AsyncCallback callback, Object stateObject)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncImpl(Func`3 beginMethod, Func`2 endFunction, Action`1 endAction, Object state, TaskCreationOptions creationOptions)
   at System.Threading.Tasks.TaskFactory`1.FromAsync(Func`3 beginMethod, Func`2 endMethod, Object state)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken)
--- End of stack trace from previous location ---
   at LinqToDB.Data.DataConnection.ExecuteNonQueryAsync(CancellationToken cancellationToken)
   at LinqToDB.Data.DataConnection.ExecuteNonQueryAsync(CancellationToken cancellationToken)
   at LinqToDB.Data.DataConnection.ExecuteNonQueryDataAsync(CancellationToken cancellationToken)
   at LinqToDB.Data.DataConnection.ExecuteNonQueryDataAsync(CancellationToken cancellationToken)
   at LinqToDB.Data.DataConnection.QueryRunner.ExecuteNonQueryAsync(CancellationToken cancellationToken)
   at LinqToDB.Linq.QueryRunner.NonQueryQueryAsync(Query query, IDataContext dataContext, Expression expression, Object[] ps, Object[] preambles, CancellationToken cancellationToken)
   at LinqToDB.Linq.QueryRunner.NonQueryQueryAsync(Query query, IDataContext dataContext, Expression expression, Object[] ps, Object[] preambles, CancellationToken cancellationToken)
   at LinqToDB.Linq.QueryRunner.NonQueryQueryAsync(Query query, IDataContext dataContext, Expression expression, Object[] ps, Object[] preambles, CancellationToken cancellationToken)
   at LinqToDB.Linq.QueryRunner.Insert`1.QueryAsync(IDataContext dataContext, T obj, InsertColumnFilter`1 columnFilter, String tableName, String serverName, String databaseName, String schemaName, TableOptions tableOptions, CancellationToken token)
   at LinqToDB.Linq.QueryRunner.Insert`1.QueryAsync(IDataContext dataContext, T obj, InsertColumnFilter`1 columnFilter, String tableName, String serverName, String databaseName, String schemaName, TableOptions tableOptions, CancellationToken token)
   at Its.PcsBrewmax.Service.PcsBrewmaxExport.ExportPcsMessages(IAsyncEnumerable`1 exports)

Steps to reproduce

I have a large amount of tables with datetime fields and several of them have this problem. The fields are defined as follows:

[Column("POINT_IN_TIME", DataType = DataType.DateTime)]
public DateTime PointInTime { get; set; }

When looking at the SetParameter function in the SqlServerDataProvider class there is no implementation for DataType.DateTime and value is DateTime (there is one for DataType.DateTime2 on line 253). There is only for the combination DataType.DateTime and DateTimeOffset.

Workaround

When setting the DataType to LinqToDB.DataType.DateTime2 the error is not raised. Which leads me to suspect that there should be an implementation for DataType.DateTime and DateTime aswell.

Environment details

Linq To DB version: 5.4.1

Database (with version): SQL Server 2012 and SQL Server 2019

ADO.NET Provider (with version): System.Data.SqlClient 4.8.6

Operating system: W11

.NET Version: .net 8