SQL查询使用子查询的Linq [英] Sql query to Linq using subquery

问题描述

其实，我收到的顶部基于数5 countires一个列表是这样的：

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

现在，我需要转换成LINQ的，但无法做到这一点。

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

我已经用这样的子查询获取前1 country.but的前5个国家尝试过，我有点糊涂了：

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 :

任何帮助将是很大的AP preciated。

Any help would be greatly appreciated.

推荐答案

这对我来说完美的作品。

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

这篇关于SQL查询使用子查询的Linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！