OUTER APPLY with Linq

OUTER APPLY in Sql Server is a very convenient joining technique to achieve Left outer join with a group by. See the below example. 

Example: 

select Name, ea.eeacount, es.name 

From dbo.EnrollmentEntity ee 

OUTER APPLY

(

    Select count(eea.Id) as eeacount 

    From dbo.EnrollmentEntityAssister eea 

    Where eea.EnrollmentEntityId = ee.Id and eea.active =1 and ee.active = 1

)ea

OUTER APPLY

(

    Select TOP 1 ls.name 

    From dbo.EnrollmentEntityStatus ees 

    join dbo.LookupEnrollmentEntityStatus ls on ees.StatusId = ls.Id and ls.active = 1

    where ees.EnrollmentEntityId = ee.Id and ees.active = 1 and ee.active = 1

    Order by ees.UpdatedDate desc 

)es

This query gets all the EnrollmentEntity records with a count of the assisters for each Enrollment Entity and the latest status

To achieve similar in Linq, we can do something like this: 

var results = from enrollmentEntity in enrollmentEntities
        join assister in assisters on enrollmentEntity.Id equals assister.EnrollmentEntityId into eAssisters 
        join status in statuses on enrollmentEntity.Id equals status.EnrollmentEntityId into eStatuses 
        where enrollmentEntity.Active
         select new EnrollmentEntityGridViewModel
        {
            Id = enrollmentEntity.Id,
            Name = enrollmentEntity.Name,
            Assisters = eAssisters.Count(), 
            RenewalDate = "",
            Status =  eStatuses.OrderByDescending(p => p.UpdatedDate).FirstOrDefault().StatusIdSource.Name 
       };

Neat, huh!