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?

Answer 1

• 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.