Dapper IPAddress/PhysicalAddress/Enum Parameter Support Over Npgsql v3

c# dapper npgsql

Question

Npgsql supports parsing System.Net.NetworkInformation.PhysicalAddress and System.Net.IPAddress from query result sets of type macaddr and inet, respectively. For example, the following class can be populated using Npgsql with Dapper:

-- Postgres CREATE TABLE command
CREATE TABLE foo (
    ipaddress inet,
    macaddress macaddr
);
// C# class for type "foo"
public class foo
{
    public IPAddress ipaddress { get; set; }
    public PhysicalAddress macaddress { get; set; }
}

// Code that loads all data from table "foo"
IDbConnection connection = new NpgsqlConnection(connectionString);
var foos = connection.Query<foo>("SELECT * FROM foo");

Since Npgsql v3.0.1 sends data in binary form, I assume this means there is some binary representation for types inet and macaddr. However, when I run the following code using the same declarations above...

// Code that tries to load a specific row from "foo"
var query = "SELECT * FROM foo WHERE macaddress = :macAddress";
var queryParams = new DynamicParameters();
queryParams.Add("macAddress", PhysicalAddress.Parse("FF-FF-FF-FF-FF-FF"));
IDbConnection connection = new NpgsqlConnection(connectionString);
var foos = connection.Query<foo>(query, queryParams);

I get the exception:

Problem with query: SELECT * FROM foo WHERE macaddress = :macAddress
System.NotSupportedException : The member macAddress of type System.Net.NetworkInformation.PhysicalAddress cannot be used as a parameter value

How is it that Dapper/Npgsql knows how to parse an IPAddress and PhysicalAddress from a column of type inet and macaddr, respectively, yet I am unable to use these types as parameters? In previous versions of Npgsql, I simply sent the ToString() result as the parameter value, but in Npgsql v3.0.1 the following code...

// Code that tries to load a specific row from "foo"
// The only change from above is the "ToString()" method called on PhysicalAddress
var query = "SELECT * FROM foo WHERE macaddress = :macAddress";
var queryParams = new DynamicParameters();
queryParams.Add("macAddress", PhysicalAddress.Parse("FF-FF-FF-FF-FF-FF").ToString());
IDbConnection connection = new NpgsqlConnection(connectionString);
var foos = connection.Query<foo>(query, queryParams);

Generates the exception:

Problem with query: SELECT * FROM foo WHERE macaddress = :macAddress
Npgsql.NpgsqlException : 42883: operator does not exist: macaddr = text

I know I could change the query to be "SELECT * FROM foo WHERE macaddress = :macAddress::macaddr" instead, but am wondering if there is a cleaner way to go about this? Is there any plan to add support for these types in the near future?

-- BEGIN EDIT --

I just realized that the same issue is plaguing enumerated types. If I have an enumeration parameter, I can parse it from a query result, but am unable to pass the enumeration to Postgres. For example:

CREATE TYPE bar AS ENUM (
    val1,
    val2
);

CREATE TABLE log (
    mybar bar
);
public enum bar
{
    val1,
    val2
}

public class log
{
    public bar mybar { get; set; }
}

// Code that loads all data from table "log"
NpgsqlConnection.RegisterEnumGlobally<bar>();
IDbConnection connection = new NpgsqlConnection(connectionString);
var logs = connection.Query<log>("SELECT * FROM log");

// Code that attempts to get rows from log with a specific enum
var query = "SELECT * FROM log WHERE mybar = :barParam";
var queryParams = new DynamicParameters();
queryParams.Add("barParam", bar.val1);
// The following throws an exception
logs = connection.Query<log>(query, queryParams);

In the above, everything works until the last line which throws the following exception:

42883: operator does not exist: bar = integer

If instead, I change the query to be:

SELECT * FROM log WHERE mybar = :barParam::bar

Then I get the exception:

42846: cannot cast type integer to bar

The only way I can get enumerated values to be passed as parameters, is to pass them as text and cast the parameter in the query, as follows:

// Code that successfully performs the query
var query = "SELECT * FROM log WHERE mybar = :barParam::bar";
var queryParams = new DynamicParameters();
queryParams.Add("barParam", bar.val1.ToString());
logs = connection.Query<log>(query, queryParams);

Surely there is a better way to go about this. Can anyone shed some light as to what that is?

Accepted Answer

Thanks to the help from Hambone and Shay, I figured out a way to resolve this for IPAddress and PhysicalAddress types. The issue is that inet and macaddr are Postgres-specific, and Dapper appears to be provider agnostic. Therefore, the solution is to add a custom handler that sets the appropriate NpgsqlDbType prior to forwarding these parameter types to Npgsql. The custom handler can be coded as:

using System;
using System.Data;
using Dapper;
using Npgsql;
using NpgsqlTypes;

namespace MyNamespace
{
    internal class PassThroughHandler<T> : SqlMapper.TypeHandler<T>
    {

        #region Fields

        /// <summary>Npgsql database type being handled</summary>
        private readonly NpgsqlDbType _dbType;

        #endregion

        #region Constructors

        /// <summary>Constructor</summary>
        /// <param name="dbType">Npgsql database type being handled</param>
        public PassThroughHandler(NpgsqlDbType dbType)
        {
            _dbType = dbType;
        }

        #endregion

        #region Methods

        public override void SetValue(IDbDataParameter parameter, T value)
        {
            parameter.Value = value;
            parameter.DbType = DbType.Object;
            var npgsqlParam = parameter as NpgsqlParameter;
            if (npgsqlParam != null)
            {
                npgsqlParam.NpgsqlDbType = _dbType;
            }
        }

