NHibernate和DateTime映射问题 [英] Problems with NHibernate and DateTime mappings

查看：90
本文介绍了NHibernate和DateTime映射问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在查询中遇到了麻烦，在该查询中我选择了给定范围内的记录 时间跨度.

I'm having trouble with a query where I select records within a given timespan.

我要选择的列的类型为DATE.我已经映射了这个 列作为DateTime属性，查询工作正常，但是速度很慢.

The column I'm selecting from is of type DATE. I have mapped this column as a DateTime property, and the query works but is slow.

生成的查询如下:(由 NHProfiler 提供)

The generated query looks like: (provided by NHProfiler)

select kifkalende0_.KALENDER_MEDARBEJDER_ID as KALENDER1_119_0_,
   kifkalende1_.KALENDER_EMNE_ID        as KALENDER1_210_1_,
   kifkalende0_.OPDATERET_TIDSPUNKT     as OPDATERET2_119_0_,
   kifkalende0_.AENDRET                 as AENDRET119_0_,
   kifkalende0_.OPDATERET_AF            as OPDATERET4_119_0_,
   kifkalende0_.OPRETTET_AF             as OPRETTET5_119_0_,
   kifkalende0_.OPRETTET_TIDSPUNKT      as OPRETTET6_119_0_,
   kifkalende0_.SLETTET                 as SLETTET119_0_,
   kifkalende0_.KALENDER_EMNE_ID        as KALENDER8_119_0_,
   kifkalende0_.MEDARBEJDER_ID          as MEDARBEJ9_119_0_,
   kifkalende1_.OPDATERET_TIDSPUNKT     as OPDATERET2_210_1_,
   kifkalende1_.BESKRIVELSE             as BESKRIVE3_210_1_,
   kifkalende1_.DATO                    as DATO210_1_,
   kifkalende1_.ER_FRA_SAG              as ER5_210_1_,
   kifkalende1_.FRA_SAG_ID              as FRA6_210_1_,
   kifkalende1_.FRA_TABEL               as FRA7_210_1_,
   kifkalende1_.FRA_TID                 as FRA8_210_1_,
   kifkalende1_.OPDATERET_AF            as OPDATERET9_210_1_,
   kifkalende1_.OPRETTET_AF             as OPRETTET10_210_1_,
   kifkalende1_.OPRETTET_TIDSPUNKT      as OPRETTET11_210_1_,
   kifkalende1_.SAG_TYPE                as SAG12_210_1_,
   kifkalende1_.TIL_TID                 as TIL13_210_1_,
   kifkalende1_.YDERLIGERE_BESKRIVELSE  as YDERLIGERE14_210_1_,
   kifkalende1_.EMNE_ID                 as EMNE15_210_1_,
   kifkalende1_.PERSON_ID               as PERSON16_210_1_
from   "KIF_KALENDER_MEDARBEJDER" kifkalende0_
   left outer join "KIF_KALENDER_EMNE" kifkalende1_ on 
kifkalende0_.KALENDER_EMNE_ID = kifkalende1_.KALENDER_EMNE_ID,
   "KIF_KALENDER_EMNE" kifkalende2_
where  kifkalende0_.KALENDER_EMNE_ID = kifkalende2_.KALENDER_EMNE_ID
   and (kifkalende0_.MEDARBEJDER_ID in (7624 /* :p3 */,6226 
/* :p4 */,7382 /* :p5 */,5774 /* :p6 */, 5775 /* :p7 */,8259 
/* :p8 */,8218 /* :p9 */,9899 /* :p10 */, 6000 /* :p11 */,5779 
/* :p12 */,5780 /* :p13 */,5782 /* :p14 */, 5783 /* :p15 */,5784 
/* :p16 */,5785 /* :p17 */,5788 /* :p18 */, 5789 /* :p19 */,5790 
/* :p20 */,7341 /* :p21 */,8963 /* :p22 */, 10201 /* :p23 */,10388 
/* :p24 */))       
and kifkalende2_.DATO >= TIMESTAMP '2010-11-10 00:00:00.00' /* :p0 */
and kifkalende2_.DATO <= TIMESTAMP '2010-11-10 23:59:59.00' /* :p1 */
and (kifkalende0_.SLETTET = TIMESTAMP '1899-12-31 00:00:00.00' /* :p2 */
    or kifkalende0_.SLETTET is null);

在我们的数据库中，执行大约需要1500毫秒.

And in our database, it takes around a 1500 ms to execute.

如果我们将查询手动更改为:

If we change the query manually to:

