EntityFramework Core format DateTime in sqlite database

Question

EntityFramework Core format DateTime in sqlite database

c# database datetime ef-core-2.0 entity-framework-core

Question

I'm using EF Core 2.0 with SQLite and I want to reduce the size of my database without loosing the human readable datetime value in my table column. Currently the data context stores the full DateTime CLR object as a a string like "2018-03-10 16:18:17.1013863" but in my case "2018-03-10 16:18:17" would be enought. What I have to do?

BlackMatrix

Popular Answer

If you really want to shave even more size you could add a custom conversion to each column that you want to reduce size, like this:

 protected override void OnModelCreating(ModelBuilder constructor) =>
 constructor.Entity<YourEntity>().Property(c => c.DatePropertyOfYourEntity)
                    .HasConversion(f => f.ATextoYYMMDD(), s => s.AFechaYYMMDD());

I have used the following extension methods for very fast conversion between date and the string format YYMMDD:

    public static string ATextoYYMMDD(this DateTime fechaHora) {

        var chars = new char[6];
        var valor = fechaHora.Year % 100;
        chars[0] = (char)(valor / 10 + '0');
        chars[1] = (char)(valor % 10 + '0');
        valor = fechaHora.Month;
        chars[2] = (char)(valor / 10 + '0');
        chars[3] = (char)(valor % 10 + '0');
        valor = fechaHora.Day;
        chars[4] = (char)(valor / 10 + '0');
        chars[5] = (char)(valor % 10 + '0');
        return new string(chars);

    } 

    public static DateTime AFechaYYMMDD(this string s)
        => new DateTime((s[0] - '0') * 10 + s[1] - '0' + 2000, 
        (s[2] - '0') * 10 + s[3] - '0', (s[4] - '0') * 10 + s[5] - '0');

If you want to go and apply it to multiple entities that implement an interface you can do:

            var convertidor = new ValueConverter<DateTime, string>
                (f => f.ATextoYYMMDD(), s => s.AFechaYYMMDD());    
            foreach (var tipoEntidad in constructor.Model
                .GetEntityTypes().Where(t => typeof(IYourInterface).IsAssignableFrom(t.ClrType.BaseType))) {

                constructor.Entity(tipoEntidad.Name)
                    .Property("YourPropertyName").HasConversion(convertidor);
            }

Or you can also apply it to all date properties, see EF CORE 2.1 HasConversion on all properties of type datetime

David

View more on Stack Overflow

bricelam · Accepted Answer · 3/13/2018 6:44:33 PM

The fractional seconds aren't stored if they're zero. You can safely remove them from the existing data. Update your application to not specify them. You can do this eagerly (when you set DateTime values on your entities) or lazily during SaveChanges.

public override int SaveChanges()
{
    var dateTimeProperties =
        from e in ChangeTracker.Entries()
        where e.State == EntityState.Added
                || e.State == EntityState.Modified
        from p in e.Properties
        where p.CurrentValue is DateTime
        select p;
    foreach (var property in dateTimeProperties)
    {
        // Strip millisecond
        var value = (DateTime)property.CurrentValue;
        property.CurrentValue = new DateTime(
            value.Year,
            value.Month,
            value.Day,
            value.Hour,
            value.Minute,
            value.Second);
    }

    return base.SaveChanges();
}

EntityFramework Core format DateTime in sqlite database

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related