DateTime values lose accuracy when stored in the DB

[gzak]

If you store a DateTime object in the DB, then read that record back from the DB, you'll get a DateTime object with a slightly different value than what you originally stored. This means that a test to compare equality of the before/after value of a round trip to the DB will fail.

Here's an example:

var entity = db.Entity.Single(e => e.Id == 123);
var now = DateTime.Now;
entity.Date = now;
db.SaveChanges();

entity = db.Entity.Single(e => e.Id == 123);
Assert.Equal(now, entity.Date); // fails the assert

If you inspect now.Ticks and entity.Date.Ticks, you'll see that the latter ends with about four 0's whereas the former has non-zero digits all the way to the 1's place.

The target column in this example is of type datetime2(7) in an MS SQL Server DB, which presumably has the same accuracy as .NET's DateTime struct (down to ticks, where a tick = 100 nanoseconds). So it should fit in the column without truncation, yet for some reason it's still getting truncated.

[rowanmiller]

@smitpatel can you verify that with our default mapping no precision is lost

[smitpatel]

This works fine in latest packages.
Repro code:

    public class Program
    {
        public static void Main(string[] args)
        {
            using (var context = new MyContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();
                context.Add(new Entity());
                context.SaveChanges();
            }

            using (var context = new MyContext())
            {
                var entity = context.Entities.Single(e => e.Id == 1);
                var now = DateTime.Now;
                entity.Date = now;
                context.SaveChanges();

                entity = context.Entities.Single(e => e.Id == 1);
                Assert.Equal(now, entity.Date);
                Console.WriteLine(now.ToString("O"));
                Console.WriteLine(entity.Date.ToString("O"));
            }
        }
    }

    public class Entity
    {
        public int Id { get; set; }
        public DateTime Date { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<Entity> Entities { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var connString = new SqlConnectionStringBuilder
            {
                DataSource = "(localdb)\\MSSQLLocalDB",
                InitialCatalog = "issue5175",
                IntegratedSecurity = true
            }.ConnectionString;

            optionsBuilder.UseSqlServer(connString);
        }
    }

Output:
Assert passes. &

2016-04-26T12:51:38.1080322-07:00
2016-04-26T12:51:38.1080322-07:00

[gzak]

This is my bad, we aren't using EF's insert functionality due to #1441 (we're waiting for that to be released), and the issue was with DbParameter.DbType being detected as datetime rather than datetime2 for System.DateTime objects in the code. This and our other issue with timezones captured in #4711 are both solved by switching all dates to the DateTimeOffset type in C# and the datetimeoffset type in the DB.