[#1011101] LINQ to entities timestamptz does not accept DateTimeOffset or DateTime

View Trackers | Bugs | Download .csv | Monitor

2011-10-06 07:36
Submitted by:
Misha Sugakov (alabax)
Assigned to:
Nobody (None)
Npgsql Version:
LINQ to entities timestamptz does not accept DateTimeOffset or DateTime

Detailed description
Excuse me for double posting this issue , but I believe it's a bug. Timestamptz column in the linq to entities query allows comparison neither with DateTimeOffset nor with DateTime values. I use the following query with EF 4.0 and Npgsql

var e = (from u in myEntities.user
where u.deletedate <= DateTimeOffset.UtcNow
select u); // Statement #1

Console.WriteLine(((System.Data.Objects.ObjectQuery)e).ToTraceString()); // Statement #2

foreach (var v in e) // Statement #3

When the code is executed an exception is thrown on statement #3:

System.InvalidCastException: Can't cast System.DateTimeOffset into any valid DbType.
at Npgsql.NpgsqlParameter.set_Value(Object value)
at System.Data.EntityClient.EntityCommandDefinition.SyncParameterProperties(EntityParameter entityParameter, DbParameter storeParameter, DbProviderServices storeProviderServices)
at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at MyApplication.Program.Main(String[] args) in D:\MyApplication\Program.cs:line 78

If I change DateTimeOffset.UtcNow to DateTime.UtcNow, new exception is thrown on statement #2:

System.Data.EntityCommandCompilationException: An error occurred while preparing the command definition. See the inner exception for details. ---> System.NotSupportedException: Specified method is not supported.
at Npgsql.SqlGenerators.SqlBaseGenerator.GetDbType(EdmType edmType)
at Npgsql.SqlGenerators.SqlBaseGenerator.Visit(DbCastExpression expression)
at Npgsql.SqlGenerators.SqlBaseGenerator.Visit(DbComparisonExpression expression)
at Npgsql.SqlGenerators.SqlBaseGenerator.VisitFilterExpression(DbFilterExpression expression, Boolean partOfJoin, VisitedExpression joinCondition)
at Npgsql.SqlGenerators.SqlBaseGenerator.Visit(DbSortExpression expression)
at Npgsql.SqlGenerators.SqlBaseGenerator.Visit(DbProjectExpression expression)
at Npgsql.SqlGenerators.SqlSelectGenerator.BuildCommand(DbCommand command)
at Npgsql.NpgsqlServices.CreateDbCommand(DbCommandTree commandTree)
at Npgsql.NpgsqlServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, 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.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Span span, ReadOnlyCollection`1 compiledQueryParameters)
at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
at System.Data.Objects.ObjectQuery.ToTraceString()
at MyApplication.Program.Main(String[] args) in D:\MyApplication\Program.cs:line 125


Date: 2011-12-27 01:53
Sender: Francisco Figueiredo jr.

Hi, Misha!

I'm glad it is working ok in your code.

Would you mind to open another bug report about this timezone problem?

I remember we already have a lot of complains about timezone issues within Npgsql and I think your report would help us fix this problem.

Thanks in advance.
Date: 2011-12-17 17:33
Sender: Misha Sugakov

Hello, Francisco!
The DateTimeOffset works ok! Thank you very much!
About testing I made: it was “build and run and see what
happens” test. I do not know how to convert it to the unit
test, so I’m sending it as is. And sorry for delay: I had to
get rid of framework targeting in sln and csproj files which
is really painful in VS. In the end it is much easier to
edit these files in notepad.
Well, I can’t stand to report another feature. When
datetimeoffset or NpgsqlTimestampTZ value is sent to
database it gets converted to the local time and timezone is
adjusted to local. For example, 18:00+01 becomes 21:00+04
for Russian Standard Time (well, at least it should but our
timezones are twisted almost every year and we have to patch
software periodically). There’s NpgsqlTestAdoNet project for
demonstration. Anyway this feature does not break my code
and I just want to inform you. :)
Thanks for your help
Date: 2011-12-17 02:37
Sender: Francisco Figueiredo jr.

Patch applied. Please, give it a try and let me know if it is working ok.
Please, if possible, send me some of your testcases so I can add them to our testcases.
Date: 2011-12-17 01:53
Sender: Francisco Figueiredo jr.

In the post below it should read: "I didn't notice the 2.0 sp1 support though"
Date: 2011-12-16 03:29
Sender: Francisco Figueiredo jr.

That's great, Misha!

Thank you for your tests. I did notice the 2.0 sp1 support though. I only checked the "Other versions" combo box on the right and had noticed 2.0 wasn't supported.

I think there is no problem with 2.0 sp1 and up supporting this. As I think almost nobody nowadays uses "pure" 2.0 installation.

Can you contribute your tests? I started to create a mega test to test all datatypes on Npgsql and its conformance with .net so we can easily check if there is any mismatch between types.

Thank you very much again. I'll apply your patch and let you know when it is done!
Date: 2011-12-15 19:45
Sender: Misha Sugakov

Hello, Francisco!
I checked the code I suggested. It does not change default
behavior for timestamptz columns: in ADO.NET (framework
versions 2.0, 3.5, 4.0), NpgsqlDataReader returns DateTime.
Though NpgsqlCommand can now accept DateTimeOffset as parameter.
As it is said in msdn
DateTimeOffset is available in 2.0 SP1, 3.0 SP1, 3.5 SP1 and
later (at the same time this page
tells that 3.5 includes 2.0 sp1 so 3.5 should be enough) so
the changes I made should not break existing clients. The
problem that may happen is Npgsql would not build on systems
without .NET v2 & v3 SP1.
I also tested 4.0, 3.5, 2.0, and clean .net 2.0 installation
to make sure it works right. Everything is fine except the
later case: on v2.0 without SP1 there is Unhandled
Exception: System.TypeInitializationException: The type
initializer for 'NpgsqlTypes.NpgsqlTypesHelper' threw an
exception. ---> System.TypeLoadException: Could not load
type 'System.DateTimeOffset' from assembly 'mscorlib,
Version=, Culture=neutral,
I suggest to surround the code dealing with DateTimeOffset
with the conditional macros to allow this type only when
building Npgsql with 3.5 and 4.0 support. What do you think
about that?
Regards, Misha
Date: 2011-12-12 03:34
Sender: Francisco Figueiredo jr.

Hi, Misha!

As we support .net 2.0, we have to elaborate more about how we would handle .net 2.0 clients. Which type would be returned?
Date: 2011-10-31 14:16
Sender: Misha Sugakov

I've made some research and found that niether
System.DateTimeOffset nor System.Data.DbType.DateTimeOffset
are recognized by Npgsql right now.
However I need to have timestamptz columns in database.
Theese are mapped for Entity Framework to DateTimeOffset by
edmgen2 (the only tool I know enables EF for postgres).
It is strange that Npgsql does not support DateTimeOffset
having almost identical but more feature-rich
NpgsqlTimeStampTZ type.
I suggest this patch to allow DateTimeOffset in command

Index: NpgsqlTypesHelper.cs
RCS file:
retrieving revision 1.39
diff -u -r1.39 NpgsqlTypesHelper.cs
--- NpgsqlTypesHelper.cs 25 Apr 2011 02:29:44 -0000 1.39
+++ NpgsqlTypesHelper.cs 31 Oct 2011 08:10:03 -0000
@@ -378,6 +378,10 @@
NpgsqlDbType.TimestampTZ, DbType.DateTime, true,

+ nativeTypeMapping.AddDbTypeAlias("timestamptz",
+ nativeTypeMapping.AddTypeAlias("timestamptz",

NpgsqlDbType.Abstime, DbType.DateTime, true,
@@ -613,7 +617,7 @@
yield return
new NpgsqlBackendTypeInfo(0, "timestamptz",
NpgsqlDbType.TimestampTZ, DbType.DateTime, typeof
typeof(DateTime), timestamptz =>
npgsqlTimestampTZ => (npgsqlTimestampTZ is DateTime ?
(NpgsqlTimeStampTZ)(DateTime) npgsqlTimestampTZ :
typeof(DateTime), timestamptz =>
npgsqlTimestampTZ => (npgsqlTimestampTZ is DateTime ?
(NpgsqlTimeStampTZ)(DateTime) npgsqlTimestampTZ :
npgsqlTimestampTZ is DateTimeOffset ?
(NpgsqlTimeStampTZ)(DateTimeOffset) npgsqlTimestampTZ :

And also theese conversion operators to be inserted into
class NpgsqlTimestampTZ (I could not create patch because
TortoiseCVS gave \r\r\n line endings which turn diff results
into a mess):

public static implicit operator
NpgsqlTimeStampTZ(DateTimeOffset datetimeoffset)
if (datetimeoffset == DateTimeOffset.MaxValue)
return Infinity;
else if (datetimeoffset == DateTimeOffset.MinValue)
return MinusInfinity;
NpgsqlDate newDate = new NpgsqlDate(datetimeoffset.Year,
datetimeoffset.Month, datetimeoffset.Day);
new NpgsqlTimeStampTZ(newDate, new
new NpgsqlTimeZone(datetimeoffset.Offset)));
public static explicit operator
DateTimeOffset(NpgsqlTimeStampTZ timestamp)
switch (timestamp._type)
case TimeType.Infinity:
return DateTimeOffset.MaxValue;
case TimeType.MinusInfinity:
return DateTimeOffset.MinValue;
return new DateTimeOffset(timestamp.Date.DaysSinceEra
* NpgsqlInterval.TicksPerDay + timestamp.Time.Ticks,
throw new InvalidCastException();

Theese source changes allow parameters of type
DateTimeOffset in commands and entity framework. My
program depends now on this feature so I am going to do what
is necessary for this feature to be included in the next
Npgsql release. Please guide me.
I really hope my message will not go unnoticed.

Attached Files:



Field Old Value Date By
File Added710: NpgsqlDateTimeOffsetTest.zip2011-12-17 17:33alabax
ResolutionNone2011-12-16 03:29fxjr
Powered By FusionForge