Returning object as JSON data using Web API C#

Question

I want to generate JSON data from a database table using Web API C#. The following is the table structure

CREATE TABLE [dbo].[Fields](
    [fieldID] [varchar](250) NOT NULL,
    [displayName] [varchar](500) NOT NULL,
    [id] [bigint] NOT NULL,
    [tenant] [bigint] NOT NULL,
    [Name] [varchar](500) NOT NULL,
    [description] [varchar](500) NULL,
    [type] [varchar](250) NULL
) ON [PRIMARY]
GO

And having following data

INSERT [dbo].[Fields] ([fieldID], [displayName], [id], [tenant], [Name], [description], [type]) VALUES (N'100', N'Loan#', 18, 3, N'Loan#', N'Loan#', N'string')
GO
INSERT [dbo].[Fields] ([fieldID], [displayName], [id], [tenant], [Name], [description], [type]) VALUES (N'101', N'LoanProgram', 19, 3, N'LoanProgram', N'LoanProgram', N'string')
GO

From this table and I want to generate a JSON in the following format using the Web API

{
  "100": {
        "fieldID": "100",
        "displayName": "Loan#",
        "id": 18,
        "tenant": 3,
        "name": "Loan#",
        "description": "Loan#",
        "type": "string"        
    },
    "101": {
        "fieldID": "101",
        "displayName": "LoanProgram",
        "id": 19,
        "tenant": 3,
        "name": "LoanProgram",
        "description": "LoanProgram",
        "type": "string"        
    }
}

Following is my API controller

[HttpGet("Fields/{id}/fields/")]
public Object GetFields(int id)
{
   return _fieldService.GetFields(id).Result;
}

I have created a class as follows

public class ConfiguredFields
{
    public int fieldID { get; set; }
    public string displayName { get; set; }
    public int id { get; set; }
    public string tenant { get; set; }
    public string name { get; set; }
    public string description { get; set; }
    public string type { get; set; }        
}

And using Dapper I have called the SP and tried to the value

public async Task<Object> GetWorkflowFields(int WID)
{
    using (var db = new SqlConnection(_connectionString.Value))
    {
        var parameters = new DynamicParameters();
        parameters.Add("@pm_ID", WID);
        var result = await db.QueryAsync<ConfiguredFields>("SP_GetLoanFields", parameters, commandType: CommandType.StoredProcedure);

        
        return result.ToList();

    }
}

But I am getting the JSON in the following format (with array lap and not in the desired format where fieldID wise wrapping is not there.)

[
    {
        "fieldID": 100,
        "displayName": "Loan#",
        "id": 18,
        "tenant": "3",
        "name": "Loan#",
        "description": "Loan#",
        "type": "string"        
    },
    {
        "fieldID": 101,
        "displayName": "LoanProgram",
        "id": 19,
        "tenant": "3",
        "name": "LoanProgram",
        "description": "LoanProgram",
        "type": "string"   
    }
    
]

Please suggest what all changes required here to get the JSON in desired format? Please help me to solve this?

Answer 1

1. Change the return type of your GetWorkflowFields method from Task<Object> to Task<List<ConfiguredFields>>:

    public async Task<List<ConfiguredFields>> GetWorkflowFields(int WID)
    {
        ...
    }
   

2. Change your model properties to have types that match the data in your database (based on the table definition you gave in your question).

   Change fieldID from int to string.
   Change id from int to long.
   Change tenant from string to long.

   public class ConfiguredFields
   {
       public string fieldID { get; set; }
       public string displayName { get; set; }
       public long id { get; set; }
       public long tenant { get; set; }
       public string name { get; set; }
       public string description { get; set; }
       public string type { get; set; }
   }
   

3. Change your GetFields controller method to convert the result from GetWorkflowFields to a dictionary:

    return _fieldService.GetWorkflowFields(id).Result.ToDictionary(f => f.fieldID);
   

4. (Optional, but recommended) Change the return type of the GetFields controller method to Dictionary<string, ConfiguredFields>. Better yet, make the method async:

    [HttpGet("Fields/{id}/fields/")]
    public async Task<Dictionary<string, ConfiguredFields>> GetFields(int id)
    {
        var fields = await _fieldService.GetWorkflowFields(id);
        return fields.ToDictionary(f => f.fieldID);        
    }