Linq选择与另一个表中的ID相等的项目 [英] Linq select Item where it is equal to ID in another table

问题描述

我不确定这有什么可能，但是我有两个表，我想通过表1的值从表2中获取一个值.

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.

表1具有一个称为"rank"的外键，它是一个int.表2具有一个名为名称"的值，它是一个string.现在，表1的等级"与表2的"ID"相关.

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

所以我说

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

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

我真的想说些类似的话

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

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

尽管我还是LINQ的新手，但我不确定如何从这样的查询中的另一个表中获取rank的字符串值.

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.

编辑

我正在使用的表及其关系值.

Tables I am using and their relational values.

用户:ID(PK)，s1elo(从FK到PastElos)，冠军(从FK到ChampionList)，elo(从FK到EloList)

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

PastElo:ID(PK)，排名

PastElo: ID (PK), Rank

ChampionList:ID(PK)，名称

ChampionList: ID (PK), name

EloList:ID(PK)，排名

EloList: ID (PK), Rank

Users和PastElo的工作示例

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

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

注意:PastElo是PastEloe的产品，原因是在我同步数据库时，EF使所有内容复数，因此为什么User也是Users，我认为这被称为上下文".

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

推荐答案

您可以尝试以下操作:

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

在上面的linq查询中，我们根据关联在两个表Table1和Table2之间创建一个Join，然后选择所需的表.

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

这篇关于Linq选择与另一个表中的ID相等的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！