SCM

[#1011102] DateTimeOffset/TimeSpan mapping problem on EF 4.1, .NET 4, using code first

View Trackers | Bugs | Download .csv | Monitor

Date:
2011-10-06 10:36
Priority:
3
State:
Open
Submitted by:
Damian W (metrix)
Assigned to:
Nobody (None)
Npgsql Version:
2.0.11
Category:
Group:
Resolution:
None
Summary:
DateTimeOffset/TimeSpan mapping problem on EF 4.1, .NET 4, using code first

Detailed description
Following example using 2.0.10:
(Need to use 2.0.10 because 2.0.11.92 even ignores the Schema-Attribute!!!)

Table: User
Column:
- ID integer not null primary key
- Created timestamptz not null

Class: User
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Configuration;

[Table("User")]
public class User
{
[Column("id", TypeName = "int4")]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Key]
public int ID { get; private set; }

[Column("created", TypeName = "timestamptz")]
public DateTimeOffset Created { get; private set; }
}

Throws:
PrimitiveType is not a Datetime-Type in class NpgsqlProviderManifest:
Line 232 (which is the else return here)
case PrimitiveTypeKind.DateTimeOffset:
if (edmType.Facets.TryGetValue(PrecisionFacet, false, out facet) &&
!facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["timestamptz"], (byte)facet.Value);
}
else
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["timestamptz"], null);
}

using a DateTime as Type for "Created"-Property it throws: PrimitiveType is not a DateTime-Type in class NpgsqlProviderManifest (Line 242, which is the else return here):
case PrimitiveTypeKind.Time:
if (edmType.Facets.TryGetValue(PrecisionFacet, false, out facet) &&
!facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["interval"], (byte)facet.Value);
}
else
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["interval"], null);
}

this only works when using TypeName ="timestamp" and Property-Type DateTime in the Model, despite the fact that the User-table uses timestamptz!

Adding a column to "User" like
- alter table "User" add column billtime interval;

and extending the Model using
[Column("billtime", TypeName = "interval")]
public Nullable<global::System.TimeSpan> BillTime { get; set; }

makes it unusable. The same error as above on the same lines. Npgsql/EF seem not being able to transform an Interval into a Timespan (which works when using csdl/msl/ssdl-Mapping files, so that problem is dedicated to the Code-First approach i think.

Need any additional info?

Followup

Message
Date: 2013-03-06 23:30
Sender: Damian W

Please replace this:

case "time":
case "interval":
if (storeType.Facets.TryGetValue(PrecisionFacet, false, out facet) &&
!facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateDateTimeTypeUsage(primitiveType, (byte)facet.Value);
}
else
{
return TypeUsage.CreateDateTimeTypeUsage(primitiveType, null);
}


with this:

case "time":
case "interval":
if (storeType.Facets.TryGetValue(PrecisionFacet, false, out facet) &&
!facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateTimeTypeUsage(primitiveType, (byte)facet.Value);
}
else
{
return TypeUsage.CreateTimeTypeUsage(primitiveType, null);
}


TIME and INTERVAL are NOT DateTime representations but TimeSpan!
Date: 2012-03-16 01:56
Sender: Damian W

Sorry for constantly updating, but the solution lacked...
Storing values like 100 hours with the solution posted before ended in
INTERVAL '4.04:00:00' which is 96 hours and 57.6 Seconds due to {T} formatting.

Now I have a dedicated solution for TimeSpan and the standard solution (TIME) for anything else.

Code works, but it not prettiest

case PrimitiveTypeKind.Time:
if (_value is TimeSpan)
{
TimeSpan _valueTmp = (TimeSpan)_value;
sqlText.Append("INTERVAL ");
sqlText.Append("'");
sqlText.Append(Math.Round(_valueTmp.TotalHours).ToString());
sqlText.Append(":");
sqlText.Append(_valueTmp.Minutes);
sqlText.Append(":");
sqlText.Append(_valueTmp.Seconds);
sqlText.Append(".");
sqlText.Append(_valueTmp.Milliseconds);
sqlText.Append("'");
}
else
{
sqlText.AppendFormat(ni, "TIME '{0:T}'", _value);
}
break;


When the _value is TimeSpan, I retrieve the TotalHours and cut off everything that is not a full hour. The cutted parts are retrieved by their fields (Minutes, Seconds,
Milliseconds). Then the formatted string for 100 hours looks like INTERVAL '100:0:0.0' which works great.
Date: 2012-03-16 01:25
Sender: Damian W

That's not all. For storing there as to be an additional change in VisitedExpression.cs:

OLD:
case PrimitiveTypeKind.Time:
sqlText.AppendFormat(ni, "TIME '{0:T}'", _value);


NEW:
case PrimitiveTypeKind.Time:
sqlText.AppendFormat(ni, "'{0:T}'::INTERVAL", _value);


This is also possible:
sqlText.AppendFormat(ni, "INTERVAL '{0:T}'", _value);

This also works for columns that are type 'time' because postgresql can convert INTERVAL to TIME automatically. So this should not be a big deal and enabled the use of TimeStamp for
storing and retrieving
Date: 2012-03-16 00:33
Sender: Damian W

Hi again,

in 2.0.11.93 DateTime and DateTimeOffset for timestamp and timestamptz seems to work, good job.

For interval, just use this:
<code>
case PrimitiveTypeKind.Time:
if (edmType.Facets.TryGetValue(PrecisionFacet, false, out facet) &&
!facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["interval"], (byte)facet.Value);
}
else
{
return TypeUsage.CreateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["interval"], null);
}
</code>

