CODEWITHSUNDEEP
c#sqlsql-serverlinq
Austin
Austin

Reputation: 3080

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

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

Upvotes: 5

Views: 20957

Answers (2)

mJ222398
mJ222398

Reputation: 140

Another way to do this would be to include your Database relationships in your C# entities. You could use EntityRef here. See the following documentation:

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/how-to-map-database-relationships

Upvotes: 0

Christos
Christos

Reputation: 53958

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();

Upvotes: 11

Related Questions