        public override T Parse(object value)
        {
            if (value == null || value == DBNull.Value)
            {
                return default(T);
            }
            if (!(value is T))
            {
                throw new ArgumentException(string.Format(
                    "Unable to convert {0} to {1}",
                    value.GetType().FullName, typeof(T).FullName), "value");
            }
            var result = (T)value;
            return result;
        }

        #endregion

    }
}

Then, in the static constructor of my data access layer (DAL) class, I simply add the lines:

var ipAddressHandler  =
    new PassThroughHandler<IPAddress>(NpgsqlDbType.Inet);
var macAddressHandler =
    new PassThroughHandler<PhysicalAddress>(NpgsqlDbType.MacAddr);
SqlMapper.AddTypeHandler(ipAddressHandler);
SqlMapper.AddTypeHandler(macAddressHandler);

Now I can send PhysicalAddress and IPAddress parameters through Dapper, without the need to stringify them.

Enums, however, present another challenge since Dapper 1.42 doesn't support adding custom enum handlers (See Dapper issues #259/#286). Even more unfortunate, is that Dapper by default sends enumerated values as integers to the underlying implementation. Therefore, it is currently impossible to send enumerated values to Npgsql without converting them to strings when using Dapper 1.42 (or earlier). I have contacted Marc Gravell about this issue, and hope to get some sort of resolution in the near future. Until that time, the resolution is either:

1) Use Npgsql directly, bypassing Dapper
2) Send all enum values as text, and cast to the appropriate type in the query

I personally opted to continue with option #2.


BEGIN EDIT

After looking through the Dapper source code, I realized there is a third option to make this work. While it is not possible to create a custom handler for each enumerated type, it is possible to wrap an enumerated value in a SqlMapper.ICustomQueryParameter object. Since the code only needs to pass the enumerated value to Npgsql, the implementation is simple:

using System;
using System.Data;
using Dapper;

namespace MyNamespace
{
    internal class EnumParameter : SqlMapper.ICustomQueryParameter
    {

        #region Fields

        /// <summary>Enumerated parameter value</summary>
        private readonly Enum _val;

        #endregion

        #region Constructors

        /// <summary>Constructor</summary>
        /// <param name="val">Enumerated parameter value</param>
        public EnumParameter(Enum val)
        {
            _val = val;
        }

        #endregion

        #region Methods

        public void AddParameter(IDbCommand command, string name)
        {
            var param = command.CreateParameter();
            param.ParameterName = name;
            param.DbType = DbType.Object;
            param.Value = _val;
            command.Parameters.Add(param);
        }

        #endregion

    }
}

My code was already set up such that each parameter is added to a Dictionary<string, object>, then converted to a DynamicParameters object in a single code path. Because of this, I was able to add the following check to the loop that converts from one to the other:

var queryParams = new DynamicParameters();
foreach (var kvp in paramDict)
{
    var enumParam = kvp.Value as Enum;
    if (enumParam == null)
    {
        queryParams.Add(kvp.key, kvp.Value);
    }
    else
    {
        queryParams.Add(kvp.key, new EnumParameter(enumParam));
    }
}

By doing this, the enumerated values are passed to Npgsql without being converted to their numeric equivalent (and thus, without losing associated type information). This entire process still seems incredibly convoluted, but at least there is a way to pass enumerated value parameters through Dapper using the Npgsql v3 binary forms.


Popular Answer

Npgsql 3.0's behavior is a bit different from previous versions when it comes to parameter handling, and in many cases it is somewhat stricter. In your examples above it's important to distinguish between Dapper-related issues (which have nothing to do with Npgsql) and Npgsql issues.

In a nutshell, Npgsql can convert PhysicalAddress instances to the PostgreSQL binary representation of macaddr and vice versa. Unlike previous versions, it will no longer accept textual representations transparently, it's up to you to parse them and provide a PhysicalAddress instance.

var query = "SELECT * FROM foo WHERE macaddress = :macAddress";
var queryParams = new DynamicParameters();
queryParams.Add("macAddress", PhysicalAddress.Parse("FF-FF-FF-FF-FF-FF"));
IDbConnection connection = new NpgsqlConnection(connectionString);
var foos = connection.Query<foo>(query, queryParams);

The problem here is probably Dapper not being aware of the PhysicalAddress type. Check out this issue we had with 3.0.0 where a Dapper type handler for jsonb is included, you'll have to do the same with PhysicalAddress.

// Code that tries to load a specific row from "foo"
// The only change from above is the "ToString()" method called on PhysicalAddress
var query = "SELECT * FROM foo WHERE macaddress = :macAddress";
var queryParams = new DynamicParameters();
queryParams.Add("macAddress", PhysicalAddress.Parse("FF-FF-FF-FF-FF-FF").ToString());
IDbConnection connection = new NpgsqlConnection(connectionString);
var foos = connection.Query<foo>(query, queryParams);

The problem here is that you're providing a string where a PhysicalAddress is expected, this is why PostgreSQL complains about you comparing a macaddr type to a text type.

Regarding enums, Npgsql 3.0.0 includes support for writing and reading enums directly without going through the string representation. However, you do need to let Npgsql know about your enum type in advance, by calling NpgsqlConnection.RegisterEnumGlobally("pg_enum_type_name") in advance. Unfortunately I haven't gotten around to documenting the new enum support yet, this will happen soon.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why