Dapper.Contrib InsertAsync插入HIERARCHYID主键报错：无法插入空值到Id字段

I’ve helped several developers troubleshoot this exact issue with Dapper and SqlHierarchyId—even when you can see the Id property is populated in debug, Dapper often struggles to map this custom SQL type correctly by default. Here’s how to fix it step by step:

1. Register a Custom Type Handler for SqlHierarchyId #

Dapper doesn’t have built-in support for SqlHierarchyId out of the box, so you need to tell it how to convert between the .NET type and the database’s hierarchyid type. Create a type handler class:

public class SqlHierarchyIdTypeHandler : SqlMapper.TypeHandler<SqlHierarchyId>
{
    public override void SetValue(IDbDataParameter parameter, SqlHierarchyId value)
    {
        parameter.Value = value.IsNull ? DBNull.Value : value;
        parameter.SqlDbType = SqlDbType.Udt;
        parameter.UdtTypeName = "hierarchyid"; // Critical: matches the SQL type name
    }

    public override SqlHierarchyId Parse(object value)
    {
        if (value == DBNull.Value || value == null)
            return SqlHierarchyId.Null;
        
        return SqlHierarchyId.Parse(value.ToString());
    }
}

Then register this handler when your application starts (e.g., in Program.cs or your startup class):

SqlMapper.AddTypeHandler(new SqlHierarchyIdTypeHandler());

This tells Dapper exactly how to serialize and deserialize SqlHierarchyId values when interacting with the database.

2. Check Your Entity’s Property Setter #

While you’re using [ExplicitKey] to mark the Id as a non-identity primary key, Dapper might have trouble accessing a private set property during parameter mapping. Try changing the setter to public (or internal if you’re using internal visibility) to rule this out:

[Table("OurTable")]
public class OurTable 
{ 
    [ExplicitKey] 
    public SqlHierarchyId Id { get; set; } // Changed from private set to public
    // Other fields
}

If you need to keep the setter private for encapsulation, you can use a constructor to initialize the Id instead—just make sure Dapper can still read the property’s value via the getter.

3. Test with an Anonymous Type (Debugging Step) #

To confirm the issue is with entity mapping rather than your SqlHierarchyId value, try inserting using an anonymous type instead of the OurTable entity:

var insertParams = new 
{
    Id = yourOurTableInstance.Id,
    // Map all other fields here
};

await connection.InsertAsync(insertParams, "OurTable");

If this works, it confirms the problem is with how Dapper is mapping your entity class—double-check the [Table] and [ExplicitKey] attributes are correctly applied, and that the type handler is registered before this code runs.

4. Verify Dapper Version #

Older versions of Dapper had limited support for custom SQL types. Make sure you’re using the latest stable version of Dapper via NuGet—this can resolve subtle mapping bugs that might be causing the null value issue.

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