使用DataTable和List进行Join和Group by

本文关键字：进行 Join Group by DataTable List 使用 | 更新日期: 2023-09-27 18:08:58

我正在尝试执行以下linq操作。这里表示为SQL:

SELECT 
    CAMPAIGN,
    ADGROUPID,
    CLICKS,
    TOTALCONV,
    COST,
    COSTCONVCLICK,
    KEYWORD,
    COUNT(*) AS APARICIONES
FROM 
    ADWORDSSEARCH
GROUP BY CAMPAIGN,adgrOUPID,CLICKS,TOTALCONV,COST,COSTCONVCLICK,KEYWORD
ORDER BY CLICKS DESC

我用下面的linq表达式使它工作:

var filtro =(from r in newTable.AsEnumerable()
             group r by new { 
                                camp=r.Field<string>("CAMPAIGN"), 
                                keyw= r.Field<string>("KEYWORD")
                            }
             into grouping
             select new
             {
                 grouping.Key.camp,
                 grouping.Key.keyw,
                 Key = grouping.Key,
                 NumberGroup = grouping.Count()
             }).ToList()
               .OrderByDescending(t=>t.NumberGroup);

但问题是，newTable是一个数据表，它有一个名为AdGroupID的字段是数字，我需要与列表连接，以取代AdGroupID的数字ID与它的描述。这是我目前所尝试的:

var filtro = from r in newTable.AsEnumerable()
             join anuncios in adslist.AsEnumerable() 
             on r.Field<string>("ADGROUPSID") equals anuncios.id.toString() 
             into grouping
             select new
             {
                 Campaign = r.Field<string>("CAMPAIGN"),
                 AdsName = anuncios.name ???? ->Here is the problem
                 keyword = r.Field<string>("KEYWORD"),
                 Counting = grouping.Count()
             };

使用DataTable和List<T>进行Join和Group by

我用from d in grouping.DefaultIfEmpty()让它工作

               var filtro = (from r in newTable.AsEnumerable()
                             join anuncios in adslist.AsEnumerable() on r.Field<string>("ADGROUPID") equals anuncios.id.ToString() into grouping
                             from d in grouping.DefaultIfEmpty()
                             select new
                                 {
                                     Campaign = r.Field<string>("CAMPAIGN"),
                                     AdName = d.name,
                                     Clicks = r.Field<string>("CLICKS"),
                                     TotalConv = r.Field<string>("TotalConvValue"),
                                     Cost = r.Field<string>("Cost"),
                                     CostConvClick = r.Field<string>("CostConvertedClick"),
                                     keyword = r.Field<string>("KEYWORD"),
                                     Counting = grouping.Count()
                                 }).ToList().OrderByDescending(t => t.Clicks);

也可以使用group by然后join，没有from子句:

   List<AdwordsClass> filtro = (from r in newTable.AsEnumerable()
                                   group r by new{ camp=r.Field<string>("CAMPAIGN"), id=r.Field<string>("adgroupid"), keyw= r.Field<string>("KEYWORD")} into grouping
                                  join anuncios in adslist.AsEnumerable() on grouping.FirstOrDefault().Field<string>("ADGROUPID") equals anuncios.id.ToString()
                                                 select new AdwordsClass()
                                  {
                                      CampaignName = grouping.Key.camp,
                                      CampaignId   =  Convert.ToInt64(grouping.FirstOrDefault().Field<string>("CAMPAIGNID")),
                                      AdsGroupID = grouping.Key.id,
                                      KeyWord = grouping.Key.keyw,
                                      AdsGroupName = anuncios.name,
                                      clicks = grouping.FirstOrDefault().Field<string>("CLICKS"),
                                      TotalConv = grouping.FirstOrDefault().Field<string>("TotalConvValue"),
                                      Cost =grouping.FirstOrDefault().Field<string>("Cost"),
                                      CostConvClick =grouping.FirstOrDefault().Field<string>("CostConvertedClick"),
                                      Counting = grouping.Count()
                                  }).ToList().OrderByDescending(t => t.clicks).ToList();
相关文章：