Reason why NHibernate persist "wrong" data from DateTime property to timestamptz field

namespace TestNpgsql
{
    using System;
    using Npgsql;
    using NpgsqlTypes;
    using System.Data;

    class Program
    {
        static readonly string utcString = "2018-05-04T17:37:00.0000000Z";

        static readonly DateTime local = DateTime.Parse(utcString);
        static readonly DateTime utc = DateTime.Parse(utcString).ToUniversalTime();

        static string insertTest = "insert into test(a, b, c, d) values(:a, :b, :c, :d)";
        
        static void Main(string[] args)
        {                    
            using (var conn = 
                   new NpgsqlConnection("Server=127.0.0.1;Database=test;User Id=postgres;Password=opensesame93;")
                  )

            {
                conn.Open();
                
                Console.WriteLine($"utc string {utcString}");
                Console.WriteLine($"local value is {local} kind is {local.Kind}");
                Console.WriteLine($"utc value is {utc} kind is {utc.Kind}");
                
                var cmd = new NpgsqlCommand(@"insert into test(a, b, c, d) values(:a, :b, :c, :d)", conn);
                
                
                Insert(conn);
                //    "2018-05-05 01:37:00"    "2018-05-05 01:37:00"    "2018-05-05 01:37:00+08"    "2018-05-05 01:37:00+08"                
                                                                
                
                Insert(conn, DbType.DateTimeOffset); 
                //     "2018-05-05 01:37:00"    "2018-05-05 01:37:00"    "2018-05-05 01:37:00+08"    "2018-05-05 01:37:00+08"
                
                Insert(conn, DbType.DateTime); // now I see why NHibernate is "wrong"
                //    "2018-05-05 01:37:00"    "2018-05-04 17:37:00"    "2018-05-05 01:37:00+08"    "2018-05-04 17:37:00+08"
                
                
                Insert(conn, NpgsqlDbType.TimestampTZ);
                //    "2018-05-05 01:37:00"    "2018-05-05 01:37:00"    "2018-05-05 01:37:00+08"    "2018-05-05 01:37:00+08"
                
                Insert(conn, NpgsqlDbType.Timestamp);
                //    "2018-05-05 01:37:00"    "2018-05-04 17:37:00"    "2018-05-05 01:37:00+08"    "2018-05-04 17:37:00+08"
            }                                         
        }
        
         
        static void Insert(NpgsqlConnection conn)
        {
            var cmd = new NpgsqlCommand(insertTest, conn);

            cmd.Parameters.AddWithValue("a", local);
            cmd.Parameters.AddWithValue("b", utc);
            cmd.Parameters.AddWithValue("c", local);
            cmd.Parameters.AddWithValue("d", utc);

            cmd.ExecuteNonQuery();
        }
        
        static void Insert(NpgsqlConnection conn, DbType dbType)
        {            
            var cmd = new NpgsqlCommand(insertTest, conn);
            
            cmd.Parameters.AddRange(new[]
            {
                new NpgsqlParameter { ParameterName = "a", DbType = dbType, Value = local },                    
                new NpgsqlParameter { ParameterName = "b", DbType = dbType, Value = utc },                    
                new NpgsqlParameter { ParameterName = "c", DbType = dbType, Value = local },                    
                new NpgsqlParameter { ParameterName = "d", DbType = dbType, Value = utc },                    
            });

            cmd.ExecuteNonQuery();
        }
        

        static void Insert(NpgsqlConnection conn, NpgsqlDbType npgsqlDbType)
        {
            var cmd = new NpgsqlCommand(insertTest, conn);     
            
            Console.WriteLine($"local value is {local} kind is {local.Kind}");
            Console.WriteLine($"utc value is {utc} kind is {utc.Kind}");

            cmd.Parameters.AddRange(new[]
            {
                new NpgsqlParameter { ParameterName = "a", NpgsqlDbType = npgsqlDbType, Value = local },                    
                new NpgsqlParameter { ParameterName = "b", NpgsqlDbType = npgsqlDbType, Value = utc },                    
                new NpgsqlParameter { ParameterName = "c", NpgsqlDbType = npgsqlDbType, Value = local },                    
                new NpgsqlParameter { ParameterName = "d", NpgsqlDbType = npgsqlDbType, Value = utc },                    
            });

            cmd.ExecuteNonQuery();
        }

              
    }
}

/*
 
-- drop table test;
create table test
(
    id int generated by default as identity primary key,
    a timestamp not null,
    b timestamp not null,
    c timestamptz not null,
    d timestamptz not null
);
 
 */