select kifkalende0_.KALENDER_MEDARBEJDER_ID as KALENDER1_119_0_,
   kifkalende1_.KALENDER_EMNE_ID        as KALENDER1_210_1_,
   kifkalende0_.OPDATERET_TIDSPUNKT     as OPDATERET2_119_0_,
   kifkalende0_.AENDRET                 as AENDRET119_0_,
   kifkalende0_.OPDATERET_AF            as OPDATERET4_119_0_,
   kifkalende0_.OPRETTET_AF             as OPRETTET5_119_0_,
   kifkalende0_.OPRETTET_TIDSPUNKT      as OPRETTET6_119_0_,
   kifkalende0_.SLETTET                 as SLETTET119_0_,
   kifkalende0_.KALENDER_EMNE_ID        as KALENDER8_119_0_,
   kifkalende0_.MEDARBEJDER_ID          as MEDARBEJ9_119_0_,
   kifkalende1_.OPDATERET_TIDSPUNKT     as OPDATERET2_210_1_,
   kifkalende1_.BESKRIVELSE             as BESKRIVE3_210_1_,
   kifkalende1_.DATO                    as DATO210_1_,
   kifkalende1_.ER_FRA_SAG              as ER5_210_1_,
   kifkalende1_.FRA_SAG_ID              as FRA6_210_1_,
   kifkalende1_.FRA_TABEL               as FRA7_210_1_,
   kifkalende1_.FRA_TID                 as FRA8_210_1_,
   kifkalende1_.OPDATERET_AF            as OPDATERET9_210_1_,
   kifkalende1_.OPRETTET_AF             as OPRETTET10_210_1_,
   kifkalende1_.OPRETTET_TIDSPUNKT      as OPRETTET11_210_1_,
   kifkalende1_.SAG_TYPE                as SAG12_210_1_,
   kifkalende1_.TIL_TID                 as TIL13_210_1_,
   kifkalende1_.YDERLIGERE_BESKRIVELSE  as YDERLIGERE14_210_1_,
   kifkalende1_.EMNE_ID                 as EMNE15_210_1_,
   kifkalende1_.PERSON_ID               as PERSON16_210_1_
from   "KIF_KALENDER_MEDARBEJDER" kifkalende0_
   left outer join "KIF_KALENDER_EMNE" kifkalende1_ on 
kifkalende0_.KALENDER_EMNE_ID = kifkalende1_.KALENDER_EMNE_ID,
   "KIF_KALENDER_EMNE" kifkalende2_
where  kifkalende0_.KALENDER_EMNE_ID = kifkalende2_.KALENDER_EMNE_ID
   and (kifkalende0_.MEDARBEJDER_ID in (7624 /* :p3 */,6226 
/* :p4 */,7382 /* :p5 */,5774 /* :p6 */, 5775 /* :p7 */,8259 
/* :p8 */,8218 /* :p9 */,9899 /* :p10 */, 6000 /* :p11 */,5779 
/* :p12 */,5780 /* :p13 */,5782 /* :p14 */, 5783 /* :p15 */,5784 
/* :p16 */,5785 /* :p17 */,5788 /* :p18 */, 5789 /* :p19 */,5790 
/* :p20 */,7341 /* :p21 */,8963 /* :p22 */, 10201 /* :p23 */,10388 
/* :p24 */))  
   and kifkalende2_.DATO>=to_date('10-11-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
   and kifkalende2_.DATO<=to_date('10-11-2010 23:59:59', 'DD-MM-YYYY HH24:MI:SS')
   and (kifkalende0_.SLETTET=to_date('31-12-1899 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
   or kifkalende0_.SLETTET is null);

它在大约50毫秒内执行.

it executes in about 50ms.

有什么方法可以让NHibernate生成to_date而不是 日期比较的时间戳??

Is there any way to make NHibernate generate to_date instead of timestamp for date comparisons??

我对RegisterDateTimeTypeMappings的工作方式有些困惑 Oracle10gDialect，但是我尝试扩展它，将方法更改为

I am bit confused as to how the RegisterDateTimeTypeMappings works in the Oracle10gDialect, but I tried extending it, changing the method to

            protected override void RegisterDateTimeTypeMappings()
            {
                    RegisterColumnType(DbType.Date, "DATE");
                    //RegisterColumnType(DbType.DateTime, "TIMESTAMP(4)");
                    RegisterColumnType(DbType.DateTime, "DATE");
                    RegisterColumnType(DbType.Time, "TIMESTAMP(4)");
            }

但这没有帮助.

我们的环境是:

  • .net(C#)4.0
  • NHibernate 3.1.0，主要通过Linq使用
    • 针对Oracle 11g的
  • ODP.Net 11.2.2.0
  • .net (C#) 4.0
  • NHibernate 3.1.0, used primarily via Linq
  • ODP.Net 11.2.2.0 against an Oracle 11g

有人有什么建议吗?

谢谢， ./Daniel

Thanks, ./Daniel

推荐答案

我通过重写以下NHibernate类解决了我和你的问题. NHProfiler仍将显示TIMESTAMP值，但基础参数将为DATE类型-如果您的oracle列也是DateTime，则该索引将是可索引的. 将鼠标悬停在NHProfiler中的参数上，以了解我的意思-类似于: :p0:TIMESTAMP-'2011-07-01 00:00:00.00'日期 代替 :p0:TIMESTAMP-'2011-07-01 00:00:00.00'时间戳.

I solved my problem and yours by overriding the following NHibernate Class. The NHProfiler will still display a TIMESTAMP value but the underlying parameter will be of type DATE - which will be indexable if your oracle column is also a DateTime. Hover your mouse over the parameters in NHProfiler to see what I mean - something like: :p0: TIMESTAMP - '2011-07-01 00:00:00.00' Date instead of :p0: TIMESTAMP - '2011-07-01 00:00:00.00' Timestamp.

我也在使用Oracle 11g，C#.Net 4，Nhibernate 3.2.

I'm also using Oracle 11g, C# .Net 4, Nhibernate 3.2.

public class OracleDataClientDriver2 : OracleDataClientDriver
{
    protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType)
    {
        switch (sqlType.DbType)
        {
                //Timestamp columns not indexed by Oracle 11g date columns. - Use Date 
            case DbType.DateTime:
                base.InitializeParameter(dbParam, name, SqlTypeFactory.Date);
                break;
            default:
                base.InitializeParameter(dbParam, name, sqlType);
                break;
        }
    }
}

这篇关于NHibernate和DateTime映射问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