c# 4.0 - following linq performing left outer join instead of inner join -

i cannot see problem lies following code. trying retrieve employees named responsibles vacancie. have 20 vacancies in db assigned 16 employees , 1801 employee records in employees table. code returns result 1801 entries.

from emp in container.employees join p in container.vacancies on emp.empid equals p.responsibleofficer j group j new {k1=emp.empid,k2=emp.name} g select new { empid = g.key.k1, name = g.key.k2 , count = g.count()} 

i want similar this

select emp.empid,emp.name,count(*) count vacancies p, employees e p.responsibleofficer=e.empid group e.empid,e.name 

any appreciated. thanks

you're using join ... into. always return single result each element of original sequence, if there no matches in right sequence.

you can filter out entries no elements in j using where clause:

from emp in container.employees join p in container.vacancies on emp.empid equals p.responsibleofficer j j.any() group j new {k1=emp.empid,k2=emp.name} g select new { empid = g.key.k1, name = g.key.k2 , count = g.count()} 

or use inner join start - don't understand current grouping enough see you're trying do. is group by clause for?

edit: if group employee, you're doing that. can change code to:

from emp in container.employees join p in container.vacancies on emp.empid equals p.responsibleofficer j j.any() select new { employee = emp, count = j.count()} 

basically, after join you've got 2 range variables in scope: emp (the "current" employee) , j (all relevant vacancies matching employee). you're trying count j each employee, right?