UPDATE June 1

The latest version of Npgsql, version 4.0, has TimeZone=UTC parameter to connection string.

Without UTC parameter.

test=# select * from test;
 id |          a          |          b          |           c            |           d            
----+---------------------+---------------------+------------------------+------------------------
  1 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 01:37:00+08 | 2018-05-04 17:37:00+08
  2 | 2018-05-05 01:37:00 | 2018-05-05 01:37:00 | 2018-05-05 01:37:00+08 | 2018-05-05 01:37:00+08
  3 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 01:37:00+08 | 2018-05-04 17:37:00+08
  4 | 2018-05-05 01:37:00 | 2018-05-05 01:37:00 | 2018-05-05 01:37:00+08 | 2018-05-05 01:37:00+08
  5 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 01:37:00+08 | 2018-05-04 17:37:00+08
(5 rows)

The TimeZone=UTC connection string parameter fixes the DateTime + timestamptz problem in NHibernate. The InitializeParameter fix won't be needed anymore.

AS of the time of this writing, NHibernate has an embedded Npgsql 3.2.7. Just add Npgsql 4.0 to an NHibernate project so you'll be able to use TimeZone=UTC in the connection string.

test=# select * from test;
 id |          a          |          b          |           c            |           d            
----+---------------------+---------------------+------------------------+------------------------
  1 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 09:37:00+08 | 2018-05-05 01:37:00+08
  2 | 2018-05-04 17:37:00 | 2018-05-04 17:37:00 | 2018-05-05 01:37:00+08 | 2018-05-05 01:37:00+08
  3 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 09:37:00+08 | 2018-05-05 01:37:00+08
  4 | 2018-05-04 17:37:00 | 2018-05-04 17:37:00 | 2018-05-05 01:37:00+08 | 2018-05-05 01:37:00+08
  5 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 09:37:00+08 | 2018-05-05 01:37:00+08

Persist UTC date correctly with NHibernate

I encountered a problem with NHibernate where the UTC date from a DateTime property is not correctly persisting to timestamptz. The utc 2018-05-04T17:37:00.000Z was persisted as 2018-05-04 17:37:00+08 to timestamptz field, which is incorrect.

The solution is to override the DateTime type and make NHibernate treat it as DateTimeOffset.

namespace AspNetCoreExample.Infrastructure.NHibernateNpgsqlInfra
{
    using System.Data.Common;

    using NHibernate.SqlTypes;
    using Npgsql;

    public class NpgsqlDriverExtended : NHibernate.Driver.NpgsqlDriver
    {        
        // this gets called when an SQL is executed
        protected override void InitializeParameter(DbParameter dbParam, string name, SqlType sqlType)
        {            
            if (sqlType is NpgsqlExtendedSqlType && dbParam is NpgsqlParameter)
            {
                this.InitializeParameter(dbParam as NpgsqlParameter, name, sqlType as NpgsqlExtendedSqlType);
            }
            else
            {
                base.InitializeParameter(dbParam, name, sqlType);
                
                if (sqlType.DbType == System.Data.DbType.DateTime)
                {
                    dbParam.DbType = System.Data.DbType.DateTimeOffset;
                }
            }
        }

        // NpgsqlExtendedSqlType is used for Jsonb
        protected virtual void InitializeParameter(NpgsqlParameter dbParam, string name, NpgsqlExtendedSqlType sqlType)
        {
            if (sqlType == null)
            {
                throw new NHibernate.QueryException(string.Format("No type assigned to parameter '{0}'", name));
            }

            dbParam.ParameterName = FormatNameForParameter(name);
            dbParam.DbType        = sqlType.DbType;
            dbParam.NpgsqlDbType  = sqlType.NpgDbType;
        }               
    }
}

With the code above, the utc 2018-05-04T17:37:00.000Z is now correctly persisting as 2018-05-05 01:37:00+08.

Sample wiring of NpgsqlDriver: https://github.com/MichaelBuen/AspNetCoreExample/blob/b78b97f085730cfb9494c3ec15085cccf7f761be/AspNetCoreExample.Ddd.Mapper/_TheMapper.cs#L32


If you are using Npgsql version 4.0 with NHibernate, you don't need this fix anymore. With Npgsql version 4.0, you can just add TimeZone=UTC to NHibernate's connection string. An example: http://www.ienablemuch.com/2018/06/utc-all-things-with-nhibernate-datetime-postgres-timestamptz.html

NHibernate manual mapping-by-code

