在LINQ中按和MIN()分组 [英] Group by and MIN() in LINQ

问题描述

试图将下面的SQL查询转换为LINQ，但是我只能按ClientCompany分组.

Trying to convert the below SQL query to LINQ, but I'm stuck at grouping by 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:

My attempt to convert this to 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 ，这样每个ClientCompany只能返回一行?

How would I use MIN(FeatureID) and GROUP BY ClientCompany in LINQ, so that I only get a single row per ClientCompany back?

编辑

这行得通！基于Daniel Hilgarth的回答.此解决方案有什么地方可能出奇的错误吗?

This worked! Based on Daniel Hilgarth's answer. Is there anything that can go horribly wrong with this solution?

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)

推荐答案

尝试一下:

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 ，从而使其有效地成为内部联接.

Please note, I changed the left outer join into an inner join, because your original query accesses AdFeature unconditionally, making it effectively an inner join .

这篇关于在LINQ中按和MIN()分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！