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?

convert your queryable to dictionary(not to list) with fieldID as key — Selvin
– Selvin, Commented Aug 3, 2020 at 10:07
No, as there are already examples here ... also check Enumerable.ToDictionary official documentation ... there should be an example — Selvin
– Selvin, Commented Aug 3, 2020 at 10:09
On the result, instead of ToList(), you can call ToDictionary(). As an argument you will pass a lambda that selects the key (fieldID in your case). The resulting dictionary should map nicely to the json you presented. learn.microsoft.com/en-us/dotnet/api/… — user44
– user44, Commented Aug 3, 2020 at 18:46

Brian Rogers · Accepted Answer · 2020-08-04 14:29:56Z

2

Change the return type of your GetWorkflowFields method from Task<Object> to Task<List<ConfiguredFields>>:
```
 public async Task<List<ConfiguredFields>> GetWorkflowFields(int WID)
 {
     ...
 }
```

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

Change your GetFields controller method to convert the result from GetWorkflowFields to a dictionary:
```
 return _fieldService.GetWorkflowFields(id).Result.ToDictionary(f => f.fieldID);
```

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

edited Aug 4, 2020 at 14:29

answered Aug 3, 2020 at 17:59

Brian Rogers

130k31 gold badges315 silver badges315 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

A_developer Over a year ago

what should br the return type of the api controller? dictionary or Object?

AcAnanth Over a year ago

@athuman : you can use either object or public Dictionary<string,ConfiguredFields> GetWorkflowFields(int id)

Brian Rogers Over a year ago

It should work either way, but it is better to be explicit, so I would recommend using Dictionary<string, ConfiguredFields>. object doesn't tell you anything about what the method produces. Also, it would be better to make the method async since it calls an async method. I've updated my answer to show this.

Collectives™ on Stack Overflow

Returning object as JSON data using Web API C#

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related