sql - 如何在linq中计算多列数据

Question

SELECT country, city, COUNT(school), COUNT(collage) 
FROM T1
GROUP BY country, city

我在 Linq 中写了这个查询

var count_captainRegisterd1 = (from a in dt1.AsEnumerable()
                               group a by new
                               {
                                   oc_name = a.Field<string>("county"),
                                   oc_city = a.Field<string>("city")
                               } into g
                               select new
                               {
                                   g.Key.oc_name,
                                   g.Key.oc_city,
                                   country = g.Count()
                               }
                              ).ToList();

但他们算country列。我想要country，city两者都算

标签： sqlentity-frameworklinq

Answer 1

这是您查询的 linq：

var count_captainRegisterd1 = 
    (from a in dt1.AsEnumerable()
     group a by new
     {
         oc_name = a.Field<string>("county"),
         oc_city = a.Field<string>("city")
     } into g
     select new
     {
          Country = g.Key.oc_name,
          City = g.Key.oc_city,
          SchoolCount = g.Count(r => !string.IsNullOrWhiteSpace(r.Field<string>("school"))),
          CollageCount = g.Count(r => !string.IsNullOrWhiteSpace(r.Field<string>("collage")))
      }).ToList();