How to write lambda expression for an sql expression?

Question

I have an SQL expression

select S.SpecialtyName, COUNT(distinct SUC.SiteUserId) as Subscribers
from   SiteUserContent SUC Inner join 
       Specialties S on SUC.SpecialtyId = S.SpecialtyId Inner join 
        SiteUser SU on SUC.SiteUserId = SU.SiteUserId
where SU.DeletedFlag = 0
group by S.SpecialtyName
Order by S.SpecialtyName

What will be the corresponding LINQ expression for the same?

Answer 1

from suc in context.SiteUserContent
join s in context.Specialties on suc.SpecialtyId equals s.SpecialtyId
join su in context.SiteUser on suc.SiteUserId equals su.SiteUserId
where su.DeletedFlag == 0
select new { suc.SiteUserId, s.SpecialityName } into x
group x by x.SpecialityName into g
orderby g.Key
select new { 
    SpecialityName = g.Key, 
    Subscribers = g.Select(i => i.SiteUserId).Distinct().Count()
}

Generated SQL will not be same, but I think result of query execution should be same.

Answer 2

var results = contex.SiteUserContent
                    .Join(context.Specialties, suc => suc.SpecialtyId, s => s.SpecialtyId, (suc, s) => new { suc, s })
                    .Join(context.SiteUser, i = i.suc.SiteUserId, su => su.SiteUserId, (i, su) => new { suc = i.suc, s = i.s, su = su })
                    .Where(i => i.su.DeletedFlag == 0)
                    .GroupBy(i => i.s.SpecialtyName)
                    .Select(g => new {
                                     SpecialityName = g.Key,
                                     Subscribers = g.Select(i => i.suc.SiteUserId)
                                                    .Distinct()
                                                    .Count()
                                 })
                     .OrderBy(i => i.SpecialityName);