I am new at C# entity framework. I am trying to build an API, but stuck in retrieving data from relational table. I have a Game table in MS SQL database, where GameId is the primary key. I have another table called GameCharacter, where GameCharacterId is the primary key. In Game table GameCharacterId is foreign key. How i can get all GameCharacters on Get Games.

 public class Game
    {
        public Game()
        {
            GameCharacters = new List<GameCharacter>();
        }
        public int GameID { get; set; }
        public string Title { get; set; }
        public string Platform { get; set; }
        public string imgpath { get; set; }
        public int ReleaseYear { get; set; }
        public virtual ICollection< GameCharacter> GameCharacters { get; set; }
    }

 public class GameCharacter
    {
        [Key]
        public Guid CharID { get; set; }
        public string CharName { get; set; }
        public string CharGame { get; set; }
        public string charimgpath { get; set; }

        [ForeignKey("Game")]
        public int GameID { get; set; }
        public virtual Game Game { get; set; }

    }

 public class GameController : Controller
    {
        private readonly GameApiDbconnect dbContext;

        public GameController(GameApiDbconnect dbContext)
        {
            this.dbContext = dbContext;
        }
        [HttpGet]
        public async Task<IActionResult> GetGames()
        {
            return Ok(await dbContext.Games.ToListAsync());
        }

        [HttpGet]
        [Route("{GameID=guid}")]
        public async Task<IActionResult> GetGame([FromRoute] Guid GameID)
        {
            var game = await dbContext.Games.FindAsync(GameID);
            if (game == null)
            {
                return NotFound();
            }
                return Ok(game);
        }

OutPut Response body

{ "gameID": 1, "title": "string", "platform": "string", "imgpath": "string", "releaseYear": 0, "gameCharacters": [] }

CodePudding user response：

    [HttpGet]
    public async Task<IActionResult> GetGames()
    {
        return Ok(from g in dbContext.Games
                        join c in dbContext.GameCharacters on g.GameID 
    equals c.GameID into Gcharacters
                        select new
                        {
                            GameID = g.GameID,
                            Title = g.Title,
                            Platform = g.Platform,
                            imgpath = g.imgpath,
                            ReleaseYear = g.ReleaseYear,

                            GameCharacters = Gcharacters.Select(gc => new { 
    CharID = gc.CharID, CharName = gc.CharName, CharGame = 
           gc.CharGame, charimgpath = gc.charimgpath }) }
        ) ;
     }
   

CodePudding user response：

just use include

        [HttpGet]
        public async Task<IActionResult> GetGames()
        {
           var games = await dbContext.Games
                                       .Include( g=> g.GameCharacters)
                                        .ToListAsync();
            return Ok(games);
        }