Поддержка Dapper IPAddress/PhysicalAddress/Enum Parameter над Npgsql v3

Question

Npgsql поддерживает разбор System.Net.NetworkInformation.PhysicalAddress а также System.Net.IPAddress из наборов результатов запроса типа macaddr и inet соответственно. Например, следующий класс может быть заполнен с помощью Npgsql с 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");

Поскольку Npgsql v3.0.1 отправляет данные в двоичном виде, я предполагаю, что это означает, что существует некоторое двоичное представление для типов inet и macaddr. Однако, когда я запускаю следующий код, используя те же объявления выше...

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

Я получаю исключение:

Проблема с запросом: SELECT * FROM foo WHERE macaddress =: macAddress
System.NotSupportedException: член macAddress типа System.Net.NetworkInformation.PhysicalAddress не может использоваться в качестве значения параметра

Как получается, что Dapper/Npgsql знает, как анализировать IPAddress а также PhysicalAddress из столбца типа inet и macaddr соответственно, но я не могу использовать эти типы в качестве параметров? В предыдущих версиях Npgsql я просто отправлял ToString() результат в качестве значения параметра, но в Npgsql v3.0.1 следующий код...

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

Создает исключение:

Проблема с запросом: SELECT * FROM foo WHERE macaddress =: macAddress
Npgsql.NpgsqlException: 42883: оператор не существует: macaddr = text

Я знаю, что мог бы изменить запрос на "SELECT * FROM foo WHERE macaddress =:macAddress::macaddr" вместо этого, но мне интересно, есть ли более чистый способ сделать это? Планируется ли добавить поддержку этих типов в ближайшем будущем?

- НАЧАТЬ РЕДАКТИРОВАТЬ -

Я только что понял, что та же самая проблема мучает перечисляемые типы. Если у меня есть параметр перечисления, я могу проанализировать его из результата запроса, но не могу передать перечисление в Postgres. Например:

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

Выше все работает до последней строки, которая выдает следующее исключение:

42883: оператор не существует: bar = integer

Если вместо этого я изменю запрос на:

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

Тогда я получаю исключение:

42846: невозможно привести тип целого числа к столбцу

Единственный способ получить перечисляемые значения в качестве параметров - передать их в виде текста и привести параметр в запросе следующим образом:

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

Конечно, есть лучший способ сделать это. Может ли кто-нибудь пролить свет на то, что это такое?

Answer 1

Благодаря помощи Hambone и Shay, я нашел способ решить эту проблему для IPAddress а также PhysicalAddress типы. Проблема в том, что inet а также macaddr специфичны для Postgres, а Dapper не зависит от поставщика. Поэтому решение состоит в том, чтобы добавить пользовательский обработчик, который устанавливает соответствующий NpgsqlDbType до пересылки этих типов параметров в Npgsql. Пользовательский обработчик может быть закодирован как:

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

    }
}

Затем в статическом конструкторе моего класса уровня доступа к данным (DAL) я просто добавляю строки:

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

Теперь я могу отправить PhysicalAddress а также IPAddress параметры через Dapper, без необходимости их строгости.

Перечисления, однако, представляют другую проблему, так как Dapper 1.42 не поддерживает добавление пользовательских обработчиков перечислений (см. Проблемы Dapper # 259 / # 286). Еще более прискорбно, что Dapper по умолчанию отправляет перечисляемые значения в виде целых чисел в базовую реализацию. Поэтому в настоящее время невозможно отправить перечисленные значения в Npgsql без преобразования их в строки при использовании Dapper 1.42 (или более ранних версий). Я связался с Марком Гравеллом по этому вопросу и надеюсь получить какое-то решение в ближайшем будущем. До этого времени разрешение либо:

1) Используйте Npgsql напрямую, минуя Dapper
2) Отправьте все значения перечисления в виде текста и приведите к соответствующему типу в запросе.

Я лично решил продолжить с варианта № 2.

---

НАЧАТЬ РЕДАКТИРОВАТЬ

Посмотрев исходный код Dapper, я понял, что есть третий вариант, чтобы сделать эту работу. Хотя невозможно создать пользовательский обработчик для каждого перечислимого типа, можно заключить перечислимое значение в SqlMapper.ICustomQueryParameter объект. Поскольку код должен только передать перечисляемое значение в Npgsql, реализация проста:

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

    }
}

Мой код уже настроен так, что каждый параметр добавляется в Dictionary<string, object> затем преобразуется в DynamicParameters Объект в одном пути кода. Из-за этого я смог добавить следующую проверку в цикл, который преобразует из одного в другой:

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

При этом перечисляемые значения передаются в Npgsql без преобразования в их числовой эквивалент (и, следовательно, без потери информации о связанном типе). Весь этот процесс все еще кажется невероятно запутанным, но, по крайней мере, есть способ передать параметры перечисляемых значений через Dapper, используя двоичные формы Npgsql v3.

Answer 2

Поведение Npgsql 3.0 немного отличается от предыдущих версий, когда дело доходит до обработки параметров, и во многих случаях оно несколько строже. В приведенных выше примерах важно различать проблемы, связанные с Dapper (которые не имеют ничего общего с Npgsql), и проблемы Npgsql.

Короче говоря, Npgsql может преобразовывать экземпляры PhysicalAddress в двоичное представление macaddr в PostgreSQL и наоборот. В отличие от предыдущих версий, он больше не будет прозрачно принимать текстовые представления, вам нужно проанализировать их и предоставить экземпляр PhysicalAddress.

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

Проблема здесь, вероятно, в том, что Даппер не знает о типе PhysicalAddress. Проверьте эту проблему, которая была у нас с 3.0.0, где включен обработчик типа Dapper для jsonb, вам придется сделать то же самое с 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);

Проблема здесь в том, что вы предоставляете строку, где ожидается PhysicalAddress, поэтому PostgreSQL жалуется на то, что вы сравниваете тип macaddr с текстовым типом.

Что касается перечислений, Npgsql 3.0.0 включает поддержку написания и чтения перечислений напрямую, без прохождения строкового представления. Однако вам нужно заранее сообщить Npgsql о вашем типе перечисления, предварительно вызвав NpgsqlConnection.RegisterEnumGlobally("pg_enum_type_name"). К сожалению, я еще не дошел до документирования новой поддержки enum, это скоро произойдет.

Answer 3

Я должен признать, что я не знаком с классом DynamicParameters... используя собственные библиотеки NpgSql, однако, я думаю, что вы можете выполнить основную задачу, которую вы перечислили выше.

Это явное объявление параметра:

List<foo> foos = new List<AdLookup.foo>();

NpgsqlConnection conn = new NpgsqlConnection(ConnectionString);
conn.Open();

NpgsqlCommand cmd = new NpgsqlCommand(
    "select * from foo where macaddress = :macAddress", conn);
cmd.Parameters.Add("macAddress", NpgsqlTypes.NpgsqlDbType.MacAddr);
cmd.Parameters[0].Value = PhysicalAddress.Parse("FF-FF-FF-FF-FF-FF");

NpgsqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    foo f = new foo();
    f.ipaddress = (IPAddress)reader.GetValue(0);
    f.macaddress = (PhysicalAddress)reader.GetValue(1);
    foos.Add(f);
}

reader.Close();
conn.Close();

Когда вы создавали свой параметр, вы использовали значение параметра в качестве второго аргумента. Опять же, я не знаком с DynamicParameter класс, но в классе NpgSqlParameter, вы могли бы сделать это с помощью AddWithValue метод:

cmd.Parameters.AddWithValue("macAddress", PhysicalAddress.Parse("FF-FF-FF-FF-FF-FF"));

Это может заменить cmd.Parameters.Add а также cmd.Parameters[0] линии выше.

Опять же, я понятия не имею, помогает ли это или нет... но я хотел обратиться к одному способу отправки параметра в запрос.

Если DynamicParameters опоры AddWithValueВаше решение может быть таким простым.