SQL query cannot be converted to LINQ
When I try to convert this SQL snippet to LINQ I got this error:
SQL cannot be converted to LINQ: Table [GL] was not found in the current Data Context.
But in SQL it works great.
This is my SQL query:
SELECT itm.Id ,
ISNULL(itm.Debit, 0) AS Debit ,
ISNULL(itm.Credit, 0) AS Credit ,
itm.State ,
itm.DocCreateDate ,
ISNULL(itm.Num, 0) AS Num ,
ISNULL(itm.DocTypeRef, 0) AS DocTypeRef ,
itm.Year ,
itm.Month ,
ISNULL(itm.DebitCount, 0) AS DebitCount ,
ISNULL(itm.CreditCount, 0) AS CreditCount ,
itm.DL ,
itm.DL2 ,
itm.DL3 ,
itm.DL4 ,
itm.DL5 ,
itm.DL6 ,
itm.DL7 ,
ISNULL(itm.FCRef, 0) AS FCRef ,
itm.FollowUpNum ,
ISNULL(itm.BranchRef, 1) AS BranchRef ,
itm.DocHeaderRef ,
ISNULL(itm.RowNum, 0) AS RowNum ,
ISNULL(itm.DailyNum, 0) AS DailyNum ,
ISNULL(itm.TempNum, 0) AS TempNum ,
ISNULL(itm.RefNum, 0) AS RefNum ,
itm.Descript ,
itm.Count ,
itm.FollowUpDate ,
itm.FCVal ,
itm.FCRateVal ,
itm.FactorNum ,
ISNULL(itm.DebitFCVal, 0) AS DebitFCVal ,
ISNULL(itm.CreditFCVal, 0) AS CreditFCVal ,
sl.Id AS SLRef ,
sl.SLCode ,
sl.Title AS SLTitle ,
CASE WHEN ISNULL(sl.DLSRef, 0) > 0 THEN 1
ELSE 0
END AS HasDL ,
CASE WHEN ISNULL(sl.DLSRef2, 0) > 0 THEN 1
ELSE 0
END AS HasDL2 ,
CASE WHEN ISNULL(sl.DLSRef3, 0) > 0 THEN 1
ELSE 0
END AS HasDL3 ,
CASE WHEN ISNULL(sl.DLSRef4, 0) > 0 THEN 1
ELSE 0
END AS HasDL4 ,
CASE WHEN ISNULL(sl.DLSRef5, 0) > 0 THEN 1
ELSE 0
END AS HasDL5 ,
CASE WHEN ISNULL(sl.DLSRef6, 0) > 0 THEN 1
ELSE 0
END AS HasDL6 ,
CASE WHEN ISNULL(sl.DLSRef7, 0) > 0 THEN 1
ELSE 0
END AS HasDL7 ,
CASE WHEN ISNULL(sl.HasFC, 0) > 0 THEN 1
ELSE 0
END AS HasFC ,
1 AS HasFollow ,
tl.Id AS TLRef ,
tl.Title AS TLTitle ,
tl.TLCode ,
gl.Id AS GLRef ,
gl.Title AS GLTitle ,
gl.GLCode ,
gl.Balance AS GLBalance
FROM Acc.DocItem AS itm
LEFT OUTER JOIN Acc.SL AS sl ON itm.SLRef = sl.Id
LEFT OUTER JOIN Acc.TL AS tl ON sl.TLRef = tl.Id
LEFT OUTER JOIN Acc.GL AS gl ON tl.GLRef = gl.Id
WHERE ( itm.SLRef > 0 )
If you cannot pass this error, can you tell that its LINQ is equal?
+3
source to share
1 answer
There is a way to do this in linq:
var q =
from c in categories
join p in products on c.Category equals p.Category into ps
from p in ps.DefaultIfEmpty()
select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName };
It's a big idea for a left outer join, you are like the syntax for "in" like "like" in a SQL query.
+1
source to share