[nhusers] 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.

[f***@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.

Post by 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
<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)
{
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.