c# - 如何使用 LINQ 编写子查询

Question

我正在尝试使用 LINQ 编写类似的查询

select DISTINCT(m.MatchID), 
    (select TeamName 
     from tbl_TournamentTeams 
     where TeamID = m.Team1), 
    (select TeamName 
     from tbl_TournamentTeams 
     where TeamID = m.Team2) 
     from tbl_TournamentTeams t, tbl_Match m 
     where t.TournamentID = m.TournamentID

SQL tbl_TournamentTeams 表设计，

Create Table tbl_TournamentTeams(
TeamID int primary key Identity,
TournamentID int Foreign key references tbl_Tournament(TournamentID),
TeamName varchar(30) NOT NULL
);

SQL tbl_Match表设计，

Create Table tbl_Match(
MatchID int primary key Identity,
TournamentID int Foreign key references tbl_Tournament(TournamentID),
Team1 int Foreign key references tbl_TournamentTeams(TeamID),
Team2 int Foreign key references tbl_TournamentTeams(TeamID),
StartTime DateTime not null,
MatchBetAmount int not null
);

请告诉我我是否能够使用 LINQ 编写类似的查询。谢谢

我想从 linq 查询中获取每场比赛的 TeamName，目前我只能获取一场比赛的 teamId。请让我知道如何通过以下查询获取 TeamName 详细信息，

 var res = dbEntity.tbl_Match.Join(dbEntity.tbl_TournamentTeams,
                                            m => m.TournamentID,
                                            t => t.TournamentID,
                                            (m, t) => new
                                            {
                                                MatchID = m.MatchID,
                                                MatchTeam1 = m.Team1,
                                                MatchTeam2 = m.Team2,
                                                TournamentID =t.TournamentID

                                                // How to print the team name details as well here, where currently i'm just able to fetch the teamID
                                            });

Answer 1

我假设 team1 和 team2 不包含空值

 dbEntity.tbl_Match.Select(m => new{
     m.Id,
    Team1Name=dbEntity.TournamentTeams.SingleOrDefault(t => t.id=m.Team1).Name,
    Team2Name =dbEntity.TournamentTeams.SingleOrDefault(t => t.id=m.Team2).Name
     })