DapperLib / DapperLib/Dapper

Fields of postgres composite type are null when queried directly

Open
#1,684 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C#
Stars
18.4k
Forks
3.7k
Avg merge
5h 8m
Merged PRs (30d)
1

Description

Hello,
I am not sure if this is the intended behaviour or if I am doing something wrong.

I work with PostgreSQL composite types and I am trying to select one of those types as the single column of my query.
When I have a composite type as part of my query result and the class I am mapping to has a property that represents that type, everything works fine. However, when I directly want to query that type, I get rows, but the fields are null.

Classes:

public class MyCompositeType
{
    public string Field1 { get; set; }
    public string Field2 { get; set; }

    public override string ToString()
    {
        return $"Field1: {Field1}, Field2: {Field2}";
    }
}

public class MyClassWithCompositeType
{
    public int Id { get; set; }
    public MyCompositeType CompositeField { get; set; }
}

The following works as expected
IEnumerable<MyClassWithCompositeType> result = connection.Query<MyClassWithCompositeType>("select id, compositefield from mytablewithcompositetype;");
I can now access the property CompositeField in the result and will get the values that my composite type contains.
For example: result.First().CompositeField.ToString() would be "Field1: foo, Field2: bar"

However, if I only want to query for 'compositefield' as a single column query, I get empty fields (as in they are null), but result itself contains the excepected number of rows.
IEnumerable<MyCompositeType> result = connection.Query<MyCompositeType>("select compositefield from mytablewithcompositetype;");
For example: result.First().ToString() would be "Field1: , Field2: (note, that I don't get an exception on First(), I do receive rows)

Am I doing something wrong or expecting something different than is intended, or is this a bug?
Note: my composite type field is no key or anything, I saw that this apparently can cause issues.

Thank you!

Database:
select version();
PostgreSQL 13.1 (Debian 13.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Minimal repro:
Database
create database npgsqltypemapper;

create type mycompositetype as (
	field1 character varying(20),
	field2 character varying(20)
);

create table mytablewithcompositetype (
	id int not null,
	compositefield mycompositetype not null
);

insert into mytablewithcompositetype (id, compositefield)
values (1, ('foo', 'bar')::mycompositetype), (2, ('foo', 'baz')::mycompositetype);
C# .NET 5, Dapper v2.0.90 and Npgsql v5.0.7
using Dapper;
using Npgsql;
using System;
using System.Collections.Generic;

namespace NpgsqlTypeMapperRepro
{
    public class Program
    {
        public class MyCompositeType
        {
            public string Field1 { get; set; }
            public string Field2 { get; set; }

            public override string ToString()
            {
                return $"Field1: {Field1}, Field2: {Field2}";
            }
        }

        public class MyClassWithCompositeType
        {
            public int Id { get; set; }
            public MyCompositeType CompositeField { get; set; }
        }

        static void Main(string[] args)
        {
            var connectionString = "Host=localhost;Username=*****;Password=*****;Database=npgsqltypemapper";

            // 1. without dapper
            using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
            {
                connection.Open();

                connection.TypeMapper.MapComposite<MyCompositeType>("mycompositetype");

                using var cmd = new NpgsqlCommand("select compositefield from mytablewithcompositetype;", connection);
                using var reader = cmd.ExecuteReader();

                List<MyCompositeType> result = new List<MyCompositeType>();

                while (reader.Read())
                {
                    result.Add((MyCompositeType)reader.GetValue(0));
                }

                Console.WriteLine("Result without dapper");
                Console.WriteLine("---------------------");
                foreach (var r in result)
                {
                    Console.WriteLine(r);
                }
                Console.WriteLine("---------------------");
                Console.WriteLine(Environment.NewLine);
            }

            // 2. with dapper (select composite type field as property of an object)
            using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
            {
                connection.Open();

                connection.TypeMapper.MapComposite<MyCompositeType>("mycompositetype");

                IEnumerable<MyClassWithCompositeType> result = connection.Query<MyClassWithCompositeType>("select id, compositefield from mytablewithcompositetype;");

                Console.WriteLine("Result with dapper (class with composite type property)");
                Console.WriteLine("---------------------");
                foreach (var r in result)
                {
                    Console.WriteLine(r.CompositeField);
                }
                Console.WriteLine("---------------------");
                Console.WriteLine(Environment.NewLine);
            }

            // 3. with dapper (only select raw composite type field)
            using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
            {
                connection.Open();

                connection.TypeMapper.MapComposite<MyCompositeType>("mycompositetype");

                IEnumerable<MyCompositeType> result = connection.Query<MyCompositeType>("select compositefield from mytablewithcompositetype;");

                Console.WriteLine("Result with dapper (raw composite type field)");
                Console.WriteLine("---------------------");
                foreach (var r in result)
                {
                    Console.WriteLine(r);
                }
                Console.WriteLine("---------------------");
            }
        }
    }
}
Output
Result without dapper
---------------------
Field1: foo, Field2: bar
Field1: foo, Field2: baz
---------------------


Result with dapper (class with composite type property)
---------------------
Field1: foo, Field2: bar
Field1: foo, Field2: baz
---------------------


Result with dapper (raw composite type field)
---------------------
Field1: , Field2:
Field1: , Field2:
---------------------

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the minimal repro and compare the direct Query("select compositefield...") path with the working nested-property query and the direct Npgsql reader result. Done means direct Dapper queries populate Field1 and Field2 for each composite value while preserving the existing nested mapping behavior.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, postgresql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.