New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Nested Composite Array To List #2242

Open
rstander opened this Issue Nov 28, 2018 · 3 comments

Comments

Projects
None yet
3 participants
@rstander

rstander commented Nov 28, 2018

I have a composite PG type that nests an array of another composite type

CREATE TYPE ahm.monitoring_point_formula_cache_item AS (
monitoring_point_id bigint,
formula text,
parameters ahm.monitoring_point_parameter_cache_item[]
);

CREATE TYPE ahm.monitoring_point_parameter_cache_item AS (
parameter_name text,
parameter_value text
);

In C# I have a class structure that represents the above and both are registered correctly with the type mapper.

public class MonitoringPointParameterCacheItem
{
    [PgName("parameter_name")]
    public string ParameterName { get; set; }

    [PgName("parameter_value")]
    public string ParameterValue { get; set; }
}

public class MonitoringPointFormulaCacheItem
{
    [PgName("formula")]
    public string Formula { get; set; }

    [PgName("monitoring_point_id")]
    public long MonitoringPointId { get; set; }

    [PgName("parameters")]
    public List<MonitoringPointParameterCacheItem> Parameters { get; set; }
}

When running a query that returns ahm.monitoring_point_formula_cache_item I get the exception below when trying to read it from reader.

Object of type 'Pragma.OnKey.AHM.MonitoringPointReadings.MonitoringPointParameterCacheItem[]' cannot be converted to type 'System.Collections.Generic.List`1[Pragma.OnKey.AHM.MonitoringPointReadings.MonitoringPointParameterCacheItem]'.

When I change the Parameters property from List to an [] it works fine. I was under the impression that [] to List conversion was supported in npgsql, but I assume that it does not work for nested types.

Is this someting that can be supported in future?

Npgsql version: 4.0.2
PostgreSQL version: 10
Operating system: Win 10 Pro

@YohDeadfall

This comment has been minimized.

Member

YohDeadfall commented Nov 28, 2018

The reason of it is the current implementation of UnmappedCompositeHandler. It reads a value using the default type which is an array for ArrayHandler<T>.

To fix it the handler must be changed to generate code via expression trees. This will also improve performance.

@YohDeadfall YohDeadfall added this to the 4.1 milestone Nov 28, 2018

@roji

This comment has been minimized.

Member

roji commented Nov 28, 2018

Probably unrelated note: it is really recommended to map your composites, as described in the docs. We may end up removing the current form of unmapped composite support, possibly replacing it with mapping to value tuples (#2097).

@rstander

This comment has been minimized.

rstander commented Nov 28, 2018

Probably unrelated note: it is really recommended to map your composites, as described in the docs. We may end up removing the current form of unmapped composite support, possibly replacing it with mapping to value tuples (#2097).

Yes I do map them up using
NpgsqlConnection.GlobalTypeMapper.MapComposite("ahm.monitoring_point_formula_cache_item");
NpgsqlConnection.GlobalTypeMapper.MapComposite("ahm.monitoring_point_parameter_cache_item");

I quite like the idea of mapping to ROW using value tuples. Currently we using text::jsonb as input for some database validation queries, because we don't want to create strongly typed C# classes for each scenario we validate.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment