Queries with DateTimeOffset Field

[felixbrueckner]

Hi there,

we use dotConnect for SQLite with Entity Framework 6 and Code First. One of the mapped classes contains a nullable DateTimeOffset (C#: DateTimeOffset?) property. This works quite well - in the corresponding SQLite Table, we get a field of type datetimeoffset.

As soon as we query this table with Linq and a filter of the form

Code: Select all

.Where( eventRecord.Timestamp > compareTimestamp  // compareTimestamp is a DateTimeOffset value

we get no result. But there ARE records with a newer value in the field Timestamp!

Here is the SQL Statement created by EF and dotConnect for SQLite:

Code: Select all

SELECT 
Project1.Id,
Project1.Payload,
Project1.Type,
Project1.EntityId,
Project1.EntityType,
Project1.EntityVersion,
Project1.ProjectId,
Project1.Timestamp,
Project1.LogicalType,
Project1.EntityPath,
Project1.HostId,
Project1.UserId,
Project1.Marker
FROM ( SELECT 
	Extent1.Id,
	Extent1.Payload,
	Extent1.Type,
	Extent1.EntityId,
	Extent1.EntityType,
	Extent1.EntityVersion,
	Extent1.ProjectId,
	Extent1.Timestamp,
	Extent1.LogicalType,
	Extent1.EntityPath,
	Extent1.HostId,
	Extent1.UserId,
	Extent1.Marker
	FROM EventRecords AS Extent1
	WHERE (Extent1.Timestamp > :p__linq__0)
)  AS Project1
ORDER BY Project1.Timestamp ASC

-- p__linq__0: '27.03.2016 11:47:57 +00:00' (Type = DateTime)

The exact same code works perfectly with the original MS SQL Server Provider.

Is this related to this problem concerning DateTimeOffset type in SQLite?:
https://github.com/praeclarum/sqlite-net/issues/360

What can we do here?

Regards
Felix

[Shalex]

Be aware that DateTimeOffset is not supported by SQLite engine natively (https://www.sqlite.org/datatype3.html), it is actually a text field within SQLite database.

Is this related to this problem concerning DateTimeOffset type in SQLite?:
https://github.com/praeclarum/sqlite-net/issues/360

Yes, this is.

What can we do here?

Possible workarounds:
1) call ToList() to materialize collection and, then, filter it by the DateTimeOffset property
2) create a duplicated column of the DateTime type (put values here in the UTC format). Use it in the Where clause
3) migrate your DateTimeOffset column to the DateTime one and store data in it using the UTC format