使用 IN 向 Linq 提供 SQL 查询

本文关键字：SQL 查询 提供 Linq IN 使用 | 更新日期: 2023-09-27 18:32:35

我有一个这样的Sql Query：

select distinct FROM_EMAILID,FROM_COUNTRY from SURVEY_VISITORS
where FROM_COUNTRY IN 
(
  select top 1 FROM_COUNTRY as FROM_COUNTRY from SURVEY_VISITORS 
  where FROM_COUNTRY<>'undefined' 
  group by FROM_COUNTRY order by COUNT(*) desc
)

我无法处理IN操作员。
任何帮助将不胜感激。

对于子查询，我尝试了这种方式：

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

使用 IN 向 Linq 提供 SQL 查询

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 == RecentBlastedTemplate.TEMPLATE_ID)                                  
                                  select new
                                      {
                                          xx.FROM_COUNTRY,
                                          xx.FROM_EMAILID
                                      }).Distinct().ToList();
相关文章：