INSTEAD OF THIS

<code>
case PrimitiveTypeKind.Time:
if (edmType.Facets.TryGetValue(PrecisionFacet, false, out facet)
&& !facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStore
PrimitiveType["interval"], (byte)facet.Value);
}
else
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStore
PrimitiveType["interval"], null);
}
</code>

Then it can convert interval to TimeSpan.
Date: 2012-03-15 23:52
Sender: Damian W

In the current version of NpgSQL the following code is embedded to retrieve timestamptz as DateTimeOffset:
case PrimitiveTypeKind.DateTimeOffset:
if (edmType.Facets.TryGetValue(PrecisionFacet, false, out facet) && !facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateDateTimeOffsetTypeUsage(StoreTypeNameToStorePrimitiveType["timestamptz"], (byte)facet.Value);
}
else
{
return TypeUsage.CreateDateTimeOffsetTypeUsage(StoreTypeNameToStorePrimitiveType["timestamptz"], null);
}

unfortunately it does not work. When compiling and starting my application (asp.net) I receive the following error:
Der PrimitiveType ist kein DateTime-Typ.
Beschreibung: Unbehandelte Ausnahme beim Ausführen der aktuellen Webanforderung. Überprüfen Sie die Stapelüberwachung, um weitere Informationen über diesen Fehler anzuzeigen und festzustellen, wo der Fehler im
Code verursacht wurde.
Ausnahmedetails: System.ArgumentException: Der PrimitiveType ist kein DateTime-Typ.

--> PrimitiveType is not a DateTime-Type

Code not working is:
[Column("created", TypeName = "timestamptz")]
public DateTimeOffset Created { get; set; }

THis also does not work (ok, quite obvious)
[Column("created", TypeName = "timestamp")]
public DateTimeOffset Created { get; set; }

this does work:
[Column("created", TypeName = "timestamp")]
public DateTime Created { get; set; }


Moreover I do have problems with the following:
[Column("hour_limit", TypeName = "interval")]
public TimeSpan HourLimitImpl { get; protected set; }

OR

[Column("hour_limit", TypeName = "interval")]
public DateTime HourLimitImpl { get; protected set; }

The mapping code should be:
case PrimitiveTypeKind.Time:
if (edmType.Facets.TryGetValue(PrecisionFacet, false, out facet) && !facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["interval"], (byte)facet.Value);
}
else
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["interval"], null);
}

Why are you using DateTime for Interval? (Well it does not work...so, it should be not important here, but I think using TimeSpan would be a better representation of interval)

The DateTime leads to
Das angegebene Schema ist ungültig. Fehler:
(30,12) : Fehler 2019: Die angegebene Elementzuordnung ist ungültig. Der Typ 'Edm.DateTime[Nullable=False,DefaultValue=,Precision=]' von Element 'HourLimitImpl' in Typ 'Gateway' ist mit
'Npgsql.interval[Nullable=False,DefaultValue=,Precision=6]' von Element 'hour_limit' in Typ 'CodeFirstDatabaseSchema.Gateway' nicht kompatibel.

The TimeSpan leads to:
Der PrimitiveType ist kein DateTime-Typ.


is there a chance that you fix it with priority please? I think interval, timestamp and timestamptz are crucial field-types.

Thanks in advance.
Date: 2012-02-02 05:18
Sender: Mirko Geffken

I ran into this problem as well and a simple (and I think correct fix for the project team) would be to replace the code block in NpgsqlProviderManifest.GetStoreType(..) that looks as follows:
<CODE>
case PrimitiveTypeKind.DateTimeOffset:
if (edmType.Facets.TryGetValue(PrecisionFacet, false, out facet) &&
!facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["timestamptz"], (byte)facet.Value);
}
else
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["timestamptz"], null);
}
</CODE>
with
<CODE>
case PrimitiveTypeKind.DateTimeOffset:
if (edmType.Facets.TryGetValue(PrecisionFacet, false, out facet) &&
!facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateDateTimeOffsetTypeUsage(StoreTypeNameToStorePrimitiveType["timestamptz"], (byte)facet.Value);
}
else
{
return TypeUsage.CreateDateTimeOffsetTypeUsage(StoreTypeNameToStorePrimitiveType["timestamptz"], null);
}
</CODE>

I have no access to the source tree, so if someone could verify that this is correct and commit this that would be awesome!

Attached Files:

Changes:

No Changes Have Been Made to This Item

Powered By FusionForge