I have a Time(7) column in a database table, and I want to save a TimeSpan using C# and ORMLite as Object Relational Mapper. While performing this action I get this exception
Operand type clash: bigint is incompatible with time
This is my server stack trace;
[UpdateJobFrequency: 11/07/2015 5:43:39 AM]:
[REQUEST: {JobFrequency:{Id:0,JobId:3,FrequencyId:2,InstanceOfFrequency:1,Time:PT1H,WeekNumber:0}}]
System.Data.SqlClient.SqlException (0x80131904): Operand type clash: bigint is incompatible with time
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at ServiceStack.OrmLite.OrmLiteCommand.ExecuteScalar()
at ServiceStack.OrmLite.OrmLiteReadCommandExtensions.LongScalar(IDbCommand dbCmd)
at ServiceStack.OrmLite.OrmLiteResultsFilterExtensions.ExecLongScalar(IDbCommand dbCmd, String sql)
at ServiceStack.OrmLite.OrmLiteDialectProviderBase`1.InsertAndGetLastInsertId[T](IDbCommand dbCmd)
at ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.Insert[T](IDbCommand dbCmd, T obj, Boolean selectIdentity)
at ServiceStack.OrmLite.OrmLiteWriteApi.<>c__DisplayClass4`1.<Insert>b__3(IDbCommand dbCmd)
at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func`2 filter)
at ServiceStack.OrmLite.OrmLiteReadExpressionsApi.Exec[T](IDbConnection dbConn, Func`2 filter)
at ServiceStack.OrmLite.OrmLiteWriteApi.Insert[T](IDbConnection dbConn, T obj, Boolean selectIdentity)
at JobRepositoryBase.<Create>d__a`1.MoveNext() in c:\dev\RepositoryBase.cs:line 89
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at Job.WebAppHost.Service.JobFrequencyService.<Post>d__6.MoveNext() in c:\dev\JobFrequencyService.cs:line 30
I am passing 01:00:00 as TimeSpan value from the interface but it is showing as Time: PT1H in the object at server side.
Also, I have read in one of the stack overflow questionnaire that ADO.Net TimeSpan is mapped to SQL Server Time(7). And I believe that servicestack ORMLite is built upon ADO.Net. However, in this case I am facing this exception.
Any help would be highly appreciated.
Due to urgency, I had to do a workaround for the problem, I had to change the data type of my table to BigInt. So, what ORMLite is doing is mapping the BigInt as TimeSpan and vice versa - Github, now I can store and retrieve this field without any problem.
But still I believe this is not a perfect solution as in my case I am developing database from scratch, but in a case where someone has to use the existing database, this workaround fails.