SQLite与从数据库检索到的C#DateTime不匹配 [英] SQLite won't match C# DateTime retrieved from database

问题描述

我试图在内存中使用SQLite在我的应用程序上运行一些单元测试，但遇到一个奇怪的问题:

I'm attempting to run some unit tests on my application using SQLite in memory, but I've run into an odd problem:

我有两个查询.第一个结果是给定列表名称的最新价格列表的日期，第二个查询中使用DateTime来获取最新价格.问题是，第二个查询没有返回结果.

I have two queries. The result of the first is the date of the most recent price list for a given list name, and that DateTime is used in the second query in order to fetch the most recent prices. The problem is, the second query returns no results.

您知道这里的背景可能出什么问题吗?

Any idea what might be going wrong in the background here?

        var effective = DbSession.Current.CreateCriteria<ItemPrice>()
                .SetProjection(Projections.Max("Effective"))
                .Add(Restrictions.Le("Effective", workDate))
                .CreateCriteria("PriceList")
                .Add(Restrictions.Eq("ListName", listName))
                .Add(Restrictions.Eq("Active", true))
                .UniqueResult<DateTime>();

        return DbSession.Current.CreateCriteria<ItemPrice>()
            .Add(Restrictions.Eq("Effective", effective))
            .CreateCriteria("PriceList")
            .Add(Restrictions.Eq("ListName", listName))
            .Add(Restrictions.Eq("Active", true))
            .List<ItemPrice>();

结果:最终实现了一个自定义IUserType以将DateTime存储为字符串，并添加了Fluent Automapping约定供DateTime使用(以下包括):

Result: Ended up implementing a custom IUserType to store the DateTime as a string, and adding a Fluent Automapping convention for DateTime to use it (included below):

class SQLiteDateTime : IUserType
    {
        #region IUserType Members

        public object Assemble(object cached, object owner)
        {
            return cached;
        }

        public object DeepCopy(object value)
        {
            var dt = (DateTime) value;
            return new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second);
        }

        public object Disassemble(object value)
        {
            return String.Format("{0:yyyy'-'MM'-'dd' 'HH':'mm':'ss.fff}", value);
        }

        public new bool Equals(object x, object y)
        {
            return x.Equals(y);
        }

        public int GetHashCode(object x)
        {
            return x.GetHashCode();
        }

        public bool IsMutable
        {
            get { return false; }
        }

        public object NullSafeGet(IDataReader rs, string[] names, object owner)
        { 
            string dateString = (string)NHibernateUtil.String.NullSafeGet(rs, names[0]);
            DateTime result = DateTime.ParseExact(dateString, "yyyy'-'MM'-'dd' 'HH':'mm':'ss.fff", CultureInfo.InvariantCulture.DateTimeFormat);

            return result;
        }

        public void NullSafeSet(IDbCommand cmd, object value, int index)
        {  
            if (value == null)
            {
                NHibernateUtil.String.NullSafeSet(cmd, null, index);
                return;
            }
            value = Disassemble(value);
            NHibernateUtil.String.NullSafeSet(cmd, value, index);
        }  

        public object Replace(object original, object target, object owner)
        {
            return original;
        }

        public Type ReturnedType
        {
            get { return typeof (DateTime); }
        }

        public NHibernate.SqlTypes.SqlType[] SqlTypes
        {
            get {   
            var types = new SqlType[1];  
            types[0] = new SqlType(DbType.String);  
            return types;  
            } 
        }

        #endregion
    }

推荐答案

尝试为该列设置Timestamp的CustomType.我认为节省毫秒与不节省毫秒之间的区别.

Try setting a CustomType of Timestamp for that column. It's the difference between saving the milliseconds or not, I think.

这篇关于SQLite与从数据库检索到的C#DateTime不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！