Storing DateTimeOffset value without milliseconds in SQL Server using EF Core

Question

Say I create a DateTimeOffset value like this:

var dt = DateTimeOffset.UtcNow;

And I want to store this value in SQL Server using EF Core code-first. How do I remove the milliseconds portion, so that the value stored in SQL Server becomes 2020-09-08 14:51:00 +00:00 instead of 2020-09-08 14:51:00.4890984 +00:00?

Answer 1

I agree with @Zohar Peled. Define your data as it is supposed to be.

other than that, In c# you can truncate date in this way:

dt.AddTicks( - (dt.Ticks % dt.TicksPerMilliseconds));

this post has more general approach.

Answer 2

Based on @Zohar suggestion using Data Attributes on your Model binded to EF:

    [Column(TypeName = "datetimeoffset(0)")]
    public DateTimeOffset Date { get; set; }

Answer 3

A simple way is to use a value converter.

The code could look like this (using the answer from How to truncate milliseconds off of a .NET DateTime for the actual conversion logic):

public class YourContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<YourEntity>()
            .Property(e => e.YourProperty)
            .HasConversion(
                v => v.AddTicks(-(dateTime.Ticks % TimeSpan.TicksPerSecond));,
                v => v);
    }
}

Of course you can also explicitly define and then reuse the value converter and you can also apply it to all properties of a certain type like 'DateTimeOffset', by iterating over the entities and their properties via ModelBuilder.