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 : enter image description here

Any help would be greatly appreciated.

Giedrius · Accepted Answer · 2013-10-03 07:32:44Z

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

answered Oct 3, 2013 at 7:32

Giedrius

8,6106 gold badges57 silver badges95 bronze badges

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

Comments

RealSteel · Accepted Answer · 2013-10-03 07:59:14Z

0

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

edited Oct 3, 2013 at 7:59

answered Oct 3, 2013 at 7:40

RealSteel

1,9414 gold badges41 silver badges77 bronze badges

2 Comments

Giedrius Over a year ago

Actions order is not right, you first take 5, then ordering them, should order first, then take 5. Also materialization happens at select, so it is not exact replica of your sql, it receives all records for database and then does ordering and top 5, so you're receiving more records from database as you would with sql.

RealSteel Over a year ago

}).OrderByDescending(x => x.Count).Take(5); works perfect ryt?

Collectives™ on Stack Overflow

Sql query to Linq using subquery

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related