how can this SQL be done in LINQ?

i have this simple SQL query...

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

Im using entity framwork and want to do the same thing using linq. so far i have...

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

       开发者_Go百科     foreach(var t in tester)
            {
                BestSeller.Make = t.make;
                BestSeller.Model = t.make;
                BestSeller.CountValue = t.count;
            }     

i keep getting timeouts, the database is large but the SQL runs very quick

any sugestions?

thanks

truegilly

Group by a compound key.

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;

Check what queries it performs when you run it with LINQ.

I suspect that you orderby g.Count() descending might be executing a COUNT query for each row and that would take a toll on performance to say the least.

When working with EF, always check what your LINQ statements produce in terms of queries. It is very easy to create queries that result in a n+1 scenario.

thanks to Scott Weinstein answer i was able to get it working

please comment if there is a more efficiant way of doing this...

        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;