NHibernate IQuery.SetParameter lost milliseconds information when request my data base

[Christophe lavoye]

I have a problem comparing DateTime and Timestamp in between my C# .NET project and my database PostgreSQL (mapping with NHbernate).

Basically, i have a table with a column MY_COLUMN typed as "timestamp without timezone"

In this one i can store value with milliseconds precision.

I have a stored function count_request(id, timestamp without time zone) which compare the parameter timestamp without time zone with MY_COLUMN

I'm using EntityDevelopper to generate the NHibernate Mapping betwwen my .NET project and my data base

MY_COLUMN is declared as :

<property name="RequestIn" type="Timestamp" p1:nullable="true" p1:ValidateRequired="false" p1:Guid="xxx">
  <column name="request_in" not-null="false" sql-type="timestamp" p1:unicode="false" />
</property>

the query is declared as..

<sql-query name="CountUserRequest" p1:procedure="public.count_user_request" callable="true" p1:Guid="xxx">
<return-scalar column="return_value" type="Int64" />
<query-param name="date_delta_t" p1:source="date_delta_t" p1:server-type="timestamp without time zone" type="Timestamp" />
<query-param name="user_id" p1:source="user_id" p1:server-type="uuid" type="Guid" />select public.count_user_request(:date_delta_t, :user_id) as return_value</sql-query>

The generated C# method is

/// <summary>
    /// There are no comments for CountUserRequest in the schema.
    /// </summary>
    public System.Nullable<long> CountUserRequest(System.Nullable<System.DateTime> date_delta_t, System.Nullable<System.Guid> user_id)
    {

        NHibernate.IQuery query = session.GetNamedQuery(@"CountUserRequest");
        query.SetParameter(@"date_delta_t", date_delta_t);
        query.SetParameter(@"user_id", user_id);
        return ((System.Nullable<long>)(query.UniqueResult()));
    }

When i try to call my function with a DateTime with milliseconds accuracy it's like this information is lost and i can't be so accurate.

I can resolve my problem if i give the Date as string and then make a cast like

'2018-02-13 13:43:08.200'::timestamp without time zone

But i don't understand why i cannot use directly the first method.

[frederic...@free.fr]

Chances are you are using a NHIbernate version lower than 5.0.

Default mapping for .Net DateTime is NHibernate datetime type. And prior to NHibernate 5.0, it was cutting fractional seconds.

Since you use IQuery.SetParameter, the parameter is likely to be typed according to the default mapping for its value type. (In some circumstances it could have a type provided by the query.) So your DateTime parameter ends up typed as a NHibernate datetime type, and with NHibernate v4 or lower, has its fractional seconds cut.

To avoid this, use IQuery.SetTimestamp instead, when your value is not null. (Note that this method is obsoleted in NHibernate 5.0, since the datetime type has replaced timestamp, while the old datetime behavior (cutting fractional seconds) has been moved to datetimeNoMs.

Since your query mapping supply the parameter type, it does not look right to me that it is not taken into account by SetParameter though.