npgsql
DateTimeOffset LTE vs RawSql strange effects
From @tryhp3 on March 18, 2015 10:42
I think that TimeStampTZ <-> DateTimeOffset does not always work.
I have simple enttity:
public class Customer
{
[Key]
[Required]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
public DateTimeOffset? DateCreated { get; set; }
//other fields
}
When I call Linq To Entities (Find method) first, and next SQL this work ok (I think that there is a cache):
When I call SQL first i have error:
Additional information: The 'DateCreated' property on 'Customer' could not be set to a 'System.DateTime' value. You must set this property to a non-null value of type 'System.DateTimeOffset'.
Copied from original issue: npgsql/npgsql#542
From @Emill on March 22, 2015 14:47
What is IRepository? It would be helpful if you could reduce the code so it only uses Entity Framework and Npgsql directly.
From @tryhp3 on March 23, 2015 9:55
No problem:
//CREATE TABLE "Contractors"
//(
// "Id" integer NOT NULL,
// "DateCreated" timestamp with time zone,
// CONSTRAINT "Contractors_pkey" PRIMARY KEY ("Id")
//)
class Program
{
static void Main(string[] args)
{
using(var context = new DTOContext())
{
Contractor noWork = context.Contractors
.SqlQuery("select * from \"Contractors\" where \"Id\" = 1")
.Single();
Contractor work = context.Contractors.Find(1);
}
}
}
[Table("Contractors", Schema = "public")]
public class Contractor
{
[Key]
public int Id { get; set; }
public DateTimeOffset? DateCreated { get; set; }
}
public class DTOContext : DbContext
{
public DbSet<Contractor> Contractors { get; set; }
}
From @Emill on September 2, 2015 21:28
I'm afraid not. This is a hard one. in Npgsql a timestamptz is normally mapped to a DateTime, but a DateTimeOffset in EF. In normal EF query generation, we have control over data types but not when you do it that way...
@Emill, interestingly I'm working on the same area in EF7 at the moment. They make it possible to select a reading strategy that uses NpgsqlDataReader.GetFieldValue<T>() rather than doing NpgsqlDataReader.GetValue() and casting - which seems like the ideal solution to this problem (since Npgsql's Int16Handler supports returning bytes).
@roji Wouldn't it generally be more correct to use a DateTimeOffset for timestamptz columns because otherwise you're throwing data away? This is already being done in EF as you mentioned. What motivation is there to use DateTime instead?
Correction: Apparently @Emill originally mentioned that but I assume any project maintainer should also know the motivation.
@meoblast001 note that PostgreSQL timestamptz does not contain a time zone or an offset - only a timestamp. timestamptz only makes PostgreSQL perform timezone conversions when reading/writing values based on your connection session's timezone setting.
PS Nice to see you again @Emill :)
Thanks @roji. I wasn't aware of that. So it seems then that no persisted data is discarded. Defaulting to DateTimeOffset would probably only be interesting when reading the data then. I think I understand the motivation to use DateTime instead then.
If anyone still wondering on how to fix this, just add a annotation to your mapped entity:
[Column(TypeName = "timestamp with time zone")]
public DateTimeOffset MyUtcProperty { get; set; }
That works on ef6 but careful: you can only manage Offset-0 DateTime's with this configuration since postgres does not save the utc data on the column, just the defined datetime as timestamp, as mentioned here: Npgsql.org - Date and Time Handling - "Npgsql also supports reading and writing DateTimeOffset to timestamp with time zone, but only with Offset=0."
