分组依据和 MIN() 在 LINQ 中

本文关键字：LINQ MIN | 更新日期: 2023-09-27 18:35:49

尝试将下面的SQL查询转换为LINQ，但我被困在按ClientCompany分组。

SELECT TOP 300 ClientCompany,
CASE WHEN MIN(FeatureID) = 12 THEN 1 ELSE 0 END as Sort
FROM Ad
LEFT JOIN AdFeature
ON Ad.ID = AdFeature.AdID
WHERE (AdFeature.FeatureID = 13 OR AdFeature.FeatureID = 12)
AND SiteID = 2
GROUP BY ClientCompany
ORDER BY Sort DESC

我尝试将其转换为 LINQ：

(from a in Ads
join af in AdFeatures
on new {
join1 = a.ID,
join3 = 2
} equals new {
join1 = af.AdID,
join3 = af.SiteID
}
let sort = (
af.FeatureID == 12 ? 1 : 0
)
orderby sort descending
where af.FeatureID == 13 || af.FeatureID == 12
select new { a.ClientCompany, sort } ).Take(300)

如何在 LINQ 中使用 MIN(FeatureID) 和 GROUP BY ClientCompany，以便每个客户公司只返回一行？

编辑

这奏效了！基于丹尼尔·希尔加斯的回答。这个解决方案有什么可怕的问题吗？

Ads.Join(AdFeatures, x => x.ID, x => x.AdID,
(a, af) => new { Ad = a, AdFeature = af })
.Where(x => x.AdFeature.FeatureID == 12 || x.AdFeature.FeatureID == 13)
.Where(x => x.AdFeature.SiteID == 2)
.GroupBy(x => x.Ad.ClientCompany)
.Select(g => new { ClientCompany = g.Key, Sort = g.Min(x => x.AdFeature.FeatureID) == 12 ? 1 : 0 })
.OrderByDescending(x => x.Sort)
.Take(300)

分组依据和 MIN() 在 LINQ 中

试试这个：

Ads.Join(AdFeatures, x => x.FeatureID, x => x.FeatureID,
         (a, af) => new { Ad = a, AdFeature = af })
   .Where(x => x.AdFeature.FeatureID == 12 || x.AdFeature.FeatureID == 13)
   .Where(x => x.AdFeature.SiteID == 2)
   .GroupBy(x => x.Ad.ClientCompany)
   .Select(g => new { ClientCompany = g.Key,
                      Sort = g.Min(x => x.AdFeature.FeatureID) == 12 ? 1 : 0 });

请注意，我将左外部连接更改为内部连接，因为您的原始查询无条件访问AdFeature，使其成为有效的内部连接。

嗨，

我会这样写

            context.Ads.Where(ad => ad.AdFeatures.Any(feature => (feature.FeatureID == 13 || feature.FeatureID == 12) && feature.SiteID == 2))
                   .GroupBy(ad => ad.ClientCompany)
                   .Select(ads => new
                   {
                       cc = ads.Key, sort = ads.SelectMany(ad => ad.AdFeatures)
                                               .Select(feature => feature.FeatureID)
                                               .Min() == 12
                   })
                   .OrderBy(arg => arg.sort).Take(300);

试试这个：

(from a in ads
 join af in AdFeatures on a.ID equals af.AdID into g
 from x in g.DefaultIfEmpty()
 where x.FeatureID == 13 || x.FeatureID == 12
 where x.SiteID == 2
 orderby a.Sort descending
 group a by a.ClientCompany into g2
 from x2 in g2
 let sort = g2.Select(T => T.FeatureID).Min() == 12 ? 1 : 0
 select new { a.ClientCompany, Sort = sort }).Take(300);

为什么还需要分组？

相关文章：