EFcore 解决 SQLite 没有datetime 类型的问题

EFcore 解决 SQLite 没有datetime 类型的问题

SQLite不支持EFcore 的DateTime类型，请参阅
SQLite does not have proper support for DateTimeOffset via Entity Framework Core, see the limitations

解决办法

https://gist.github.com/GeorgDangl/b90370124720ed8fed9539509aafd155#file-datetimeoffsetticksubtraction-cs

DatabaseContext.cs

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            builder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());

            if (Database.ProviderName == "Microsoft.EntityFrameworkCore.Sqlite")
            {
                // SQLite does not have proper support for DateTimeOffset via Entity Framework Core, see the limitations
                // here: https://docs.microsoft.com/en-us/ef/core/providers/sqlite/limitations#query-limitations
                // To work around this, when the Sqlite database provider is used, all model properties of type DateTimeOffset
                // use the DateTimeOffsetToBinaryConverter
                // Based on: https://github.com/aspnet/EntityFrameworkCore/issues/10784#issuecomment-415769754
                // This only supports millisecond precision, but should be sufficient for most use cases.
                foreach (var entityType in builder.Model.GetEntityTypes())
                {
                    var properties = entityType.ClrType.GetProperties().Where(p => p.PropertyType == typeof(DateTime)
                                                                                   || p.PropertyType == typeof(DateTime?));
                    foreach (var property in properties)
                    {
                        builder
                            .Entity(entityType.Name)
                            .Property(property.Name)
                            .HasConversion(new DateTimeToTimeStampConverter());
                    }
                }
            }
        }

DateTimeToTimeStampConverter

    public class DateTimeToTimeStampConverter : ValueConverter<DateTime, long>
    {
        public DateTimeToTimeStampConverter(ConverterMappingHints mappingHints = null)
            : base(
                v => (long)TimeStampHelper.ConvertJavaTimeStamp(v),
                v => TimeStampHelper.JavaTimeStampToDateTime(v),
                mappingHints)
        {
        }
    }

TimeStampHelper

    public class TimeStampHelper
    {
        public static double ConvertUnixTimeStamp(DateTime time)
        {
            //create Timespan by subtracting the value provided from
            //the Unix Epoch
            TimeSpan span = (time - new DateTime(1970, 1, 1, 0, 0, 0, 0).ToLocalTime());

            //return the total seconds (which is a UNIX timestamp)
            return (double)span.TotalSeconds;
        }

        public static DateTime UnixTimeStampToDateTime(double unixTimeStamp)
        {
            // Unix timestamp is seconds past epoch
            System.DateTime dtDateTime = new DateTime(1970, 1, 1, 0, 0, 0, 0, System.DateTimeKind.Utc);
            dtDateTime = dtDateTime.AddSeconds(unixTimeStamp).ToLocalTime();
            return dtDateTime;
        }

        public static double ConvertJavaTimeStamp(DateTime time)
        {
            //create Timespan by subtracting the value provided from
            //the Unix Epoch
            TimeSpan span = (time - new DateTime(1970, 1, 1, 0, 0, 0, 0).ToLocalTime());

            //return the total Milliseconds (which is a Java timestamp)
            return (double)span.TotalMilliseconds;
        }

        public static DateTime JavaTimeStampToDateTime(double javaTimeStamp)
        {
            // Java timestamp is milliseconds past epoch
            System.DateTime dtDateTime = new DateTime(1970, 1, 1, 0, 0, 0, 0, System.DateTimeKind.Utc);
            dtDateTime = dtDateTime.AddMilliseconds(javaTimeStamp).ToLocalTime();
            return dtDateTime;
        }
    }

问题解决！

参考

https://github.com/dotnet/efcore/issues/9071
https://gist.github.com/GeorgDangl/b90370124720ed8fed9539509aafd155#file-datetimeoffsetticksubtraction-cs
https://github.com/dotnet/efcore/tree/main/src/EFCore/Storage/ValueConversion