Sql query to Linq using subquery

Question

Actually,I'm getting a list of top 5 countires based on count like this :

select top 5 COUNT(distinct FROM_EMAILID) as Count, 
FROM_COUNTRY from SURVEY_VISITORS  
where TEMPLATE_ID=79 and FROM_COUNTRY<>'undefined' 
group by FROM_COUNTRY order by COUNT desc

Now,I need to convert into Linq,but unable to do it.

I have tried using subqueries like this for getting top 1 country.but for top 5 countries,I was bit confused :

 var innerQuery = (from t in VDC.SURVEY_VISITORS
                   group t by new
                   {
                      t.FROM_COUNTRY
                   } into g
                   orderby
                   g.Count() descending
                   select new
                   {
                     VisitorCount = (Int64?)g.Count(),
                     Country = g.Key.FROM_COUNTRY
                   }).FirstOrDefault();

   var result = (from xx in VDC.SURVEY_VISITORS
                  where ((innerQuery.Country.Contains(xx.FROM_COUNTRY)) 
                   && xx.TEMPLATE_ID == 79)
                   select new
                    {
                       xx.FROM_COUNTRY,
                       xx.FROM_EMAILID
                     }).Distinct().ToList();

My result should be :

Any help would be greatly appreciated.

Answer 1

This works Perfect for me.

var query = (from xx in VDC.SURVEY_VISITORS
             where xx.TEMPLATE_ID == tempid
             group xx by new { xx.FROM_COUNTRY } into g
             select new
             {
                Count = g.Select(act => act.FROM_EMAILID).Distinct().Count(),
                g.Key.FROM_COUNTRY
             }).OrderByDescending(x => x.Count).Take(5);

Answer 2

Not sure, if OrderBy would swallow that complex expression, but try this:

SurveyVisitors
    .Where(x => x.TemplateId = 79 && x.FromCountry != "undefined")
    .GroupBy(x => x.FromCountry)
    .OrderByDescending(x => x.Select(y => y.FromEmailId).Distinct().Count())
    .Take(5)
    .Select(new => {
        Count = x.Select(y => y.FromEmailId).Distinct().Count(),
        FromCountry = x.Key
    })