Hole in the SQL Anywhere Entity Framework 4 DateTimeOffset support

I have a view in my database:

ALTER VIEW "DBA"."ReportView"
  AS SELECT "DATE"(r.EventDate) AS EventDate,
            HOUR(r.EventDate) AS Hour,
            r.ReadId AS ReadId,
            a.AlarmId,
            CASE a.AlarmStatusId WHEN 1 THEN 1 ELSE 0 END AS Correct,
            CASE a.AlarmStatusId WHEN 2 THEN 1 ELSE 0 END AS Deferred,
            CASE a.AlarmStatusId WHEN 3 THEN 1 ELSE 0 END AS Expired,
            CASE a.AlarmStatusId WHEN 4 THEN 1 ELSE 0 END AS PENDing,
            CASE a.AlarmStatusId WHEN 5 THEN 1 ELSE 0 END AS Incorrect,
            CASE a.AlarmStatusId WHEN 6 THEN 1 ELSE 0 END AS Historical
     FROM DBA.Reads AS r
      LEFT OUTER JOIN DBA.Alarms AS a ON r.ReadId = a.ReadId

And in my C# code, I have an Entity Framework 4 Entity Model which I query with the following EF4 query:

if ( alarmDomainIds != null && alarmDomainIds.Count > 0 ) {
    query = from rv in context.ReportViews
            from a  in context.Alarms
                              .Where( a => a.AlarmId == rv.AlarmId )
                              .Where( a => alarmDomainIds.Contains( a.DomainId ) )
                              .DefaultIfEmpty()
            from r  in context.Reads
                              .Where( r => r.ReadId == rv.ReadId )
                              .Where( r => readDomainIds.Contains( r.DomainId ) )
            group rv by rv.EventDate
                into dateGroup
                orderby dateGroup.Key
                select new ReportViewModel {
                    Date                 = dateGroup.Key,
                    AlarmCount           = dateGroup.Sum  ( r => r.AlarmId    == null ? 0 : 1 ),
                    CorrectAlarmCount    = dateGroup.Sum  ( r => r.Correct    == null ? 0 : r.Correct   .Value ),
                    DeferredAlarmCount   = dateGroup.Sum  ( r => r.Deferred   == null ? 0 : r.Deferred  .Value ),
                    ExpiredAlarmCount    = dateGroup.Sum  ( r => r.Expired    == null ? 0 : r.Expired   .Value ),
                    HistoricalAlarmCount = dateGroup.Sum  ( r => r.Historical == null ? 0 : r.Historical.Value ),
                    IncorrectAlarmCount  = dateGroup.Sum  ( r => r.Incorrect  == null ? 0 : r.Incorrect .Value ),
                    PendingAlarmCount    = dateGroup.Sum  ( r => r.Pending    == null ? 0 : r.Pending   .Value ),
                    ReadCount            = dateGroup.Count(),
                };
} else {
    query = from rv in context.ReportViews
            from r  in context.Reads
                              .Where( r => r.ReadId == rv.ReadId )
                              .Where( r => readDomainIds.Contains( r.DomainId ) )
            group rv by rv.EventDate
                into dateGroup
                orderby dateGroup.Key
                select new ReportViewModel {
                    Date                 = dateGroup.Key,
                    AlarmCount           = 0,
                    CorrectAlarmCount    = 0,
                    DeferredAlarmCount   = 0,
                    ExpiredAlarmCount    = 0,
                    HistoricalAlarmCount = 0,
                    IncorrectAlarmCount  = 0,
                    PendingAlarmCount    = 0,
                    ReadCount            = dateGroup.Count(),
                };
}

Everything runs fine if the Date property of the ReportViewModel class is of type DateTime. However, if the type if changed to DateTimeOffset, this query generates an "Unsupported EdmType: DateTimeOffset' error when run:

