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?
Accepted Answer
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();
}
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
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow