I have the following SQL query and I need to have it in LINQ, I tried several things but I can not get it working.
Here is the SQL query
- SELECT ST.Description, ST.STId, COUNT(SI.SIId) AS Expr1
- FROM BP INNER JOIN
- MbrBP ON BP.BPId = MbrBP.BPId INNER JOIN
- SI ON BP.BPId = SI.BPId RIGHT OUTER JOIN
- ST ON SI.STId = ST.STId
- WHERE (BP.CountryId = 1) AND (BP.RegionId = 1) AND (MbrBP.MemberId = 1)
- AND (SI.IsActive = 1)
- GROUP BY ST.Description, ST.STId
- UNION
- SELECT ST.Description, ST.STId, COUNT(SI.STId) AS Expr1
- FROM SI RIGHT OUTER JOIN
- ST ON SI.STId = ST.STId
- GROUP BY ST.Description, ST.STId
Thanks in advance.