0

I'm on EF6 and have a hard time getting DateTime columns to work. My specific problem is that I'm writing UTC datetimes to my database, but am getting datetime objects in my local time when querying the database. I've tried multiple things to fix this, but none work.

My model class

[Table("Foos")]
public class Foo
{
    public Foo()
    {
        Elements = new List<Element>();
        Timestamp = DateTime.UtcNow;

    }

    [Key]
    public DateTime Timestamp
    {
        get;
        set;
    }

    public ICollection<DbElement> Elements { get; set; }
}

The issue

var initial = FooMethods.Create(); 
var sameButReadFromDB = FooMethods.ReadMostRecent(); 
var initial_datetime = initial.Timestamp; // <- UTC datetime, think 16:04 for hh:mm
var retrieved_datetime = sameButReadFromDB.Timestamp // <- same datetime, but for my timezone. think 19:04 for hh:mm

Things I've tried

Approach I - Using custom setters and getters

    [Key]
    public DateTime Timestamp
    {
        get;
        set;
        // get => _timestamp.ToUniversalTime(); 
        // set => _timestamp = value.ToUniversalTime(); 
    }
    
    [NotMapped]
    private DateTime _timestamp;

This gave me an The value of a property that is part of an object's key does not match the corresponding property value stored in the ObjectContext. error.

Approach II - Using attributes

[Key]
[DataType(DataType.DateTime)]
[DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:yyyy-MM-ddTHH:mm:ssZ}")]
public DateTime Timestamp { get; set; }

This returns the datetime in my local time.

Approach III Solutions from StackOverflow

All these successfully change the DateTimeKind of my queried element to DateTimeKind.UTC, but the time itself doesn't adapt to UTC time (still my local time).

I'm using a SQLite DB if that helps.

emilaz
  • 1,722
  • 1
  • 15
  • 31
  • IIRC, DateTime does not include any offset information. Hence you'll always need to provide the offset (or take your system's setting as offset). If you stick with DateTime you need to normalize on "a" offset when persisting, thereby being able to create consistent output for datetime. Another option would be to use DateTimeOffset instead, which does include Offset information. (This is a known issue in the OData+EF arena and is solved by having a dummy DateTime property which feeds of the persisted DateTimeOffset value). – Marvin Smit May 09 '23 at 16:25
  • If you can provide me with an example of how either approach would work I'll gladly accept it as answer – emilaz May 10 '23 at 07:03

0 Answers0