Cannot GET /

I get that error when I create project on a junction directory


This junction directory:

C:\User\Developer\Codes

points to:

D:\dev-codes


To avoid the error, just create the project directly on D:\dev-codes


I want to emulate Unix/Linux's symlink in Windows by using junction directory, so the directory appears local to current user, and also to prevent the internal SSD from wearing out quickly. However, for some reasons, Visual Studio is having problems compiling from a junction directory.

Ternary all the things with C# 7

This code:

object NHibernate.UserTypes.IUserType.NullSafeGet(
    System.Data.Common.DbDataReader rs, string[] names,
    NHibernate.Engine.ISessionImplementor session, 
    object owner
)
{
    if (names.Length != 1)
        throw new System.InvalidOperationException("Only expecting one column...");

    object value = rs[names[0]];

    if (value is System.DateTime)
        return ((System.DateTime)value).ToUniversalTime();
    else
        return null;
}

Can be rewritten with C# 7's pattern matching:

object NHibernate.UserTypes.IUserType.NullSafeGet(
    System.Data.Common.DbDataReader rs, string[] names,
    NHibernate.Engine.ISessionImplementor session, 
    object owner
)
{
    if (names.Length != 1)
        throw new System.InvalidOperationException("Only expecting one column...");

    if (rs[names[0]] is System.DateTime value)
        return value.ToUniversalTime();
    else
        return null;
}

Nice, the variable declaration is inlined. It only means one thing, ternary!

object NHibernate.UserTypes.IUserType.NullSafeGet(
    System.Data.Common.DbDataReader rs, string[] names,
    NHibernate.Engine.ISessionImplementor session, 
    object owner
)
{
    if (names.Length != 1)
        throw new System.InvalidOperationException("Only expecting one column...");

    return rs[names[0]] is System.DateTime value ? value.ToUniversalTime() : (System.DateTime?) null;
}

And now that exceptions can be thrown inside of ternary statement? Ternary all the things!

object NHibernate.UserTypes.IUserType.NullSafeGet(
    System.Data.Common.DbDataReader rs, string[] names,
    NHibernate.Engine.ISessionImplementor session, 
    object owner
)
{            
    return 
        names.Length != 1 ? 
            throw new System.InvalidOperationException("Only expecting one column...")
        : rs[names[0]] is System.DateTime value ? 
            value.ToUniversalTime() 
        : 
            (System.DateTime?) null;
}

And who uses return statement in the 21st century? Use lambda expression syntax!

object NHibernate.UserTypes.IUserType.NullSafeGet(
    System.Data.Common.DbDataReader rs, string[] names,
    NHibernate.Engine.ISessionImplementor session, 
    object owner
)
=>
    names.Length != 1 ? 
        throw new System.InvalidOperationException("Only expecting one column...")
    : rs[names[0]] is System.DateTime value ? 
        value.ToUniversalTime() 
    : 
        (System.DateTime?) null;        

UTC all the things with NHibernate+DateTime + Postgres+timestamptz

Using Npgsql version 4.0, NHibernate can persist UTC using DateTime + timestamptz with ease. To do that, just pass the TimeZone=UTC to NHibernate's connection string.

If you are using older version of Npgsql, you cannot use TimeZone=UTC in NHibernate's connection string. Here's how to persist UTC to timestamptz with NHibernate using the older Npgsql http://www.ienablemuch.com/2018/05/persist-utc-date-correctly-with-nhibernate.html

namespace TestNhUtc
{
    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; " +
                    "TimeZone=UTC";


                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();


            /// Test code starts here            

            var utcString = "2018-05-04T17:37:00.0000000Z";

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

            var zuluFormat = "yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fff'Z'";
            var utcZuluFormat = utc.ToString(zuluFormat);



            int savedId;
            using (var session = sf.OpenSession())
            using (var tx = session.BeginTransaction())
            {
                var t = new Test
                {
                    UtcString = utcZuluFormat,
                    TheTimestampTz = utc,
                };

                await session.PersistAsync(t);

                await tx.CommitAsync();

                savedId = t.Id;
            }

            DateTime readDateTime;
            using (var session = sf.OpenSession())
            using (var tx = session.BeginTransaction())
            {
                var t = await session.GetAsync<Test>(savedId);

                readDateTime = t.TheTimestampTz;

                t.UtcString = utcZuluFormat;
                t.TheTimestampTz = utc;

                await tx.CommitAsync();
            }


