.NET 6升级至.NET 8后NpgsqlDataAdapter.Fill抛出System.Object读取异常

原有代码 #

using (var adapter = new NpgsqlDataAdapter(sql, context.Database.GetDbConnection() as NpgsqlConnection))
{
    var dataTable = new DataTable();
    adapter.Fill(dataTable);

    return dataTable;
}

报错信息 #

Reading as 'System.Object' is not supported for fields having DataType

详细异常信息：

at Npgsql.Internal.AdoSerializerHelpers.g__ThrowReadingNotSupported|0_0(Type type, String displayName, Exception inner)
at Npgsql.Internal.AdoSerializerHelpers.GetTypeInfoForReading(Type type, PostgresType postgresType, PgSerializerOptions options)
at Npgsql.BackendMessages.FieldDescription.g__GetInfoSlow|50_0(Type type, ColumnInfo& lastColumnInfo)
at Npgsql.BackendMessages.FieldDescription.GetInfo(Type type, ColumnInfo& lastColumnInfo)
at Npgsql.BackendMessages.FieldDescription.get_ObjectOrDefaultInfo()
at Npgsql.BackendMessages.FieldDescription.get_FieldType()
at Npgsql.NpgsqlDataReader.GetFieldType(Int32 ordinal)
at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithoutKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMappingInternal(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

解决方案 #

原因分析 #

.NET 8版本的Npgsql对类型映射的校验更严格，当查询结果包含无法直接映射到CLR标准类型的PostgreSQL类型（如自定义类型、jsonb、复杂数组等）时，DataAdapter.Fill自动推断类型时尝试以System.Object读取，触发该错误。

具体解决办法 #

1. 手动定义DataTable列类型（推荐）

提前明确DataTable的列结构和对应CLR类型，避免Npgsql自动推断为Object：

using (var adapter = new NpgsqlDataAdapter(sql, context.Database.GetDbConnection() as NpgsqlConnection))
{
    var dataTable = new DataTable();
    // 根据实际查询的列名和类型添加列
    dataTable.Columns.Add("Id", typeof(int));
    dataTable.Columns.Add("Content", typeof(string));
    dataTable.Columns.Add("CreateTime", typeof(DateTime));
    // 其他列按需添加
    adapter.Fill(dataTable);
    return dataTable;
}

2. SQL中转换特殊类型

如果查询包含PostgreSQL专属类型，在SQL语句中显式转换为可映射的类型：

SELECT 
    id,
    CAST(json_column AS text) AS json_column,
    uuid_column::text AS uuid_column,
    array_column::text[] AS array_column
FROM your_table

3. 升级Npgsql版本

确保使用适配.NET 8的最新Npgsql包（版本8.0及以上），新版本修复了部分类型映射的兼容性问题。

4. 用DataReader手动填充DataTable

绕过DataAdapter，直接通过NpgsqlDataReader读取并手动处理类型转换：

using var connection = context.Database.GetDbConnection() as NpgsqlConnection;
await connection.OpenAsync();
using var command = new NpgsqlCommand(sql, connection);
using var reader = await command.ExecuteReaderAsync();

var dataTable = new DataTable();
// 先初始化列结构
for (int i = 0; i < reader.FieldCount; i++)
{
    // 根据实际情况指定列类型，或用reader.GetFieldType(i)获取类型（若兼容）
    dataTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i) ?? typeof(string));
}

// 逐行读取填充
while (reader.Read())
{
    var row = dataTable.NewRow();
    for (int i = 0; i < reader.FieldCount; i++)
    {
        // 特殊类型可在此处手动转换
        row[i] = reader.IsDBNull(i) ? DBNull.Value : reader.GetValue(i);
    }
    dataTable.Rows.Add(row);
}
return dataTable;

内容的提问来源于stack exchange，提问作者user3097695