Fields of postgres composite type are null when queried directly
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
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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