在C#中如何将数据库列转换为List<string>映射到模型属性

问题场景 #

数据库表unit_properties的images列存储的是["a","b","c","d"]格式的JSON字符串。原本C#模型中UnitImages为string类型时，Dapper查询映射完全正常；但将该属性改为List<string>?类型后，无法自动完成映射，期望返回的JSON结构中unitImages是字符串数组形式：

[
  {
    "unitId": 1,
    "unitTitle": "1 Bedroom Studio",
    "unitDescription": "Fully renovated and move in ready apartments are available for rent...",
    "unitImages": [
        "a",
        "b",
        "c",
        "d"
       ],
    "unitAddress": "6876 86 Street Edmonton",
    "unitRooms": 1
  }
]

修改后的C#模型：

public class UnitProperty
{
    public long? UnitId { get; set; }
    public string? UnitTitle { get; set; }
    public string? UnitDescription { get; set; }
    public List<string>? UnitImages { get; set; } // 修改为List<string>类型
    public string? UnitAddress { get; set; }
    public int? UnitRooms { get; set; }
    public int? UnitBathrooms { get; set; }
    public int? UnitSquareMeters { get; set; }
    public bool? UnitHasParking { get; set; }
    public decimal? UnitPrice { get; set; }
    public int? UnitWalkScore { get; set; }
    public int? UnitTransitScore { get; set; }
    public int? UnitBikeScore { get; set; }
    public User? Agent { get; set; }
    public Category? Category { get; set; }
}

原控制器查询代码（原UnitImages为string时正常运行）：

// GET: api/<UnitPropertyController>
[HttpGet("Units", Name = "GetUnits")]
public async Task<List<UnitProperty>> GetUnitsAsync([FromServices] MySqlConnection connection)
{
    string query = @"
    SELECT
        up.id as unitId,
        up.title as unitTitle,
        up.description as unitDescription,
        up.images as unitImages,
        up.address as unitAddress,
        up.rooms as unitRooms,
        up.bathrooms as unitBathrooms,
        up.square_meters as unitSquareMeters,
        up.has_parking as unitHasParking,
        up.price as unitPrice,
        up.walk_score as unitWalkScore,
        up.transit_score as unitTransitScore,
        up.bike_score as unitBikeScore,
        up.agent_id,
        up.category_id,

        c.id as categoryId,
        c.name as categoryName,
        c.image as categoryImage,

        u.id as userId,
        u.name as userName,
        u.image as userImage,
        u.email as userEmail,
        u.phone_number as userPhoneNumber,
        u.address as userAddress,
        u.description as userDescription,
        u.user_role as userRole

    FROM unit_properties up
    INNER JOIN categories c ON up.category_id = c.id
    INNER JOIN users u ON up.agent_id = u.id;
";

var multiQuery = await connection.QueryMultipleAsync(query);
var unitProperties = multiQuery.Read<UnitProperty, Category, User, UnitProperty>(
    (unitProperty, category, user) =>
    {
        unitProperty.Category = category;
        unitProperty.Agent = user;

        return unitProperty;
    },
    splitOn: "categoryId,userId"
);

List<UnitProperty> results = unitProperties.ToList();

return results;
}

解决方案 #

方案1：查询后手动反序列化

无需修改Dapper配置，在获取查询结果后，遍历每个对象完成JSON字符串到列表的转换：

List<UnitProperty> results = unitProperties.ToList();

foreach (var property in results)
{
    var jsonStr = property.UnitImages as string;
    if (!string.IsNullOrEmpty(jsonStr))
    {
        // 用System.Text.Json反序列化，若用Newtonsoft.Json则替换为JsonConvert.DeserializeObject
        property.UnitImages = System.Text.Json.JsonSerializer.Deserialize<List<string>>(jsonStr);
    }
}

return results;

方案2：注册Dapper自定义类型处理器

通过自定义类型处理器，让Dapper自动完成JSON字符串与List<string>的双向映射，一劳永逸：

1. 定义类型处理器：

using Dapper;
using System.Text.Json;

public class StringListTypeHandler : SqlMapper.TypeHandler<List<string>>
{
    public override List<string> Parse(object value)
    {
        if (value == null || string.IsNullOrWhiteSpace(value.ToString()))
            return null;
            
        return JsonSerializer.Deserialize<List<string>>(value.ToString());
    }

    public override void SetValue(System.Data.IDbDataParameter parameter, List<string> value)
    {
        parameter.Value = value == null ? null : JsonSerializer.Serialize(value);
    }
}

2. 在项目启动时注册处理器（比如Program.cs）：

SqlMapper.AddTypeHandler(new StringListTypeHandler());

3. 保持修改后的UnitProperty模型不变，原控制器代码无需任何改动，Dapper会自动完成映射。

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