当有 group by 和 order by 时从 SQL 转换为 Linq

Question

是否可以将这段sql代码转换为linq？

SELECT top 3 t.tProductIDF, Count(t.tQty)as QtyBought, p.pName, v.vName
From Transactions t, Product p, Vendor v
where t.tProductIDF = p.pID and p.pdeleted = 0 and p.pVendorIDF = v.vID and v.vActive = 1
Group By t.tProductIDF, p.pName, v.vName
Order By QtyBought desc;

我目前在这里：

var topProds = (from t in this._entities.Transactions
                group t by t.tProductIDF into g
                orderby g.Count() descending
                select new {g.Key }).Take(3);

但由于我无法从选择部分访问 t，我不知道如何获取 pName 和 vName

Answer 1

var topProds = (from t in this._entities.Transactions  
               group t by t.tProductIDF into g
               orderby g.Count() descending    
               select new { ProductIDF = g.Key , Qty= g.Sum(cc=>cc.tQty) , Vname = g.Select(cc=>cc.vName).FirstOrDefault() }).Take(3); 

你的 g 持有组中的每一行。