how can this SQL be done in LINQ?

Question

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

            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

Check the generated SQL. Looks like the Take(1) is misconstrued and that instead of generating a "SELECT TOP ", the query returns all the data and applies the "Take(1)" on the client side. — Larry, Apr 12 '11 at 11:10

score 2 · Accepted Answer · answered Apr 12 '11 at 11:10

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;

score 0 · Answer 2 · edited May 23 '17 at 12:26

0

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.

edited May 23 '17 at 12:26

Community

1
1

answered Apr 12 '11 at 11:06

Mikael Östberg

16,982
6
61
79

score 0 · Answer 3 · answered Apr 12 '11 at 11:29

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;

how can this SQL be done in LINQ?

3 Answers3