Linq select Item where it is equal to ID in another table

Question

I am not sure how possible this is but I have two tables and I want to grab a value from table 2 via the value of table 1.

Table 1 has the a Foreign Key called "rank" which is an int. Table 2 has a value called "name" which is a string. Now Table 1's "rank" correlates to Table 2's "ID".

So when I say

var result = db.Table1.Select(x => new { x.name, x.rank }).ToList(); //Bob - 2

I really want to say something like

var result = db.Table1.Select(x => new { x.name, Table2.rank.Where(ID == x.rank) }).ToList(); //Bob - Gold

I am still new to LINQ though and I am not sure how to get rank's string value from the other table within a query like this.

EDIT

Tables I am using and their relational values.

User: ID (PK), s1elo (FK to PastElos), champ (FK to ChampionList), elo (FK to EloList)

PastElo: ID (PK), Rank

ChampionList: ID (PK), name

EloList: ID (PK), Rank

Working example for Users and PastElo

var result = db.Users.Join(db.PastEloes, x => x.s1elo, y => y.ID, (x, y) => new { y.Rank, x.name, x.other_items_in_Users }).ToList();

Note: PastElo is PastEloe's due to EF making everything plural when I synced up my DB, thus why User is also Users, I think that is referred to as the "context".

Answer 1

You could try something like the following:

var result = db.Table1.Join(db.Table2, 
                            x=>x.rank, 
                            y=>y.ID, 
                           (x,y) => new { x.rank, y.Name }).ToList();

In the above linq query we make a Join between the two tables, Table1 and Table2 based on the association and then we select that we want.

Another way you could try to write this query would be the following:

var result = (from t1 in db.Table1
             join t2 in db.Table2
             on t1.rank equals t2.ID
             select new { t1.rank, t2.Name, }).ToList();