c# - LINQ 的左外连接运行时错误

Question

我正在尝试使用 Linq 在 2 个表上编写左外连接查询，但是当右表中存在空值时，在运行时会出现 NULL 引用异常。

对于 tbl_Match 表上的所有 MatchID 值，tbl_UserBets 表没有所有值。因此，当出现 NULL 值时，我会遇到运行时异常

PFB 我的 LINQ 查询，

string userID = "dfa3c0e7-2aa3-42ee-a7d3-803db902dc56";
var res2 = dbEntity.tbl_Match.Select(m => new
            {
                MatchID = m.MatchID,
                Team1 = m.Team1,
                Team2 = m.Team2,
                UserForTeam1 = dbEntity.tbl_UserBets.Where(b => b.UserForTeam1 == userID).FirstOrDefault(b => b.MatchID == m.MatchID),
                UserForTeam2 = dbEntity.tbl_UserBets.Where(b => b.UserForTeam2 == userID).FirstOrDefault(b => b.MatchID == m.MatchID)
            });

            foreach (var item in res2)
            {
                Console.WriteLine(item.MatchID + " " + item.Team1 + " vs " + item.Team2 + " " + item.UserForTeam1 == null ? " NA " : item.UserForTeam1.UserForTeam1);
            }

tbl_Match表的SQL表设计:

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

tbl_UserBets 表的 SQL 表设计:

Create Table tbl_UserBets(
UserBetSlNo int primary key identity,
TournamentID int Foreign key references tbl_Tournament(TournamentID),
MatchID int Foreign key references tbl_Match(MatchID),
UserForTeam1 nvarchar(128) Foreign key references AspNetUsers(Id),
UserForTeam2 nvarchar(128) Foreign key references AspNetUsers(Id),
UserForNoBets nvarchar(128) Foreign key references AspNetUsers(Id)
);

通过以下 SQL 查询，我能够正确获得结果，需要使用 LINQ 执行相同的操作。

select DISTINCT(tbl_Match.MatchID),tbl_Match.Team1,tbl_Match.Team2,tbl_Match.StartTime,tbl_Match.MatchBetAmount,tbl_UserBets.UserForTeam1,tbl_UserBets.UserForTeam2,tbl_UserBets.UserForNoBets from tbl_Match left outer join tbl_UserBets on tbl_Match.MatchID = tbl_UserBets.MatchID and (tbl_UserBets.UserForTeam1 = 'dfa3c0e7-2aa3-42ee-a7d3-803db902dc56' or tbl_UserBets.UserForTeam2 = 'dfa3c0e7-2aa3-42ee-a7d3-803db902dc56')

请告诉我，我应该做哪些改变来解决这个问题。谢谢。

Answer 1

尝试将 where 条件与第一个或默认条件合并

我猜测问题是当 where 返回 null 时，第一个或默认值将导致异常为 stated by msdn doc

var res2 = dbEntity.tbl_Match.Select(m => new
            {
                MatchID = m.MatchID,
                Team1 = m.Team1,
                Team2 = m.Team2,
                UserForTeam1 = dbEntity.tbl_UserBets.FirstOrDefault(b => b.UserForTeam1 == userID && b.MatchID == m.MatchID),
                UserForTeam2 = dbEntity.tbl_UserBets.FirstOrDefault(b => b.UserForTeam2 == userID && b.MatchID == m.MatchID)
            });