[bug?] Cannot call methods on datetimeoffset

7 posts, 0 answers
  1. Arvi
    Arvi avatar
    3 posts
    Member since:
    Feb 2013

    Posted 23 Jul 2014 Link to this post

    Hi,

    I'm using Open Acces 2014.2. on SQL Server 2012 SP2

    When I performe the following LINQ query:

    var result = db.Invoices.GroupBy(x => new {x.CreationTime.Date.Year, x.CreationTime.Date.Month}).Select(x => new { Month = x.Key.Month, Year = x.Key.Year }).ToList()

    It produces the following sql query:

    SELECT a.[CreationTime].[Year] AS  EXPR1, a.[CreationTime].[Month] AS  EXPR2 FROM [Invoices] a GROUP BY a.[CreationTime].[Year], a.[CreationTime].[Month]

    That produces a Telerik.OpenAccess.RT.sql.SQLException: Cannot call methods on datetimeoffset.

    Should it not generate a sql query like:

    SELECT YEAR(a.[CreationTime]) AS  EXPR1, MONTH(a.[CreationTime]) AS  EXPR2 FROM [Invoices] a GROUP BY YEAR(a.[CreationTime]), MONTH(a.[CreationTime])

    Here is the trace:

    [SqlException (0x80131904): Cannot call methods on datetimeoffset.]
       System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +392
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +815
       System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4515
       System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +61
       System.Data.SqlClient.SqlDataReader.get_MetaData() +138
       System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6738869
       System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +6741487
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +586
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +107
       System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +288
       System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +180
       OpenAccessRuntime.CommandWrapper.ExecuteReader(CommandBehavior behavior) +288
       Telerik.OpenAccess.Runtime.Logging.LoggingDbCommand.ExecuteDbDataReader(CommandBehavior behavior) +440
       Telerik.OpenAccess.RT.Adonet2Generic.Impl.CommandImp.ExecuteReader() +210
       Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery(Nullable`1 commandTimeout) +277
     
    [SQLException: Cannot call methods on datetimeoffset.]
       Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery(Nullable`1 commandTimeout) +688
       OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery(Nullable`1 commandTimeout) +153
       OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute() +97
     
    [DataStoreException: Error executing query: Telerik.OpenAccess.RT.sql.SQLException: Cannot call methods on datetimeoffset. ---> System.Data.SqlClient.SqlException: Cannot call methods on datetimeoffset.
       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.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at OpenAccessRuntime.CommandWrapper.ExecuteReader(CommandBehavior behavior)
       at Telerik.OpenAccess.Runtime.Logging.LoggingDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.CommandImp.ExecuteReader()
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery(Nullable`1 commandTimeout)
       --- End of inner exception stack trace ---
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery(Nullable`1 commandTimeout)
       at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery(Nullable`1 commandTimeout)
       at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()
    SQL:
    SELECT YEAR(a.[CreationTime].[Date]) AS  EXPR1, MONTH(a.[CreationTime].[Date]) AS  EXPR2 FROM [Invoices] a GROUP BY YEAR(a.[CreationTime].[Date]), MONTH(a.[CreationTime].[Date])  Telerik.OpenAccess.RT.sql.SQLException: Cannot call methods on datetimeoffset. ---> System.Data.SqlClient.SqlException: Cannot call methods on datetimeoffset.
       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.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at OpenAccessRuntime.CommandWrapper.ExecuteReader(CommandBehavior behavior)
       at Telerik.OpenAccess.Runtime.Logging.LoggingDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.CommandImp.ExecuteReader()
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery(Nullable`1 commandTimeout)
       --- End of inner exception stack trace ---
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery(Nullable`1 commandTimeout)
       at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery(Nullable`1 commandTimeout)
       at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()]
       OpenAccessRuntime.ExceptionWrapper.Throw() +13
       OpenAccessRuntime.storagemanager.logging.LoggingStorageManager.fetchNextQueryResult(ApplicationContext context, RunningQuery runningQuery, Int32 skipAmount) +917
       OpenAccessRuntime.DataObjects.ForwardQueryResult.Initialize() +274
       OpenAccessRuntime.DataObjects.ForwardQueryResult.get_Item(Int32 indexParam) +90
       OpenAccessRuntime.ListEnumerator.setCurrent(Int32 _pos) +58
       OpenAccessRuntime.ListEnumerator.Move(Int32 relative) +97
       Telerik.OpenAccess.Query.TypedEnumerator`1.MoveNext() +32
       System.Linq.Buffer`1..ctor(IEnumerable`1 source) +520
       System.Linq.<GetEnumerator>d__0.MoveNext() +145
       System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +536
       System.Linq.Enumerable.ToList(IEnumerable`1 source) +80
       AccountManagement.BusinessLogic.Repositories.InvoiceRepository.InventorySellMonthlyRaportDates() in c:\Users\arvi.saluste\My Projects\AccountManagement\BusinessLogic\Repositories\InvoiceRepository.cs:728
       AccountManagement.Controllers.ReportsController.InventorySellMonthlyRaport() in c:\Users\arvi.saluste\My Projects\AccountManagement\AccountManagement\Controllers\ReportsController.cs:22
       lambda_method(Closure , ControllerBase , Object[] ) +81
       System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +242
       System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +39
       System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState) +12
       System.Web.Mvc.Async.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult) +139
       System.Web.Mvc.Async.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() +112
       System.Web.Mvc.Async.<>c__DisplayClass48.<InvokeActionMethodFilterAsynchronouslyRecursive>b__41() +452
       System.Web.Mvc.Async.<>c__DisplayClass33.<BeginInvokeActionMethodWithFilters>b__32(IAsyncResult asyncResult) +15
       System.Web.Mvc.Async.<>c__DisplayClass2b.<BeginInvokeAction>b__1c() +37
       System.Web.Mvc.Async.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult) +241
       System.Web.Mvc.Controller.<BeginExecuteCore>b__1d(IAsyncResult asyncResult, ExecuteCoreState innerState) +29
       System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +111
       System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +53
       System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +19
       System.Web.Mvc.MvcHandler.<BeginProcessRequest>b__5(IAsyncResult asyncResult, ProcessRequestState innerState) +51
       System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +111
       System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +606
       System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +288

  2. Arvi
    Arvi avatar
    3 posts
    Member since:
    Feb 2013

    Posted 23 Jul 2014 in reply to Arvi Link to this post

    Forgot to mention it worked correctly in 2013.3.76.4024
  3. DevCraft banner
  4. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 23 Jul 2014 Link to this post

    Hello Avri,

    I will need to see if that is/why a regression. However, I think the solution is to use

    var result = db.Invoices.GroupBy(x => new {x.CreationTime.Year, x.CreationTime.Month}).Select(x => new { Month = x.Key.Month, Year = x.Key.Year }).ToList();

    So avoiding the .Date property should solve the issue.

    Regards,
    Thomas
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  5. Arvi
    Arvi avatar
    3 posts
    Member since:
    Feb 2013

    Posted 23 Jul 2014 in reply to Thomas Link to this post

    Hi,

    Sorry the initial post had a typo, the linq query should have been without the .Date

    var result = db.Invoices.GroupBy(x => new {x.CreationTime.Year, x.CreationTime.Month}).Select(x => new { Month = x.Key.Month, Year = x.Key.Year }).ToList()

    I tried several different linq query's and the one with .Date returned a sql query

    SELECT YEAR(a.[CreationTime].[Date]) AS  EXPR1, MONTH(a.[CreationTime].[Date]) AS  EXPR2 FROM [Invoices] a GROUP BY YEAR(a.[CreationTime].[Date]), MONTH(a.[CreationTime].[Date])

    Regards,
    Arvi
  6. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 23 Jul 2014 Link to this post

    Hi Arvi,

    gotcha! The DateTimeOffset CLR type seems to be not handled properly yet. I filed a bug for this and in an upcoming version there will be handling for DateTimeOffset properties and methods.
    You could switch to DateTime for the time being, this should avoid the issue.
    Thanks for reporting this issue with us!

    Regards,
    Thomas
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  7. Warren
    Warren avatar
    1 posts
    Member since:
    Dec 2012

    Posted 09 Sep 2015 Link to this post

    Can you tell me if this is still a problem in the latest version of Telerik Data Access?  We are having a similar issue with a DateTimeOffset sql type/clr type column.

    var result = from c in model.table
                 group c by c.fieldstr into g
                 select new objA {
                      key = g.fieldstr,
                      value = Max(g.datetimeoffsetValue)
                 };

  8. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 14 Sep 2015 Link to this post

    Hello Warren,

    I tried with the latest version, and there is still some minor issue concerning the Max functionality.
    However, you can work around it by using constructs similar to 

    using Telerik.DataAccess;

    var result =
        from c in model.table
        group c by c.fieldstr into g
        select new {
            key = g.Key,
            value = "{0}".SQL<DateTimeOffset>(g.Max(z => z.datetimeoffsetValue))
        };

    The strange SQL extension method is needed because the Max function seems not to be handling the result type well (assumes Int16 instead of DateTimeOffset). I will file another bug for this. Until this is fixed, the SQL method ensures that the DateTimeOffset value is properly fetched from the server.

    Regards,
    Thomas
    Telerik
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Back to Top
DevCraft banner