ASP.NET Core中C#对象与SQL Server表映射求助

作为ASP.NET Core新手，我需要实现C#类对象与SQL Server数据库表的映射，最终输出指定格式的JSON：

[
    {
        "ID": 1,
        "Line1": "myaddress",
        "Line2": "address2",
        "City": "mycity",
        "State": {
                     "StateID": 1,
                     "StateName": "mystate"
                 },
        "StateID": 1,
        "ZipCode":"545588"
    }
]

我的C#类定义：

public class Address
{
    public int Id { get; set; }
    public string Line1 { get; set; }
    public string Line2 { get; set; }
    public string City { get; set; }
    public State State { get; set; }
    public string ZipCode { get; set; }
}

public class State
{
    public int StateId { get; set; }
    public string StateName { get; set; }
}

数据库表结构：

CREATE TABLE [dbo].[address]
(
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [line1] [varchar](50) NULL,
    [line2] [varchar](50) NULL,
    [city] [varchar](50) NULL,
    [stateid] [int] NULL,
    [zipcode] [varchar](20) NULL,
    -- 假设存在PayorId字段，匹配查询语句中的条件
    [PayorId] [bigint] NULL
)

CREATE TABLE [types].[state] 
(
    [stateid] [int] IDENTITY(1,1) NOT NULL,
    [statename] [text] NULL
)

当前读取数据的代码：

await using var conn_payoraddr = new SqlConnection(_connectionString.Value);
string query = "Select * from Address t1 left join types.State t2 on t1.stateid = t2.stateid where PayorId = @Id";

var result_addr = await conn_payoraddr.QueryAsync<PayorAddress>(query, new { Id = id });

请求协助实现正确的对象映射与输出格式。

1. 调整C#类定义 #

• 数据库address.id为bigint类型，需将Address.Id改为long避免类型不匹配
• 添加StateID字段匹配JSON输出要求
• 使用JsonPropertyName特性指定JSON输出的属性名称，确保与期望格式一致

using System.Text.Json.Serialization;

public class Address
{
    [JsonPropertyName("ID")]
    public long Id { get; set; }
    [JsonPropertyName("Line1")]
    public string Line1 { get; set; }
    [JsonPropertyName("Line2")]
    public string Line2 { get; set; }
    [JsonPropertyName("City")]
    public string City { get; set; }
    [JsonPropertyName("State")]
    public State State { get; set; }
    [JsonPropertyName("StateID")]
    public int StateID { get; set; }
    [JsonPropertyName("ZipCode")]
    public string ZipCode { get; set; }
}

public class State
{
    [JsonPropertyName("StateID")]
    public int StateId { get; set; }
    [JsonPropertyName("StateName")]
    public string StateName { get; set; }
}

2. 优化SQL查询语句 #

避免使用SELECT *，明确指定字段并给重复字段（如stateid）加别名，防止映射冲突：

SELECT 
    t1.id AS Id,
    t1.line1 AS Line1,
    t1.line2 AS Line2,
    t1.city AS City,
    t1.stateid AS StateID,
    t1.zipcode AS ZipCode,
    t2.stateid AS State_StateId,
    t2.statename AS State_StateName
FROM dbo.address t1 
LEFT JOIN types.State t2 ON t1.stateid = t2.stateid 
WHERE t1.PayorId = @Id

3. 使用Dapper多映射关联对象 #

通过Dapper的多映射功能，将查询结果分别映射到Address和State对象，指定splitOn参数区分两个对象的字段起始点：

await using var conn_payoraddr = new SqlConnection(_connectionString.Value);
string query = @"SELECT 
                    t1.id AS Id,
                    t1.line1 AS Line1,
                    t1.line2 AS Line2,
                    t1.city AS City,
                    t1.stateid AS StateID,
                    t1.zipcode AS ZipCode,
                    t2.stateid AS State_StateId,
                    t2.statename AS State_StateName
                FROM dbo.address t1 
                LEFT JOIN types.State t2 ON t1.stateid = t2.stateid 
                WHERE t1.PayorId = @Id";

var result_addr = await conn_payoraddr.QueryAsync<Address, State, Address>(
    query,
    (address, state) => {
        address.State = state;
        return address;
    },
    param: new { Id = id },
    splitOn: "State_StateId"
);

4. 验证JSON输出 #

将result_addr返回给前端时，ASP.NET Core的JSON序列化器会根据JsonPropertyName特性生成符合要求的JSON格式，与你期望的输出完全一致。

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