How to convert join 2 tables with group by into linq

Question

How could I convert this query into Linq in C#?

SQL query:

SELECT * 
FROM com.DepartmentPersonnel q1 
INNER JOIN 
    (SELECT dp.DepartmentID, MIN(dp.JobTypeID)JobTypeID  
     FROM com.DepartmentPersonnel dp
     GROUP BY dp.DepartmentID) q2 ON q1.DepartmentID = q2.DepartmentID 
                                  AND q1.JobTypeID = q2.JobTypeID

Answer 1

Try This

var query = 
from q1 in dbContext.DepartmentPersonnel
join q2 in
    (from dp in dbContext.DepartmentPersonnel
     group dp by dp.DepartmentID into g
     select new { DepartmentID = g.Key, JobTypeID = g.Min(dp => dp.JobTypeID) })
on new { q1.DepartmentID, q1.JobTypeID } equals new { q2.DepartmentID, q2.JobTypeID }
select q1;