NHibernate, Fluent.NHibernate and PostgreSql: How to map PostgreSql Json type using Fluent.NHibernate

Blobbed.cs

[Serializable]
public class Blobbed<T> : IUserType where T : class
{
    public new bool Equals(object x, object y)
    {
        if (x == null && y == null)
            return true;

        if (x == null || y == null)
            return false;

        var xdocX = JsonConvert.SerializeObject(x);
        var xdocY = JsonConvert.SerializeObject(y);

        return xdocY == xdocX;
    }

    public int GetHashCode(object x)
    {
        return x == null ? 0 : x.GetHashCode();
    }

    public object NullSafeGet(IDataReader rs, string[] names, object owner)
    {
        if (names.Length != 1)
            throw new InvalidOperationException("Only expecting one column...");

        var val = rs[names[0]] as string;

        if (val != null && !string.IsNullOrWhiteSpace(val))
            return JsonConvert.DeserializeObject<T>(val);

        return null;
    }

    public void NullSafeSet(IDbCommand cmd, object value, int index)
    {
        var parameter = (NpgsqlParameter) cmd.Parameters[index];
        parameter.NpgsqlDbType = NpgsqlDbType.Json;

        if (value == null)
            parameter.Value = DBNull.Value;
        else
            parameter.Value = JsonConvert.SerializeObject(value);
    }

    public object DeepCopy(object value)
    {
        if (value == null)
            return null;
        
        var serialized = JsonConvert.SerializeObject(value);
        return JsonConvert.DeserializeObject<T>(serialized);
    }

    public object Replace(object original, object target, object owner)
    {
        return original;
    }

    public object Assemble(object cached, object owner)
    {
        var str = cached as string;
        return string.IsNullOrWhiteSpace(str) ? null : JsonConvert.DeserializeObject<T>(str);
    }

    public object Disassemble(object value)
    {
        return value == null ? null : JsonConvert.SerializeObject(value);
    }

    public SqlType[] SqlTypes
    {
        //we must write extended SqlType and return it here
        get
        {
            return new SqlType[] { new NpgsqlExtendedSqlType(DbType.Object, NpgsqlTypes.NpgsqlDbType.Json) };
        }
    }

    public Type ReturnedType
    {
        get { return typeof(T); }
    }

    public bool IsMutable
    {
        get { return true; }
    }
}

CustomEntity.cs

public class CustomEntity
{
    public virtual Guid Id { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string Surname { get; set; }
    public virtual string AddressStreet { get; set; }
    public virtual string AddressCity { get; set; }
    public virtual string AddressZip { get; set; }
    public virtual string AddressCountry { get; set; }
    public virtual DateTime AddressLivingFromDate { get; set; }
    public virtual string Gender { get; set; }
    public virtual string PhoneNumber { get; set; }
    public virtual string Email { get; set; }
    public virtual DateTime DateOfBirth { get; set; }
    public virtual string CountryOfBirth { get; set; }
    public virtual decimal GrossIncome { get; set; }
    public virtual string SubjectType { get; set; }
    public virtual string MothersMaidenName { get; set; }
    public virtual string FullName { get; set; }
    public virtual string EmployerName { get; set; }
    public virtual string EmployerPhoneNumber { get; set; }
    public virtual decimal MonthlyExpenses { get; set; }
    public virtual string WebPage { get; set; }
    public virtual int NumberOfChildren { get; set; }
}

CustomEntityWithSerializedData.cs

public class CustomEntityWithSerializedData
{
    public virtual Guid Id { get; set; }
    public virtual CustomEntity SerializedData { get; set; }
}

CustomEntityWithSerializedDataPostgreMap.cs

public class CustomEntityWithSerializedDataPostgreMap : ClassMap<CustomEntityWithSerializedData>
{
    public CustomEntityWithSerializedDataPostgreMap()
    {
        Table("\"CustomEntityWithSerializedData\"");
        Id(m => m.Id, "\"Id\"");
        Map(m => m.SerializedData, "\"SerializedData\"").
            CustomType<Blobbed<CustomEntity>>(). //here is my custom mapping on Json type
            Not.Nullable();
    }
}

NpgsqlDriverExtended.cs

public class NpgsqlDriverExtended : NpgsqlDriver
{
    protected override void InitializeParameter(IDbDataParameter 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);
    }

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

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

NpgsqlExtendedSqlType.cs

public class NpgsqlExtendedSqlType : SqlType
{
    public NpgsqlExtendedSqlType(DbType dbType, NpgsqlDbType npgDbType)
        : base(dbType)
    {
        this.npgDbType = npgDbType;
    }

    public NpgsqlExtendedSqlType(DbType dbType, NpgsqlDbType npgDbType, int length)
        : base(dbType, length)
    {
        this.npgDbType = npgDbType;
    }

    public NpgsqlExtendedSqlType(DbType dbType, NpgsqlDbType npgDbType, byte precision, byte scale)
        : base(dbType, precision, scale)
    {
        this.npgDbType = npgDbType;
    }

    private readonly NpgsqlDbType npgDbType;
    public NpgsqlDbType NpgDbType
    {
        get
        {
            return this.npgDbType;
        }
    }
}

thanks:
https://gist.github.com/phillip-haydon/1936188
http://nhforge.org/wikis/howtonh/use-postgresql-arrays-with-nhibernate.aspx

Thank you!

I get this error: column "SerializedData" is of type json but expression is of type text.

I kept getting Dialect does not support DbType.Object (when generating the schema). This article http://daanleduc.nl/2014/02/08/fluent-nhibernate-postgresql-column-array/ solved it for me. In short add 'CustomSqlType("jsonb")' to the mapping. Json instead of jsonb is also possible.

Fixes column "x" is of type json but expression is of type text. exception

thanks @Rovak

I found this when searching for how to map a string property into json postgres type. In case somebody is looking for the same: https://gist.github.com/xhafan/1b001bd9618d2c83a5839e356226739f

This last comment/link was the secret, thank you.