[bug?] Cannot call methods on datetimeoffset

[Arvi]

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

[Arvi]

Forgot to mention it worked correctly in 2013.3.76.4024

[Thomas]

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

 

 

[Arvi]

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

[Thomas]

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

 

 

[Warren]

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)
             };

[Thomas]

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