namespace TestNH
{
    using System;
    using NHibernate.Cfg;

    class Program
    {
        static async System.Threading.Tasks.Task Main(string[] args)
        {
            var cfg = new NHibernate.Cfg.Configuration();

            cfg.DataBaseIntegration(c =>
            {
                c.Driver<NHibernate.Driver.NpgsqlDriver>();
                c.Dialect<NHibernate.Dialect.PostgreSQLDialect>();

                c.ConnectionString = "Server=localhost; Port=5432; Database=test; User Id=postgres; Password=opensesame93";   

                c.LogFormattedSql = true;
                c.LogSqlInConsole = true;

            });


            var mapper = new NHibernate.Mapping.ByCode.ModelMapper();
            mapper.AddMapping<TheTimeMapping>();
            
            var mapping = mapper.CompileMappingForAllExplicitlyAddedEntities();

            cfg.AddMapping(mapping);

            var sf = cfg.BuildSessionFactory();


            using (var session = sf.OpenSession())
            using (var tx = session.BeginTransaction())
            {
                var utcString = "2018-05-04T17:37:00.0000000Z";

                var local = DateTime.Parse(utcString);
                var utc = DateTime.Parse(utcString).ToUniversalTime();

                Console.WriteLine($"local value is {local} kind is {local.Kind}");
                Console.WriteLine($"utc value is {utc} kind is {utc.Kind}");                

                var t = new Test
                {
                    A = local,
                    B = utc,
                    C = local,
                    D = utc
                };
                                              
                await session.PersistAsync(t);

                await tx.CommitAsync();
            }                       
        }
    }


    public class Test
    {
        public virtual int Id { get; set; }

        public virtual DateTime A { get; set; }
        public virtual DateTime B { get; set; }
        public virtual DateTime C { get; set; }
        public virtual DateTime D { get; set; }
    }

    public class TheTimeMapping: NHibernate.Mapping.ByCode.Conformist.ClassMapping<Test>
    {
        public TheTimeMapping()
        {
            Id(x => x.Id, id =>
            {                
                id.Generator(
                    NHibernate.Mapping.ByCode.Generators.Sequence, 
                    generatorMapping => generatorMapping.Params(new { sequence = "test_id_seq"})
                );               
            });

            Property(p => p.A);
            Property(p => p.B);
            Property(p => p.C);
            Property(p => p.D);            
        }
    }

    /*
        create table test
        (
            id int generated by default as identity primary key,
            a timestamp not null,
            b timestamp not null,
            c timestamptz not null,
            d timestamptz not null
        );
     */
}

Outputs:

local value is 05/05/2018 01:37:00 kind is Local
utc value is 05/04/2018 17:37:00 kind is Utc

Database:

Proper way to cache entities from cached query with NHibernate

Despite the Profile entity is cached, it's possible that the CityFk property below cannot get a cached City and it will get the City from the database instead. Causing N + 1 problem.

public static async Task<ProfileDto> GetProfileDto(IDdd ddd, int userId)
{
    var profile = await ddd.GetAsync<Profile>(userId);

    var user = profile?.User ?? await ddd.GetAsync<IdentityDomain.User>(userId);
                
    var dto = new ProfileDto
    {
        Username        = user.UserName,
        AddressLine1    = profile?.AddressLine1,
        AddressLine2    = profile?.AddressLine2,
        StateFk         = profile?.City?.State?.Id ?? 0,
        CityFk          = profile?.City?.Id ?? 0,
        ZipCode         = profile?.ZipCode,
        Email           = user.Email,
        Telephone       = profile?.Telephone,
        MobilePhone     = profile?.MobilePhone
    };

    return dto;
}

This code produces City and State list.

The citiesQuery is cached, but it can not produce cache for City entities.

public static async Task<IEnumerable<StateCityDto>> GetListWithStateAsync(IDdd ddd)
{    
    var citiesQuery =
        from c in ddd.Query<City>().FetchOk(fc => fc.State)
        select new
        {
            Id = c.Id,
            Name = c.Name,
            StateId = c.State.Id,
            StateName = c.State.Name
        };


    var citiesList = await citiesQuery.CacheableOk().ToListAsyncOk();

    var distinctStates =
        citiesList.Select(x => new { x.StateId, x.StateName }).Distinct();


    var stateCityListDto =
        from state in distinctStates
        orderby state.StateName
        select new StateCityDto
        {
            Id = state.StateId,
            Name = state.StateName,
            Cities = (
                from city in citiesList
                where city.StateId == state.StateId
                orderby city.Name
                select new CityDto { Id = city.Id, Name = city.Name }
            ).ToList()
        };


    return stateCityListDto.ToList();
}

