title: Problems with NHibernate and DateTime mappings
body: <p>I'm having trouble with a query where I select records within a given
  timespan.</p>
  
  <p>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.</p>
  
  <p>The generated query looks like: (provided by <a href="http://nhprof.com/" rel="nofollow">NHProfiler</a>)</p>
  
  <pre><code>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 &gt;= TIMESTAMP '2010-11-10 00:00:00.00' /* :p0 */
  and kifkalende2_.DATO &lt;= 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);
  </code></pre>
  
  <p>And in our database, it takes around a 1500 ms to execute.</p>
  
  <p>If we change the query manually to:</p>
  
  <pre><code>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&gt;=to_date('10-11-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
     and kifkalende2_.DATO&lt;=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);
  </code></pre>
  
  <p>it executes in about 50ms.</p>
  
  <p>Is there any way to make NHibernate generate to_date instead of
  timestamp for date comparisons??</p>
  
  <p>I am bit confused as to how the RegisterDateTimeTypeMappings works in
  the Oracle10gDialect, but I tried extending it, changing the method to</p>
  
  <pre><code>            protected override void RegisterDateTimeTypeMappings()
              {
                      RegisterColumnType(DbType.Date, "DATE");
                      //RegisterColumnType(DbType.DateTime, "TIMESTAMP(4)");
                      RegisterColumnType(DbType.DateTime, "DATE");
                      RegisterColumnType(DbType.Time, "TIMESTAMP(4)");
              }
  </code></pre>
  
  <p>but that did not help.</p>
  
  <p>Our environment is:</p>
  
  <ul>
  <li>.net (C#) 4.0</li>
  <li>NHibernate 3.1.0, used primarily via Linq</li>
  <li>ODP.Net 11.2.2.0 against an Oracle 11g</li>
  </ul>
  
  <p>Do anyone have any suggestions? </p>
  
  <p>Thanks,
  ./Daniel</p>