How to convert this T-SQL query to lambda or query syntax in C#

Question

I am new to Entity Framework in C#. I am trying to convert this SQL statement into a lambda expression or query syntax, but I can't to seem to figure out how exactly to do it.

Here is the SQL query:

SELECT
    a.Id, a.IndustryName, 
    COUNT(b.ProcessJobPostNumId) AS IndustryCount 
FROM
    ERECRUITDEV.dbo.Industries a
LEFT JOIN
    (SELECT 
         b.IndustryID, b.Id AS JobPostId, d.MdaId, c.ParishID, 
         c.EmploymentType, c.Id AS JobPostNumId, 
         d.Id AS ProcessJobPostNumId, e.AvailableInternal  
     FROM 
         ERECRUITDEV.dbo.JobPost b
     LEFT JOIN
         ERECRUITDEV.dbo.JobPostNumber c ON c.JobPostId = b.id
     LEFT JOIN
         ERECRUITDEV.dbo.ProcessJobPostNum d ON d.JobPostNumId = c.Id
     LEFT JOIN
         ERECRUITDEV.dbo.RecruitmentProcess e ON e.id = d.RecruitProcessId 
     WHERE
         AvailableInternal = 1) b ON b.IndustryID = a.Id
GROUP BY 
    a.Id, a.IndustryName

Answer 1

As you informed that all entity objects contain navigation property for every join you make in the query you provide, then the lambda expression below should work like a charm;

var result = context.Industries.Select(i => new {
    i.Id,
    i.IndustryName,
    IndustryCount = i.JobPosts
        .Where(jp => jp.JobPostNumbers
            .Any(jpn => jpn.ProcessJobPostNums
                .Any(pjpn => pjpn.RecruitmentProcess
                    .Any(rp => rp.AvailableInternal == 1))))
        .SelectMany(jp => jp.ProcessJobPostNums
            .SelectMany(jpn => jpn.ProcessJobPostNums
                .Select(pjpn => pjpn.Id)))
        .Count()
}).ToList();