如何在LINQ中完成此SQL

Question

我有这个简单的SQL查询…

-- BestSeller
SELECT TOP(1) v.make, v.model, COUNT(v.make) AS NoSold
FROM Vehicles v
group by v.make, v.model
order by NoSold DESC

我使用实体框架，并希望使用linq做同样的事情。到目前为止，我已经……

                var tester = (from v in DB.VP_Historical_Vehicles
                         group v by v.make into g
                         orderby g.Count() descending
                         select new { make = g.Key, model = g, count = g.Count() }).Take(1);
            foreach(var t in tester)
            {
                BestSeller.Make = t.make;
                BestSeller.Model = t.make;
                BestSeller.CountValue = t.count;
            }     

我总是超时，数据库很大，但SQL运行得很快

有什么建议吗?

感谢

truegilly

Answer 1

按复合键分组

var t = (
    from v in DB.VP_Historical_Vehicles
    group v by new { v.make, v.model } into g
    orderby g.Count() descending
    select new { make = g.Key.make, model = g.Key.model, count = g.Count() }
    )
    .First();
BestSeller.Make = t.make;
BestSeller.Model = t.make;
BestSeller.CountValue = t.count;

Answer 2

检查使用LINQ运行时执行的查询。

我怀疑您的orderby g.Count() descending可能对每一行执行COUNT查询，这至少会对性能造成影响。

当使用EF时，总是检查LINQ语句在查询方面产生了什么。创建导致n+1场景的查询非常容易。

Answer 3

多亏了Scott Weinstein的回答，我才能让它正常工作

请评论是否有更有效的方法来做这件事…

        VehicleStatsObject BestSeller = new VehicleStatsObject();
        using (var DB = DataContext.Get_DataContext)
        {
            var t = (from v in DB.VP_Historical_Vehicles
                     group v by new { v.make, v.model } into g
                     orderby g.Count() ascending
                     select new { make = g.Key.make, model = g.Key.model, count = g.Count() }).OrderByDescending(x => x.count).First();
                BestSeller.Make = t.make;
                BestSeller.Model = t.model;
                BestSeller.CountValue = t.count;                              
        }
        return BestSeller;