After saving the Profile:

NHibernate: 
     UPDATE
         job.profile 
     SET
         city_fk = :p0 
     WHERE
         user_fk = :p1;
     :p0 = 999 [Type: Int32 (0:0:0)], :p1 = 1 [Type: Int32 (0:0:0)]

When GetProfileDto is called again:

NHibernate: 
     SELECT
         profile0_.user_fk as user1_1_0_,
         profile0_.address_line_1 as address2_1_0_,
         profile0_.address_line_2 as address3_1_0_,
         profile0_.city_fk as city4_1_0_,
         profile0_.zip_code as zip5_1_0_,
         profile0_.telephone as telephone6_1_0_,
         profile0_.mobile_phone as mobile7_1_0_ 
     FROM
         job.profile profile0_ 
     WHERE
         profile0_.user_fk=:p0;
     :p0 = 1 [Type: Int32 (0:0:0)]
 NHibernate: 
     SELECT
         city0_.id as id1_0_0_,
         city0_.name as name2_0_0_,
         city0_.state_fk as state3_0_0_ 
     FROM
         job.city city0_ 
     WHERE
         city0_.id=:p0;
     :p0 = 999 [Type: Int32 (0:0:0)]

ProfileDto should get be able to get the City from cached City entities. The SQL log shows otherwise though.

The citiesQuery query in GetListWithStateAsync above, despite the query is cached, that query cannot produce the cache for City entities.

To make GetListWithStateAsync's citiesQuery produce cache for City entities, don't select individual fields of City from the query, to wit:

public static async Task<IEnumerable<StateCityDto>> GetListWithStateAsync(IDdd ddd)
{
    // The citiesQuery is cached. And it also produces cache for City entities.
    var citiesQuery = ddd.Query<City>().FetchOk(c => c.State);
                            
    var citiesList = await citiesQuery.CacheableOk().ToListAsyncOk();

    var distinctStates =
        citiesList.Select(x => new 
            { 
                StateId = x.State.Id, 
                StateName = x.State.Name
            }).Distinct();


    var stateCityListDto =
        from state in distinctStates
        orderby state.StateName
        select new StateCityDto
        {
            Id = state.StateId,
            Name = state.StateName,
            Cities = (
                from city in citiesList
                where city.State.Id == state.StateId
                orderby city.Name
                select new CityDto { Id = city.Id, Name = city.Name }
            ).ToList()
        };


    return stateCityListDto.ToList();
}

After saving the Profile:

NHibernate: 
     UPDATE
         job.profile 
     SET
         city_fk = :p0 
     WHERE
         user_fk = :p1;
     :p0 = 992 [Type: Int32 (0:0:0)], :p1 = 1 [Type: Int32 (0:0:0)]

When GetProfileDto is called again:

NHibernate: 
     SELECT
         profile0_.user_fk as user1_1_0_,
         profile0_.address_line_1 as address2_1_0_,
         profile0_.address_line_2 as address3_1_0_,
         profile0_.city_fk as city4_1_0_,
         profile0_.zip_code as zip5_1_0_,
         profile0_.telephone as telephone6_1_0_,
         profile0_.mobile_phone as mobile7_1_0_ 
     FROM
         job.profile profile0_ 
     WHERE
         profile0_.user_fk=:p0;
     :p0 = 1 [Type: Int32 (0:0:0)]

Without selecting the individual fields of the city in GetListWithStateAsync, the GetProfileDto is now able to get the city from the cached cities produced by cached city query.



Happy coding!

NHibernate 5 IQueryable's DML use with caution

public class User
{
    // ...properties here

    public IEnumerable<ExternalLogin> ExternalLogins { get; protected set; } = new List<ExternalLogin>();

    public void DeleteAllExternalLogins()
    {
        await this.ExternalLogins.AsQueryable()
        .DeleteAsync(new System.Threading.CancellationToken());
    }
}    

public class ExternalLogin
{
    protected User User { get; }

    internal ExternalLogin(User applicationUser) => this.User = applicationUser;
    
    public int Id { get; protected set; }

    public string LoginProvider { get; internal protected set; } // provider: facebook, google, etc

    public string ProviderKey { get; internal protected set; } // user's id from facebook, google, etc

    public string DisplayName { get; internal protected set; } // seems same as provider   
}        

The above DML will not put a user id filter, instead it will just issue this:

delete from external_login;

Happy Coding!