CODEWITHSUNDEEP
c#linqsql-to-linq-conversion
DAKSH
DAKSH

Reputation: 1

How to use left outer join in LINQ for SQL query?

How can I use left outer join in LINQ for the following SQL query?

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

Upvotes: 0

Views: 275

Answers (1)

msmolcic
msmolcic

Reputation: 6557

It should look like this:

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

Upvotes: 1

Related Questions