join, group by and count sql to linq conversion

Question

Query:

SELECT COUNT(*) as Avalied
FROM LeaveMaster as lm
JOIN LeaveSubsidary as ls 
ON lm.TransMasterId = ls.TransMasterId
WHERE lm.SystemCode = 'abc123' AND lm.EmployeeCode = '0014' AND lm.Status ='Approved'
Group by ls.Leave_Type

How would you convert the above sql query to linq? Thanks :)

Edit :

from p in db.PY_LeaveTransactionMasterTAB.AsEnumerable()
join e in db.PY_LeaveTransactionSubsidaryTAB.AsEnumerable() on p.LV_TransMasterId equals e.LV_TransMasterId into grp
where p.SystemCode == SysCode && p.EmployeeCode == EmpCode && p.Status == "Approved"
group grp by e.Leave_Type // This line is invalid Why?

The above is what I have tried so far, why can't I do e.Leave_Type or what is the right way to do group grp by e.Leave_Type?

Possible duplicate of [LINQ - Left Join, Group By, and Count](http://stackoverflow.com/questions/695506/linq-left-join-group-by-and-count) — Gurwinder Singh, Apr 01 '17 at 15:18
@GurV I've already seen that link, take a look at my edited part. — Bilal Ahmed, Apr 01 '17 at 15:28

score 1 · Accepted Answer · answered Apr 03 '17 at 08:31

1

You could try this:

(from p in db.PY_LeaveTransactionMasterTAB
join e in db.PY_LeaveTransactionSubsidaryTAB on p.LV_TransMasterId equals e.LV_TransMasterId 
where p.SystemCode == SysCode && p.EmployeeCode == EmpCode && p.Status == "Approved"
group new {p,e} by new {e.Leave_Type } into gr
select new 
{
   Leave_Type = gr.Key.Leave_Type ,
   Count = gr.Count()
}).ToList();

answered Apr 03 '17 at 08:31

TriV

5,118
2
10
18

That's the Answer! – Bilal Ahmed Apr 03 '17 at 10:35

join, group by and count sql to linq conversion

1 Answers1