Web API Returning Nested JSON Values

Question

I am working with a JSON structure being returned by a web api, but but have a problem.

Say, I have two tables, Teams and Players. They are joined by TeamID (PK on Teams and FK on Players).

I want my API call to return some JSON format similar as below:

[
    {
        TeamId: 1,
        TeamName: 'Chicago Bulls',
        TeamPlayers: [
            {PlayerId: 1, PlayerName: 'Pau Gasol'},
            {PlayerId: 2, PlayerName: 'Derrick Rose'},
            {PlayerId: 3, PlayerName: 'Joakim Noah'},
            {PlayerId: 4, PlayerName: 'Jimmy Butler'},
            {PlayerId: 5, PlayerName: 'Taj Gibson'}]
    },
    {
        TeamId: 2,
        TeamName: 'Cleveland Cavaliers',
        TeamPlayers: [
            {PlayerId: 1, PlayerName: 'Lebron James'},
            {PlayerId: 2, PlayerName: 'Kyrie Irving'},
            {PlayerId: 3, PlayerName: 'Anderson Varejao'},
            {PlayerId: 4, PlayerName: 'Dion Waiters'},
            {PlayerId: 5, PlayerName: 'Shawn Marion'}]
    },
    {
        TeamId: 3,
        TeamName: 'Los Angeles Clippers',
        TeamPlayers: [
            {PlayerId: 1, PlayerName: 'Chris Paul'},
            {PlayerId: 2, PlayerName: 'Blake Griffin'},
            {PlayerId: 3, PlayerName: 'DeAndre Jordan'},
            {PlayerId: 4, PlayerName: 'Jamal Crawford'},
            {PlayerId: 5, PlayerName: 'Matt Barnes'}]
    }
]

Controller:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using System.Web.Http.Description;
using MyApp.Models;

namespace MyApp.Controllers
{
    public class TeamsController : ApiController
    {
        private DataModel db = new DataModel();

        // GET: api/teams
        public IQueryable<TeamsWithPlayers> GetTeamsAndPlayers()
        {
            var query = from x in db.Teams
                        join y in db.Players on x.TeamId equals y.TeamId
                        select
                        {
                            // This is where I need some help...
                        }
        }
    }
}

TeamAndPlayer Class:

namespace MyApp.Models
{
    public class TeamAndPlayers
    {
        public int TeamId { get; set; }
        public string TeamName { get; set; }
        public Players players { get; set; }
    }
}

Players Class:

namespace MyApp.Models
{
    public class Players 
    {
        public int TeamId { get; set; }
        public int PlayerId { get; set; }
        public string PlayerName { get; set; }
    }
}

Can someone provide some insight?

Your JSON structure doesn't match your models ! in the JSON you return an array of TeamPlayers, while your TeamAndPlayers has a single Player but named Players. — Omar.Alani
– Omar.Alani, Commented Dec 7, 2014 at 10:39

Omar.Alani · Accepted Answer · 2014-12-07 12:01:42Z

14

I will assume that the JSON structure is the source of truth here not the models (TeamWithPlayers/Players) - see my comments.
My solution assumes that you are using Entity Framework to retrieve the data from the database because I am using "Include" method, but can replace it to use "Join".

1- Define TeamDto and PlayerDto classes to be like :

public class TeamDto
{
    public int TeamId { get; set; }
    public string TeamName { get; set; }
    public IEnumerable<PlayerDto> TeamPlayers { get; set; }
}

public class PlayerDto
{
    public int PlayerId { get; set; }
    public string PlayerName { get; set; }
}

2- Your TeamsController will be like this:

public class TeamsController : ApiController
{
    private readonly TeamDbContext _dbContext = new TeamDbContext();

    // GET api/teams
    public IEnumerable<TeamDto> GetTeamsAndPlayers()
    {
        var teams = _dbContext
            .Teams
            .Include("Players") // Load the players associated with each Team, (this depends on your declaration, but you mentioned that there is a FK from Player => TeamId)
            // you can use the join if you like or if you don't use entity framework where you cannot call Include, but the following code will stay the same 
            .Select(t => new TeamDto
            {
                TeamId = t.TeamId,
                TeamName = t.TeamName,
                TeamPlayers = t.Players.Select(p => new PlayerDto
                    {
                        PlayerId = p.PlayerId, 
                        PlayerName = p.PlayerName
                    })
            }).ToList();

        return teams;
    }
}

Hope that helps.

edited Dec 7, 2014 at 12:01

answered Dec 7, 2014 at 11:47

Omar.Alani

4,1302 gold badges23 silver badges33 bronze badges

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

2 Comments

James Burke Over a year ago

Can you please expand your answer to show how it would look if you didn't have a FK relationship. Thanks

VahidHoseini Over a year ago

My frieand said that you were really great. thank you.

Ali Adravi · Accepted Answer · 2014-12-05 16:39:06Z

0

   var query = from x in db.Teams
    join y in db.Players on x.TeamId equals y.TeamId
    select new TeamsWithPlayers
    {
      TeamId = x.Id,
      TeamName= x.TeamName,
      Players = y
    }.ToList();

answered Dec 5, 2014 at 16:39

Ali Adravi

23k9 gold badges93 silver badges97 bronze badges

1 Comment

Omar.Alani Over a year ago

Your query will not return the required structure, pay attention to the model declaration and your query, you will notice that your query returns TeamId in the TeamsPlayer, and Players is not a collection.

ogg130 · Accepted Answer · 2018-04-24 19:53:19Z

Love the suggested answer, VERY HELPFUL!

I used the suggested answer to make a similar solution for msyelf for learning and then tried to fit the above solution code into a getbyid method.

I had a couple of struggles but came up with the following working solution, in case anyone ever stumbles across this post like I did and wants to know how to use the above solution with a getbyid method.

Here is the default WebAPI getbyid method that VS created:

    // GET: api/teams/5
    [ResponseType(typeof(teams))]
    public IHttpActionResult Getteams(long id)
    {
        teams teams = db.teams.Find(id);
        if (teams == null)
        {
            return NotFound();
        }

       return Ok(teams);
    }

Here is the updated Getbyid method using the structure used for the answer in this post:

    // GET: api/teams/5
    [ResponseType(typeof(teams))]
    public IHttpActionResult Getteams(long id)
    {
            var teams = _dbContext
            .Teams
            .Where(t => t.TeamId == id)
            .Include("Players")
            .Select(t => new TeamDto
            {
                TeamId = t.TeamId,
                TeamName = t.TeamName,
                TeamPlayers = t.Players.Select(p => new PlayerDto
                    {
                        PlayerId = p.PlayerId, 
                        PlayerName = p.PlayerName
                    })
            })
        });

        if (request == null)
        {
            return NotFound();
        }

        return Ok(requests);
    }

Collectives™ on Stack Overflow

Web API Returning Nested JSON Values

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related