An error occurred while trying to generate the General Report A database error occurred while trying to: LPRCore.CarSystem.DataAccessException: A database error occurred while trying to generate the General Report. ---> System.Data.EntityCommandCompilationException: An error occurred while preparing the command definition. See the inner exception for details. ---> System.NotSupportedException: Unsupported EdmType: DateTimeOffset
   at iAnywhere.Data.SQLAnywhere.SqlGenerator.GetSqlPrimitiveType(TypeUsage type)
   at iAnywhere.Data.SQLAnywhere.SqlGenerator.Visit(DbCastExpression e)
   at System.Data.Common.CommandTrees.DbCastExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at iAnywhere.Data.SQLAnywhere.SqlGenerator.VisitNewInstanceExpression(DbNewInstanceExpression e)
   at iAnywhere.Data.SQLAnywhere.SqlGenerator.Visit(DbProjectExpression e)
   at System.Data.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at iAnywhere.Data.SQLAnywhere.SqlGenerator.VisitInputExpression(DbExpression inputExpression, String inputVarName, TypeUsage inputVarType, Symbol& fromSymbol)
   at iAnywhere.Data.SQLAnywhere.SqlGenerator.Visit(DbSortExpression e)
   at System.Data.Common.CommandTrees.DbSortExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at iAnywhere.Data.SQLAnywhere.SqlGenerator.VisitInputExpression(DbExpression inputExpression, String inputVarName, TypeUsage inputVarType, Symbol& fromSymbol)
   at iAnywhere.Data.SQLAnywhere.SqlGenerator.Visit(DbProjectExpression e)
   at System.Data.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at iAnywhere.Data.SQLAnywhere.SqlGenerator.VisitExpressionEnsureSqlStatement(DbExpression e, Boolean addDefaultColumns)
   at iAnywhere.Data.SQLAnywhere.SqlGenerator.GenerateSql(DbQueryCommandTree tree)
   at iAnywhere.Data.SQLAnywhere.SqlGenerator.GenerateSql(DbCommandTree tree, List`1& parameters, CommandType& commandType)
   at iAnywhere.Data.SQLAnywhere.SAProviderServices.CreateCommand(DbProviderManifest manifest, DbCommandTree commandTree)
   at iAnywhere.Data.SQLAnywhere.SAProviderServices.CreateDbCommandDefinition(DbProviderManifest manifest, DbCommandTree commandTree)
   at System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree)
   at System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
   --- End of inner exception stack trace ---
   at System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
   at System.Data.EntityClient.EntityProviderServices.CreateCommandDefinition(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
   at System.Data.EntityClient.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
   at System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree)
   at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Span span, ReadOnlyCollection`1 compiledQueryParameters, AliasGenerator aliasGenerator)
   at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at CarSystem.LPRDataAccess.ReportViewDataAccessor.GeneralReport(CarSystemEntities context, HashSet`1 alarmDomainIds, HashSet`1 readDomainIds) in d:\ElsagTFS\EOC4\Client UI\LPRDataAccess\ReportViewDataAccessor.cs:line 150
   --- End of inner exception stack trace ---
   at CarSystem.LPRDataAccess.ReportViewDataAccessor.GeneralReport(CarSystemEntities context, HashSet`1 alarmDomainIds, HashSet`1 readDomainIds) in d:\ElsagTFS\EOC4\Client UI\LPRDataAccess\ReportViewDataAccessor.cs:line 157
   at CarSystem.ServiceModel.DataInterface.GeneralReport(IDisposable connection) in d:\ElsagTFS\EOC4\Client UI\ServiceModel\DataInterface.cs:line 266

I submit that this is a bug. The work around is to keep the data type as a DateTime, but that may not always be possible.

entity-framework datetime

asked 02 Jul '13, 09:42

TonyV
1.2k●33●39●67
accept rate: 75%

Hello Tony,

Bugs can always be submitted through your technical support plan, via Case-Express for Sybase customers, or via Service Marketplace for migrated SAP customers.

If you don't currently have a technical support plan, Sybase is currently still accepting free bug reports, via Case-Express underneath 'Create Bug Report Case', but a warning that this is not a priority service and bugs will be looked at as time allows.

We'll also need more information to confirm that this is indeed a bug behaviour. Which version and build of:

SQL Anywhere
SQL Anywhere ADO.NET Provider

are you using?

Does this query need to have data in columns or is the mere presence of the data type querying empty columns in the query that causes the behaviour? Can you simplify the reproducible at all by dropping unnecessary columns? Does this behaviour have to happen on a view or can it also happen for a regular table? If it also happens with just a table, can you just provide the table definition and revised C# code?

(02 Jul '13, 10:55) Jeff Albion

Replies hidden

We're currently running SQL Anywhere 12.0.1 Build 3817 for both the server and the ADO.NET provider.

The query needs to have data in the columns. It is counting how many rows are in the view that match criteria represented by the various conditions on a given day. None of the columns are unnecessary, however, I don't think that you need all of them. You can probably get away with only one, like a call to COUNT. The problem seems to be the type of the property in the C# class that the DATE function's result is to be written to.

You can concentrate on the query in the THEN clause of the C# code. There's a little too much detail in there, sorry about that. Pretend the IF and ELSE statements aren't there.

It might be that the DATE function returns a DATETIME and that you can't assign it to a C# DateTimeOffset. That's OK, but in that case I'd say that the error message is misleading. I think in that case that it should say something more descriptive, like 'Type mismatch: Cannot assign a DateTime to a DateTimeOffset'. That would let the programmer know that the problem is that the assignment types conflict.

(02 Jul '13, 14:02) TonyV

flat view

One Answer:

active answers oldest answers newest answers popular answers

I tried upgrading to the latest build I'd downloaded, which is EBF 3895. This problem is fixed in that release. I withdraw the bug report.

permanent link

answered 02 Jul '13, 16:31

TonyV
1.2k●33●39●67
accept rate: 75%

Well, feel free to accept that as anwered by yourself:)

(02 Jul '13, 18:36) Volker Barth

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

entity-framework ×69
datetime ×24

question asked: 02 Jul '13, 09:42

question was seen: 4,032 times

last updated: 02 Jul '13, 18:37

SAP SQL Anywhere

Hole in the SQL Anywhere Entity Framework 4 DateTimeOffset support

Follow this question

Related questions