ASP.NET Core 3.1 Web API返回JSON格式异常问题求助

我正在使用ASP.NET Core 3.1、Oracle、Dapper和Newtonsoft Json开发Web API，通过POST提交JSON数据到数据库，期望返回特定格式的JSON响应。

请求数据 #

{"Payees": [{"EMPLOYEE_ID": "100","FIRST_NAME":"Steven","LAST_NAME": "King","HIRE_DATE": "17-JUN-03"},      {"EMPLOYEE_ID": "104","FIRST_NAME":"Bruce","LAST_NAME": "Ernst","HIRE_DATE": "21-MAY-07"}]}

期望响应格式 #

暂不关注STATUS字段，只要能返回SALARY字段即可：

{"Employees": [{"SALARY": "24000","STATUS":"SUCCESS"},      {"SALARY": "6000","STATUS":"SUCCESS"}]}

实际返回结果 #

"[{\"EMPLOYEE_ID\":null,\"FIRST_NAME\":null,\"LAST_NAME\":null,\"HIRE_DATE\":null,\"SALARY\":\"24000\"},{\"EMPLOYEE_ID\":null,\"FIRST_NAME\":null,\"LAST_NAME\":null,\"HIRE_DATE\":null,\"SALARY\":\"17000\"},{\"EMPLOYEE_ID\":null,\"FIRST_NAME\":null,\"LAST_NAME\":null,\"HIRE_DATE\":null,\"SALARY\":\"6000\"}]"

POST方法代码 #

[HttpPost]        
public async Task<object> PostAsync([FromBody] EmployeeInfo employeeInfo)        
{            
    var jsonResponse = await _payeeService.PostPayeeList(employeeInfo);            
    var newSalary = new Payee();            
    if (jsonResponse != null)            
    {                
        newSalary.SALARY = Newtonsoft.Json.JsonConvert.SerializeObject(jsonResponse);               
    }            
    return this.Ok(newSalary.SALARY);        
}

PayeeService类代码 #

public async Task<IEnumerable<Payee>> PostPayeeList(EmployeeInfo employeeInfo)        
{            
    var parameters = new DynamicParameters();            
    var json = JsonConvert.SerializeObject(employeeInfo);            
    parameters.Add("@jsonQuery", json);            
    var sql = String.Format("select e.salary " +                            "FROM EMPLOYEES e " +                            "WHERE e.EMPLOYEE_ID IN" +                            "(SELECT jt.*FROM JSON_TABLE(q'~{0}~', '$.Payees[*]' " +                            "COLUMNS(EMPLOYEE_ID Number(20) PATH '$.EMPLOYEE_ID')) AS jt)", json);            
    IEnumerable<Payee> payeeList;                   
    using (IDbConnection dbConnection = new OracleConnection(this._config.Value))            
    {                
        payeeList = await dbConnection.QueryAsync<Payee>(sql);                
        IEnumerable results = payeeList.ToList();                
        return (IEnumerable<Payee>)results;            
    }        
}

实体类代码 #

public class Payee    
{        
    public string EMPLOYEE_ID { get; set; }        
    public string FIRST_NAME { get; set; }        
    public string LAST_NAME { get; set; }        
    public string HIRE_DATE{ get; set; }           
    public string SALARY { get; set; }    
}    
public class EmployeeInfo    
{        
    public List<Payee> Payees { get; set; }     
}    
public class Salary: Payee    
{        
    public new string SALARY { get; set; }     
}   
public class SalaryInfo   
{       
    public List<Salary> Salary { get; set; }     
}

接口代码 #

public interface IPayeeService    
{        
    Task<IEnumerable<Payee>> PostPayeeList(EmployeeInfo employeeInfo);    
}

问题核心 #

1. 重复序列化：Post方法中对查询结果再次序列化，导致返回的是带转义符的JSON字符串而非JSON对象
2. SQL查询风险与数据异常：用String.Format拼接JSON存在注入风险，且子查询逻辑可能导致返回多余数据
3. 实体类冗余：继承重定义属性无必要，增加代码复杂度

修改步骤 #

1. 修正Post方法，构造正确响应结构

去掉手动序列化，直接返回强类型或匿名对象，让ASP.NET自动处理JSON序列化：

[HttpPost]        
public async Task<IActionResult> PostAsync([FromBody] EmployeeInfo employeeInfo)        
{            
    var payeeList = await _payeeService.PostPayeeList(employeeInfo);            
    var response = new 
    {
        Employees = payeeList.Select(p => new 
        {
            SALARY = p.SALARY,
            STATUS = "SUCCESS"
        })
    };            
    return Ok(response);        
}

2. 修复SQL查询，避免注入并确保数据准确

改用参数绑定方式传递JSON，同时明确子查询返回字段：

public async Task<IEnumerable<Payee>> PostPayeeList(EmployeeInfo employeeInfo)        
{            
    var sql = @"select e.salary as SALARY
                FROM EMPLOYEES e 
                WHERE e.EMPLOYEE_ID IN
                (SELECT jt.EMPLOYEE_ID FROM JSON_TABLE(:jsonQuery, '$.Payees[*]' 
                COLUMNS(EMPLOYEE_ID Number(20) PATH '$.EMPLOYEE_ID')) AS jt)";
    
    using (IDbConnection dbConnection = new OracleConnection(this._config.Value))            
    {                
        return await dbConnection.QueryAsync<Payee>(sql, new { jsonQuery = JsonConvert.SerializeObject(employeeInfo) });            
    }        
}

• 用:jsonQuery绑定参数，避免SQL注入
• 给查询字段起别名SALARY，确保Dapper能正确映射到实体属性

3. 简化实体类（可选）

删除冗余的Salary和SalaryInfo类，若需要强类型响应，可新建简洁类：

public class EmployeeSalaryResponse
{
    public string SALARY { get; set; }
    public string STATUS { get; set; } = "SUCCESS";
}

public class ApiResponse
{
    public List<EmployeeSalaryResponse> Employees { get; set; }
}

在Post方法中使用：

var response = new ApiResponse
{
    Employees = payeeList.Select(p => new EmployeeSalaryResponse { SALARY = p.SALARY }).ToList()
};
return Ok(response);

修改完成后，API将返回符合期望的JSON格式：

{"Employees": [{"SALARY": "24000","STATUS":"SUCCESS"}, {"SALARY": "6000","STATUS":"SUCCESS"}]}

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