使用group by时npgsql数据类型未知 [英] npgsql data type unknown when using group by

问题描述

我有2个表：

CREATE TABLE "book" 
(
  "id" serial PRIMARY KEY, 
  "ean_number" TEXT NULL, 
  "title" TEXT NULL 
); 

CREATE TABLE "e_book" 
(
  "id" serial PRIMARY KEY, 
  "ean" TEXT NULL, 
  "title" TEXT NULL, 
  "format" VARCHAR(255) NOT NULL, 
  "physical_book_ean" TEXT NULL 
); 

从书本到电子书之间存在一对多或没有关系。

There is a one to many or none relationship from book to e_book.

我想在我的代码中运行这样的查询：

I want to run a query like this in my code:

var q = "select b.*, array_agg(e) ebooks from book b " +
         "left join e_book e on e.physical_book_ean = b.ean_number " +
         "group by b.id";

using (var cmd = new NpgsqlCommand(q, conn))
using (var reader = cmd.ExecuteReader())
    while (reader.Read())
    {
        //read data
    }

array_agg列电子书来了< unknown>

The array_agg column ebook comes up as content type <unknown>

如何定义内容类型以便可以读取？ / p>

How do I define the content type so I can read it?

推荐答案

这是作为github问题打开的： https://github.com/npgsql/npgsql/issues/2510

This was opened as a github issue: https://github.com/npgsql/npgsql/issues/2510

答案如此处给出：

首先，如果创建电子书表并在同一过程中对其进行查询，则需要告诉Npgsql重新加载数据库类型定义。这是因为当Npgsql首次连接到数据库时，它会加载类型列表并对其进行缓存-但那时e_book类型尚不存在。如果再次使用已经存在的表运行应用程序，则不再有此问题，或者可以调用Npgsql.ReloadTypes（）。

First, if you create the e_book table and query it in the same process, you need to tell Npgsql to reload database type definitions. This is because when Npgsql first connects to a database, it loads the list of types and caches it - but at that point the e_book type doesn't exist yet. If you run your application again with the tables already there you should no longer have this issue, or you can call Npgsql.ReloadTypes().

第二，您需要在连接字符串上传递LoadTableComposites = true标志，告诉Npgsql加载所有复合类型-包括与表相对应的复合类型。默认情况下，Npgsql不会执行此操作，因为表的数量可能很大，并且在某些情况下会影响启动性能。

Second, you will need to pass the LoadTableComposites=true flag on the connection string, telling Npgsql to load all composite types - including those which correspond to tables. Npgsql doesn't do that by default since the number of tables can be massive and affect startup performance in some cases.

这篇关于使用group by时npgsql数据类型未知的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！