            Console.WriteLine($@"
                {nameof(utc)}    : {utcZuluFormat} kind:{utc.Kind}
                {nameof(local)}  : {local} kind:{local.Kind}

                {nameof(readDateTime)} {readDateTime} kind:{readDateTime.Kind}

                Both resolves to same value? {readDateTime == utc} 
            ");

        }
    }

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

        public virtual string UtcString { get; set; }
        public virtual DateTime TheTimestampTz { get; set; }            
    }

    public class TheTimeMapping : NHibernate.Mapping.ByCode.Conformist.ClassMapping<Test>
    {
        public TheTimeMapping()
        {
            Table("test");

            Id(x => x.Id, id =>
            {
                id.Generator(
                    NHibernate.Mapping.ByCode.Generators.Sequence,
                    generatorMapping => generatorMapping.Params(new { sequence = "test_id_seq" })
                );
            });

            Property(p => p.UtcString, cm => cm.Column("utc_string"));

            Property(p => p.TheTimestampTz, cm => cm.Column("the_timestamptz"));

            DynamicUpdate(true);
            SelectBeforeUpdate(true);
        }
    }
}
/*
-- drop table test;

create table test
(
    id serial primary key,          
    utc_string text not null,
    the_timestamptz timestamptz not null
);

*/

It has a problem though, it suffers from ghost-update problem. If the consumer of your REST service sends their datetime in UTC, or a datepicker/datetimepicker sends its value in UTC e.g., 2018-05-04T17:37:00.0000000Z, the UPDATE routine would cause an update on the timestamptz column even if that column's existing value (e.g., 2018-05-05T01:37:00+08:00) resolves to "same" value as the UTC.

That is due to NHibernate's default reading of timestamptz as DateKind.Local. However, the value 2018-05-04T17:37:00.0000000Z is DateKind.Utc. NHibernate will not attempt to compare the two as the same as they are different kind of Date; consequently, NHibernate will issue an update on the timestamptz column regardless of the sameness of new value to original value.


The problem's output:

NHibernate: 
    select
        nextval ('test_id_seq')
NHibernate: 
    INSERT 
    INTO
        test
        (utc_string, the_timestamptz, Id) 
    VALUES
        (:p0, :p1, :p2);
    :p0 = '2018-05-04T17:37:00.000Z' [Type: String (0:0:0)], 
    :p1 = 2018-05-04T17:37:00.0000000Z [Type: DateTime (0:0:0)], 
    :p2 = 10 [Type: Int32 (0:0:0)]
NHibernate: 
    SELECT
        test0_.Id as id1_0_0_,
        test0_.utc_string as utc2_0_0_,
        test0_.the_timestamptz as the3_0_0_ 
    FROM
        test test0_ 
    WHERE
        test0_.Id=:p0;
    :p0 = 10 [Type: Int32 (0:0:0)]
NHibernate: 
    UPDATE
        test 
    SET
        the_timestamptz = :p0 
    WHERE
        Id = :p1;
    :p0 = 2018-05-04T17:37:00.0000000Z [Type: DateTime (0:0:0)], 
    :p1 = 10 [Type: Int32 (0:0:0)]

                utc    : 2018-05-04T17:37:00.000Z kind:Utc
                local  : 05/05/2018 01:37:00 kind:Local

                readDateTime 05/05/2018 01:37:00 kind:Local

                Both resolves to same value? False 

We can make NHibernate make timestamptz return its universal value, a.k.a., UTC. So if it's exactly the same as the new UTC value, NHibernate will not update the timestamptz column.

Here's how, on your table mapping, set the timestamptz field's NHibernate Type mapping to your custom type. Do note that you will still use DateTime as the type for the property, you cannot use DateTimeOffset as timestamptz (a.k.a., timestamp with time zone) does not store time zone. timestamptz's time zone is just for presentation purposes only (e.g., in psql/pgAdmin), following dates stores as the same value, the time zone offset is not stored.

test=# set timezone to 'Asia/Manila'; -- Manila is +08:00
SET
test=# 
test=# create table x (y serial primary key, z timestamptz);
CREATE TABLE
test=# 
test=# insert into x(z) values
test-# ('2018-05-04T17:37:00.000Z'),
test-# ('2018-05-05T01:37:00+08:00'),
test-# ('2018-05-05T02:37:00+09:00');
INSERT 0 3
test=# 
test=# select * from x;
 y |           z            
---+------------------------
 1 | 2018-05-05 01:37:00+08
 2 | 2018-05-05 01:37:00+08
 3 | 2018-05-05 01:37:00+08
(3 rows)

test=# 
test=# set timezone to 'Asia/Tokyo';
SET
test=# select * from x;
 y |           z            
---+------------------------
 1 | 2018-05-05 02:37:00+09
 2 | 2018-05-05 02:37:00+09
 3 | 2018-05-05 02:37:00+09
(3 rows)

test=# 
test=# set timezone to 'UTC';
SET
test=# select * from x;
 y |           z            
