NHibernate中的DateTime精度，并支持NHibernate SchemeExport中的DateTime2 [英] DateTime precision in NHibernate and support for DateTime2 in NHibernate SchemeExport

问题描述

然后我使用Fluent NHibernate及其自动化功能来映射以下简化的POCO类：

  public class Foo 
 {
 public virtual int Id {get;组; } 
 public virtual datetime CreatedDateTime {get;组; } 
} 
  

默认情况下，CreatedDateTime字段将映射到SQL DateTime。但是，如果我进行测试来检查实体是否正确创建，那么它将失败。这是因为DateTime字段的精度不会保持到SQL数据库。我不太明白这个原因是MS SQL Server DateTime只能通过四舍五入到0.000，.003或.007的增量来保持毫秒的精度（参见 http://msdn.microsoft.com/en-us/library/ms187819.aspx ）。因此，当保存到商店时，NHibernate会截断毫秒数。这导致我的测试失败，当我检测到正确保存的字段，因为我的.NET DateTime持有其毫秒，但是在保存已经失去了毫秒之后，DateTime被删除，因此两个不相等。

为了克服这个问题，我将以下映射添加到Foo对象中：

  public class FooMap： IAutoMappingOverride< Foo> 
 {
 public void Override（AutoMapping&Foo>映射）
 {
 mapping.Map（f => f.CreatedDateTime）.CustomType（datetime2）; 
} 
} 
  

我知道这个映射使NHibernate将CreatedDateTime保持为一种SQL类型的datetime2，可以存储.NET DateTime可以完整的精度。
这个工作是一个对待，测试现在通过了。

但是一次通过又是一个失败：我的检查模式导出的测试现在失败，出现以下错误：

  System.ArgumentException：方言不支持DbType.DateTime2 
参数名称：typecode 
  

与堆栈跟踪：

 在NHibernate.Dialect.TypeNames.Get（DbType typecode）
在NHibernate.Dialect.Dialect.GetTypeName（SqlType sqlType）
在NHibernate.Mapping.Column.GetDialectTypeName（方言方言，映射映射） 
在NHibernate.Mapping.Table.SqlCreateString（方言方言，IMapping p，字符串defaultCatalog，字符串defaultSchema）
在NHibernate.Cfg.Configuration.GenerateSchemaCreationScript（方言方言）
在NHibernate.Tool。 hbm2ddl.SchemaExport..ctor（配置cfg，IDictionary`2 configProperties）
在NHibernate.Tool.hbm2ddl.SchemaExport..ctor（配置cfg）
  

代码使用NHibernate.Tool.hbm2ddl.SchemaExport对象调用Execute方法。

我正在使用Fluent v1和NHibernate v2.1 。

我还尝试将我的 DateTime 映射到TimeStamp，但是甚至无法将映射工作为插入失败说明：

无法在时间戳记列中插入显式值。使用列表列表中的 INSERT 排除时间戳列，或在时间戳列中插入 DEFAULT 。

有没有人知道如何使用 datetime2 获取SchemeExport或如何获取时间戳映射工作在 datetime property？

解决方案

实际上，NHibernate引用指出DateTime nhibernate类型将存储.NET DateTime作为SQL datetime在第二级被截断（毫秒毫秒）

因此，它提供 Timestamp NHibernate类型（ type =Timestamp在映射中），它将存储.NET DateTime 作为SQL datetime 不截断。请注意，SQL 时间戳数据类型不需要，如果您有多个时间戳列在一个表中。因此，在NHibernate映射中区分 sql-type 和类型属性非常重要。

$ b另外，请注意，如果您使用过滤器，则相同的规则适用于过滤器定义：如果指定了一个 DateTime 参数，那么参数的值将被截断而不会毫秒。

查看第5.2.2节。基本价值类型，表5.3 System.ValueType映射类型 。

I am then using Fluent NHibernate and its automapping feature to map the the following simplified POCO class:

public class Foo
{    
public virtual int Id { get; set; }    
public virtual datetime CreatedDateTime { get; set; }    
}

The CreatedDateTime field will map to a SQL DateTime by default. However if I do a test to check that the entity is being created correctly it fails. This is because the precision of the DateTime field is not maintained through to the SQL database. I undersatnd the reason behind this to be that a MS SQL Server DateTime can only hold milisecond precision by rounded to increments of .000, .003, or .007 (see http://msdn.microsoft.com/en-us/library/ms187819.aspx). For this reason NHibernate truncates the miliseconds when saving to the store. This results in my test failing when checking that the fields where persisted correctly as my .NET DateTime holds its miliseconds but the DateTime retrived after the save has lost its miliseconds and therefore the two are not truely equal.

To overcome this problem I have added the following mapping to the Foo object:

public class FooMap : IAutoMappingOverride<Foo>
{
    public void Override(AutoMapping<Foo> mapping)
    {
        mapping.Map(f => f.CreatedDateTime).CustomType("datetime2");     
    }
}

I understand that this mapping makes NHibernate persist the CreatedDateTime to a SQL type of datetime2, which can store the full precision that a .NET DateTime can. This works a treat and the test now passes.

However with one pass comes another fail: My test that checks the schema export now fails with the following error:

System.ArgumentException : Dialect does not support DbType.DateTime2
Parameter name: typecode

with a stack trace of:

at NHibernate.Dialect.TypeNames.Get(DbType typecode)
at NHibernate.Dialect.Dialect.GetTypeName(SqlType sqlType)
at NHibernate.Mapping.Column.GetDialectTypeName(Dialect dialect, IMapping mapping)
at NHibernate.Mapping.Table.SqlCreateString(Dialect dialect, IMapping p, String defaultCatalog, String defaultSchema)
at NHibernate.Cfg.Configuration.GenerateSchemaCreationScript(Dialect dialect)
at NHibernate.Tool.hbm2ddl.SchemaExport..ctor(Configuration cfg, IDictionary`2 configProperties)
at NHibernate.Tool.hbm2ddl.SchemaExport..ctor(Configuration cfg)

The code uses the NHibernate.Tool.hbm2ddl.SchemaExport object to call the Execute method.

I am using Fluent v1 and NHibernate v2.1.

I have also tried mapping my DateTime to a TimeStamp but couldn't even get the mapping working as the insert fails stating:

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

Does anyone know either how to get the SchemeExport working with a datetime2 OR how to get timestamp mapping working for a datetime property?

解决方案

Actually the NHibernate reference states that the DateTime nhibernate type will store the .NET DateTime as an SQL datetime truncated at the second level (no millisecond granularity)

As such it provides the Timestamp NHibernate type (type="Timestamp" in the mapping) which will store a .NET DateTime as an SQL datetime without truncation. Note here that an SQL timestamp datatype is not needed and will infact break if you have more than one timestamp column in one table. It's thus important to differentiate between the sql-type and type attributes in the NHibernate mapping.

Additionally, note that if you are working with filters, the same rule applies at the filter definition: If you specify a DateTime parameter, the parameter's value will be truncated without milliseconds.

Check out chapter 5.2.2. Basic value types, Table 5.3 System.ValueType Mapping Types.

这篇关于NHibernate中的DateTime精度，并支持NHibernate SchemeExport中的DateTime2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！