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?

I think you are looking for the corresponding Linq expression, not Lambda. — gunr2171
– gunr2171, Commented Mar 26, 2013 at 16:26

Sergey Berezovskiy · Accepted Answer · 2013-03-26 15:22:14Z

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.

edited Mar 26, 2013 at 15:22

answered Mar 26, 2013 at 14:37

Sergey Berezovskiy

237k44 gold badges441 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gunr2171 Over a year ago

(Minor detail) I think you are missing an order by at the end.

MarcinJuraszek · Accepted Answer · 2013-03-26 14:47:03Z

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);

Collectives™ on Stack Overflow

How to write lambda expression for an sql expression?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related