I am using ASP.NET Core 3.1, Oracle, Dapper, and NewtonSoft Json and posting the JSON to a database to get different parameters back in a JSON response. My POST looks like the below.

{
    "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"
        }
    ]
}

I am expecting a response like the one below. I'm not too worried about the Status field, as I have to work with customizing that based on a few factors. Right now, I would be happy just getting the Salary field back.

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

Instead of that, I am getting back this...

"[{\"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\"}]"

Here is my POST method

 [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);
        }

Here is the part of the PayeeService class

 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;
            }
        }

Here is the Payee class

 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; }        
    }

Interface

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

I'm sure there's a better way to do this, but I can't figure it out. I've also seen posts that say I should be using System.Text.JSON, instead of Newtonsoft. I can probably figure out how to use that later, but right now I just want to get back the correctly formatted JSON that only shows salary.

CodePudding user response：

the one way is to use Newtonsoft.Json settings

 newSalary.SALARY = JsonConvert.SerializeObject(jsonResponse, Formatting.None, new JsonSerializerSettings
{
    NullValueHandling = NullValueHandling.Ignore
});

if you are still not happy, since some values can be not nullable, then other way could be

 newSalary.SALARY = Newtonsoft.Json.JsonConvert.SerializeObject(
     jsonResponse.Select(r => new { SALARY = r.SALARY, STATUS = r.STATUS } ));

PS Don't move to Text.Json, IMHO you will have only problems after this