Correlated subquery in Linq

Question

I have a employee table and EmployeeCourseStatus table.

I want to display the list of each employee with a count of courses completed(status = "CMP").

I have following correlated subquery which results in error below:

var query = (from emp in Employee
                     join adr in EmployeeAddress on emp.id = adr.EmployeeID
                     select new
                     {
                        id = emp.id,
                        name=emp.name,
                        country=adr.country,
                        CompletedCourseCount = (from c in employeeCourseStatus where c.empid = emp.id && c.status == "CMP" select c.id).count()
                     }

Error:

Only Premitive types are supported.

The equivalent SQL subquery would be -

Select emp.id
       , emp.name
       , adr.Country
       , CompletedCourseCount = (select count(id) from EmployeeCourseStatus where id = emp.id and status = "CMP") 
from   Employee emp
       JOIN employeeaddress adr ON adr.EmployeeID = emp.ID

Answer 1

Use equals keyword when joining sequences

var query = from emp in Employee
            join adr in EmployeeAddress on emp.id equals adr.EmployeeID
            join c in EmployeeCourseStatus on emp.id equals c.empid into courses
            select new
            {
               id = emp.id,
               name = emp.name,
               country = adr.country,
               CompletedCourseCount = courses.Where(x => x.status == "CMP").Count()
            };

Answer 2

Please try replacing where c.empid = emp.id with where c.empid == emp.id in your count query.

If that does not work, what are the types of emp.name and adr.country?