ReadAsync<string> throws InvalidCastException when column value is DBNull

[seesharper]

Description

When using ReadAsync<string> to read a single scalar string column that contains a NULL value, DbReader throws an InvalidCastException:

System.InvalidCastException : Unable to cast object of type 'System.DBNull' to type 'System.String'.

However, when the same nullable string column is mapped into a property on a record or class (e.g. ReadAsync<MyRecord> where MyRecord has a string property), DbReader handles DBNull correctly by setting the property to null.

Steps to reproduce

// Throws InvalidCastException if the column value is NULL
var result = (await dbConnection.ReadAsync<string>("SELECT NullableTextColumn FROM MyTable WHERE Id = @Id", new { Id = 1 })).SingleOrDefault();

// Works fine — DbReader sets the property to null
var result = (await dbConnection.ReadAsync<MyRecord>("SELECT NullableTextColumn FROM MyTable WHERE Id = @Id", new { Id = 1 })).SingleOrDefault();

public record MyRecord(string NullableTextColumn);

Expected behaviour

ReadAsync<string> should return null when the column value is DBNull, consistent with how DbReader handles DBNull when mapping into a record/class property.

Workaround

Use COALESCE in the SQL to avoid returning NULL:

SELECT COALESCE(NullableTextColumn, '') AS NullableTextColumn FROM MyTable WHERE Id = @Id