---+------------------------
 1 | 2018-05-04 17:37:00+00
 2 | 2018-05-04 17:37:00+00
 3 | 2018-05-04 17:37:00+00
(3 rows)

test=# 

I think NHibernate+timestamptz causes too much mansplaining in the Postgres community. Just like when you have to explain to someone that there is no Postgre database, it's just Postgres or PostgreSQL, as historically Postgres is a Post-Ingres database. Is there an Ingre database? Nonetheless, Postgres has only few gotchas as compared to MySQL.

Well you can use DateTimeOffset for persisting UTC to timestamptz, but NHibernate/Npgsql would throw an exception that it cannot convert DateTime to DateTimeOffset when you get database value from NHibernate/Npgsql, how would that be useful? You can write but you cannot read it back. I digress.

Here's how you can return DateTime with DateKind.Utc.

public class TheTimeMapping : NHibernate.Mapping.ByCode.Conformist.ClassMapping<Test>
{
    public TheTimeMapping()
    {
        Table("test");

        Id(x => x.Id, id =>
        {
            id.Generator(
                NHibernate.Mapping.ByCode.Generators.Sequence,
                generatorMapping => generatorMapping.Params(new { sequence = "test_id_seq" })
            );
        });

        Property(p => p.UtcString, cm => cm.Column("utc_string"));

        Property(p => p.TheTimestampTz, cm =>
        {
            cm.Column("the_timestamptz");

            // Here's how
            cm.Type<Infra.CustomUtcType>();
        });

        DynamicUpdate(true);
        SelectBeforeUpdate(true);
    }
}

Here is the Infra.CustomUtcType definition:

namespace Infra
{
    [System.Serializable]
    public class CustomUtcType : NHibernate.UserTypes.IUserType
    {
        bool NHibernate.UserTypes.IUserType.IsMutable => false;

        System.Type NHibernate.UserTypes.IUserType.ReturnedType => typeof(System.DateTime);

        NHibernate.SqlTypes.SqlType[] NHibernate.UserTypes.IUserType.SqlTypes => 
            new [] { new NHibernate.SqlTypes.SqlType(System.Data.DbType.DateTime) };

        object NHibernate.UserTypes.IUserType.Assemble(object cached, object owner) => cached;

        object NHibernate.UserTypes.IUserType.DeepCopy(object value) => value;

        object NHibernate.UserTypes.IUserType.Disassemble(object value) => value;

        object NHibernate.UserTypes.IUserType.Replace(object original, object target, object owner) => original;

        int NHibernate.UserTypes.IUserType.GetHashCode(object x) => x == null ? 0 : x.GetHashCode();

        void NHibernate.UserTypes.IUserType.NullSafeSet(
            System.Data.Common.DbCommand cmd, 
            object value, 
            int index,
            NHibernate.Engine.ISessionImplementor session
        ) 
        => cmd.Parameters[index].Value = value != null ? value: System.DBNull.Value;

        bool NHibernate.UserTypes.IUserType.Equals(object x, object y)
        =>
            x == null && y == null ?
                true
            : x == null || y == null ?
                false
            :
                ((System.DateTime)y).Equals(x);
        

        object NHibernate.UserTypes.IUserType.NullSafeGet(
            System.Data.Common.DbDataReader rs, string[] names,
            NHibernate.Engine.ISessionImplementor session, 
            object owner
        )
        =>
            names.Length != 1 ? 
                throw new System.InvalidOperationException("Only expecting one column...")
            : rs[names[0]] is System.DateTime value ? 
                value.ToUniversalTime() 
            : 
                (System.DateTime?) null;        
    }
}

No more ghost-update problem, yay!

NHibernate: 
    select
        nextval ('test_id_seq')
NHibernate: 
    INSERT 
    INTO
        test
        (utc_string, the_timestamptz, Id) 
    VALUES
        (:p0, :p1, :p2);
    :p0 = '2018-05-04T17:37:00.000Z' [Type: String (0:0:0)], 
    :p1 = 2018-05-04T17:37:00.0000000Z [Type: DateTime (0:0:0)], 
    :p2 = 11 [Type: Int32 (0:0:0)]
NHibernate: 
    SELECT
        test0_.Id as id1_0_0_,
        test0_.utc_string as utc2_0_0_,
        test0_.the_timestamptz as the3_0_0_ 
    FROM
        test test0_ 
    WHERE
        test0_.Id=:p0;
    :p0 = 11 [Type: Int32 (0:0:0)]

                utc    : 2018-05-04T17:37:00.000Z kind:Utc
                local  : 05/05/2018 01:37:00 kind:Local

                readDateTime 05/04/2018 17:37:00 kind:Utc

                Both resolves to same value? True 

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