如何在LINQ中使用左外连接进行SQL查询

本文关键字：连接查询 SQL LINQ | 更新日期: 2023-09-27 18:06:04

如何在LINQ中使用左外连接进行以下SQL查询?

SELECT a.EventID, a.PrizeId, b.PrizeName, b.PrizeValue, c.FightID, c.Winnerid, c.WinnerName
FROM tblUserprize a
JOIN tblPrizeDetails b
  ON a.PrizeId=b.PrizeId
LEFT OUTER JOIN tblWinnersList c
  ON a.EventID=c.EventID AND a.PrizeId=c.PrizeId AND c.FightID = 1534
WHERE a.EventID = 1320

看起来应该是这样的:

var userPrize = (
    from a in tblUserprize
    join b in tblPrizeDetails on a.PrizeId equals b.PrizeId
    join c in tblWinnersList on new { a.EventID, a.PrizeId } equals new { c.EventID, c.PrizeId } into joinedTables
    from item in joinedTables.DefaultIfEmpty()
    where a.EventID == 1320 && item.FightID == 1534
    select new
    {
        a.EventID,
        a.PrizeId,
        b.PrizeName,
        b.PrizeValue,
        item.FightID,
        item.Winnerid,
        item.WinnerName
    });