CODEWITHSUNDEEP
c#sqllinq
Mehdi Emrani
Mehdi Emrani

Reputation: 1313

SQL to Linq: RIGHT JOIN in LINQ

anybody can help me to convert some sql query whit right join like this to linq ?

SELECT dbo.FinnTrans.SanadID, dbo.FinnTrans.Date, dbo.FinnAccount.ID AS AccID,
dbo.FinnAccount.FullId, dbo.FinnAccount.Name, SUM(dbo.FinnTrans.Debit) AS TotalDebit,
SUM(dbo.FinnTrans.Credit) AS TotalCredit
FROM dbo.FinnAccount AS FinnAccount_1 LEFT OUTER JOIN
dbo.FinnAccount ON FinnAccount_1.ParentId = dbo.FinnAccount.ID RIGHT OUTER JOIN
dbo.FinnTrans LEFT OUTER JOIN
dbo.FinnAccount AS FinnAccount_2 ON dbo.FinnTrans.AccID = FinnAccount_2.ID ON
FinnAccount_1.ID = FinnAccount_2.ParentId
WHERE (dbo.FinnTrans.FPID = 7) AND (FinnAccount_2.AccLevel = 3)
GROUP BY dbo.FinnTrans.SanadID, dbo.FinnTrans.Date, dbo.FinnAccount.ID,
dbo.FinnAccount.Name, dbo.FinnAccount.FullId 
HAVING (dbo.FinnTrans.SanadID = 1)

Upvotes: 2

Views: 1454

Answers (2)

Jodrell
Jodrell

Reputation: 35716

I've taken the liberty of beatifying your TSQL a little.

The last two join conditions appear malformed to me so this TSQL can not be parsed. 

SELECT
                  [t].SanadID
                , [t].Date
                , [a].ID [AccID]
                , [a].FullId
                , [a].Name
                , SUM([t].Debit) [TotalDebit]
                , SUM([t].Credit) [TotalCredit]
     FROM
                dbo.FinnAccount [a1] 
         LEFT OUTER JOIN
                dbo.FinnAccount [a]
                    ON [a1].ParentId = [a].ID
         RIGHT OUTER JOIN
                dbo.FinnTrans [t]
         LEFT OUTER JOIN
                dbo.FinnAccount [a2] 
                    ON [a].AccID = [a2].ID 
                    ON [a1].ID = [a2].ParentId
     WHERE 
                 [t].FPID = 7
         AND 
                 [a2].AccLevel = 3
     GROUP BY
                   [t].SanadID
                 , [t].Date
                 , [a].ID
                 , [a].Name
                 , [a].FullId  
         HAVING 
                 [t].SanadID = 1

Upvotes: 1

Archeg
Archeg

Reputation: 8462

You can look here: http://www.hookedonlinq.com/OuterJoinSample.ashx as an example of the left outer join. And you can always swap tables to get either left or right

Upvotes: 2

Related Questions