I want to get item from a foreign key inside a foreign key (if that makes any sense...)

Question

I'm using ASP.NET Core Identity. The user ID will be as FK in the Invite model and I'm trying to display all the users that are in the invite with the desired information.

I want to display the GameName within the GameID that is assigned to the user.

So it would be something like in invite show GameName (FK in user) GameTag (user) instead of GameID with a number.

Model classes:

public class Invite
{
    public int ID { get; set; }
    [ForeignKey("UserId")]    // ICollection<Invite> in User 
    [Display(Name = "Users")]
    public virtual ApplicationUser User { get; set; }
}

public class ApplicationUser : IdentityUser
{
    public string Description { get; set; }
    [ForeignKey("GameID")] 
    public int? GameID { get; set; }
    public string GameTag { get; set; }
    public virtual ICollection<Invite> Invite { get; set; }
}

public class Game
{
    public int ID { get; set; }

    [Display(Name = "Game")]
    public string GameName { get; set; }
    public virtual ICollection<ApplicationUser> ApplicationUser { get; set; }//Allow Users to get Games FKID (Foreign key ID)
}

Getting the list of invites in the invite controller index and putting them inside viewbag for invite razor index page. it only shows GameID which is the FK inside User and I don't know how to get the information inside the Game FK from Invite that is assigned to user as FK

// GET: Invites
public async Task<IActionResult> Index()
{
    ViewBag.InviteList = new List<String>();
    var invite = _context.Invites;
    var theuser = _context.ApplicationUser;

    foreach (Invite i in invite)
    {
         foreach (ApplicationUser tu in theuser)
         {
             if (i.User.Id == tu.Id)
             {
                 ViewBag.InviteList.Add(tu.GameID + " " +tu.GameTag);
             }
         }
    }

    return View(await _context.Invites.ToListAsync());
}

If anyone understands what I'm trying to say welcome to suggest a better title

Answer 1

Your code is not implemented correctly (besides the main requirement of showing GameName). Actually the info from Game is not referenced directly from ApplicationUser. Not sure why you don't include that in the class (together with GameID). Suppose you do that (including the property Game), the code can be simple like this:

var invites = await _context.Invites.AsNoTracking()
                            .Include(e => e.User.Game).ToListAsync();

//NOTE: this can contain duplicate strings added to `InviteList`, unless you 
//include more data field in each item.
foreach (Invite i in invites)
{
     ViewBag.InviteList.Add(i.User.Game.GameName + " " + i.User.GameTag);         
}

If you don't want to include the property Game in the ApplicationUser, you need to join the entities, like this:

var games = await (from invite in _context.Invites
                   join game in _context.Games on invite.User.GameID equals game.ID
                   select new { game.GameName, invite.User.GameTag }).AsNoTracking().ToListAsync();
//to avoid duplicate items (as noted in the previous code)
ViewBag.InviteList = games.GroupBy(e => e.GameName)
                          .Select(g => g.First())
                          .Select(e => $"{e.GameName} {e.GameTag}").ToList();