Join and Group by, using DataTable and List<T>

Question

I'm trying to perform the following linq operation. Here expressed as 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

I got it working with the following linq expression:

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

But the problem is that newTable is a Datatable and it has a field called AdGroupID that is numeric, and I need to make a join with a List to replace the numeric ID of AdGroupID with it's description. This is what I tried so far:

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

Answer 1

I got it working using 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); 

It is also working using group by and then join, without the from clause:

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