ganeshkamath89 / sql2linqconvertor Goto Github PK

Need help in addressing Group By clause used in SQL to Linq.

Example:

SQL:

SELECT
*
FROM
(SELECT
*
FROM
(SELECT
CMN_BANK_LOAN.ID,
CMN_BANK_LOAN.LOAN_DATE,
CMN_BANK_LOAN.REPAY_DATE,
CMN_BANK_LOAN.AC_ID,
(
CMN_ACCOUNT_MASTER.ACCOUNT_NO + ' : ' + CMN_ACCOUNT_MASTER.BANK_NAME
) AS AC_NO,
CMN_BANK_LOAN.BY_WHOM,
CMN_BANK_LOAN.TOTAL_AMOUNT,
SUM(CMN_BANK_LOAN_PAY.AMOUNT) AS PAID_AMOUNT,
CMN_BANK_LOAN.TOTAL_AMOUNT - SUM(CMN_BANK_LOAN_PAY.AMOUNT) AS OUTSTANDING
FROM
CMN_BANK_LOAN
LEFT JOIN CMN_BANK_LOAN_PAY
ON CMN_BANK_LOAN.ID = CMN_BANK_LOAN_PAY.LOAN_ID
INNER JOIN CMN_ACCOUNT_MASTER
ON CMN_ACCOUNT_MASTER.ID = CMN_BANK_LOAN.AC_ID
GROUP BY CMN_BANK_LOAN.ID,
CMN_BANK_LOAN.LOAN_DATE,
CMN_BANK_LOAN.REPAY_DATE,
CMN_BANK_LOAN.BY_WHOM,
CMN_BANK_LOAN.TOTAL_AMOUNT,
CMN_ACCOUNT_MASTER.ACCOUNT_NO,
CMN_ACCOUNT_MASTER.BANK_NAME) AS A
WHERE A.OUTSTANDING <> 0
ORDER BY A.LOAN_DATE) AS B

Linq:

from B in (
(from A in (
(from CMN_BANK_LOAN in _appdb.CMN_BANK_LOAN
join CMN_BANK_LOAN_PAY in _appdb.CMN_BANK_LOAN_PAY on new { ID = CMN_BANK_LOAN.ID } equals new { ID = CMN_BANK_LOAN_PAY.LOAN_ID } into CMN_BANK_LOAN_PAY_join
from CMN_BANK_LOAN_PAY in CMN_BANK_LOAN_PAY_join.DefaultIfEmpty()
group new {CMN_BANK_LOAN, CMN_BANK_LOAN.CMN_ACCOUNT_MASTER, CMN_BANK_LOAN_PAY} by new {
CMN_BANK_LOAN.ID,
CMN_BANK_LOAN.LOAN_DATE,
CMN_BANK_LOAN.REPAY_DATE,
CMN_BANK_LOAN.BY_WHOM,
CMN_BANK_LOAN.TOTAL_AMOUNT,
CMN_BANK_LOAN.CMN_ACCOUNT_MASTER.ACCOUNT_NO,
CMN_BANK_LOAN.CMN_ACCOUNT_MASTER.BANK_NAME
} into g
select new {
g.Key.ID,
g.Key.LOAN_DATE,
g.Key.REPAY_DATE,
g.Key.AC_ID,
AC_NO = (g.Key.ACCOUNT_NO + " : " + g.Key.BANK_NAME),
g.Key.BY_WHOM,
g.Key.TOTAL_AMOUNT,
PAID_AMOUNT = (decimal?)g.Sum(p => p.CMN_BANK_LOAN_PAY.AMOUNT),
OUTSTANDING = (decimal?)(g.Key.TOTAL_AMOUNT - g.Sum(p => p.CMN_BANK_LOAN_PAY.AMOUNT))
}))
where
A.OUTSTANDING != 0
orderby
A.LOAN_DATE
select new {
A
}))
select new {
B.A.ID,
B.A.LOAN_DATE,
B.A.REPAY_DATE,
B.A.AC_ID,
B.A.AC_NO,
B.A.BY_WHOM,
B.A.TOTAL_AMOUNT,
B.A.PAID_AMOUNT,
B.A.OUTSTANDING
}