When I run an Npgsql query using Dapper, why do I get "The field 'XYZ' has a type currently unknown to Npgsql"

Question

I've created the following SQL view in my PostgreSql database (have simplified for brevity):

create view "GlossaryView" as 
select 
    (
        select cast(
            array_agg(
                cast(quals as "Qualification")
            ) as "Qualification"[]
        ) as "Qualifications" 
        from 
        (
            select * from "Qualification"
        ) as quals
    );

Qualification is a table in my database. Running a query against this view locally using psql works fine.

However, if I run the following query using Dapper:

connection.QueryAsync<dynamic>("select * from \"GlossaryView\" ");

I get the exception in the title, with XYZ replaced with Qualifications.

I came across this answer, which suggests using LoadTableComposites=true in the connection string:

https://stackoverflow.com/a/56850677

However, when I include this in my connection string in my appsettings.json file, I get this exception during application startup:

Keyword 'LoadTableComposites ' not supported

Question: How do I get the query working in Dapper? I suspect it's not really Dapper but Npgsql.

Npgsql version: 3.2.6

Edit 1: Here's the Qualification table structure:

          Table "public.Qualification"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 Id     | uuid |           | not null | 
 Name   | text |           |          | 
Indexes:
    "PK_Qualification" PRIMARY KEY, btree ("Id")
Referenced by:
    TABLE ""User"" CONSTRAINT "FK_User_Qualification_QualificationId" 
        FOREIGN KEY ("QualificationId") REFERENCES "Qualification"("Id") ON DELETE CASCADE

Answer 1

Using Npgsql 4.0.10 and Dapper 2.0.4 the code snippet below should give you what you are requesting.

Notice I added the Load Composite Types into connection string as suggested in the comments and used Npgsql's typemapper to map the CLR type to the PostgreSQL type. You need to instantiate the connection as NpgsqlConnection instead of IDbConnection (normally suggested by Dapper).

When i created the testing objects for my snippet I didn't use quoted identifiers. You will need to adjust the snippet to compensate for the quoted identifiers in your implementation.

Information on Mapping CLR types can be found here

   public class QualificationType
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection("Host=localhost;Database=postgres;username=postgres;password=!@#$%^&;Load Table Composites= true"))
            {
                conn.Open();
                conn.TypeMapper.MapComposite<QualificationType>("qualification");

                var results = conn.Query("Select * from glossaryview").AsList();

                var o = results[0].qualifications;

                foreach ( var t in o)
                {
                    Console.WriteLine($"id: {t.Id}, name: {t.Name}");
                }


            }
        }
    }

The results to that snippet returned the following;

id: d3c47de5-ab49-498e-be24-b77f8bb587fd, name: abc
id: 4089576a-d0a1-4570-b601-f54f98784ab2, name: def
id: 123e0722-9ade-48c7-937c-aa21214bee57, name: ghi