Group by clause is not handled by the convertor

[ganeshkamath89]

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 }