when no orderdetails exists, GetTopSellingAlbums returns first "count" of albums

Oct 4, 2011 at 5:30 PM

The code shown on page 134:

        private List<Album> GetTopSellingAlbums(int count)
        {
            // Group to order details by album and return
            // the ablums with highest count
            return  storeDB.Albums
                .OrderByDescending(a => a.OrderDetails.Count())
                .Take(count)
                .ToList();

        }

I found, when no orderdetails records, that it returns the first "count" records from the albums table.  In fact if you remove the .Take(count) it returns the whole thing.  Plus shouldn't the "frequency" of purchases be based on the sum of the quantity.  For us newbie "Entity Framework / Lambda Expression folks" can someone show the more robust query here that would take into account the sum of the Quantity of each orderdetail record as it relates to the album?  All of my attempts come up short and I'm not finding documentation of accomplishing this.  The SQL would be:

with TotAlbumsSold
as (
    select od.AlbumId, sum(od.Quantity) as TotSold
    from OrderDetails od
    group by od.AlbumId 
)

select top 5 a.*
from TotAlbumsSold ts inner join
	Albums a on ts.AlbumId = a.AlbumId
order by ts.TotSold desc

Oct 4, 2011 at 6:19 PM

I read some more and came up with this work around using "sqlquery", but I would still like to see the "robust lambda expression" that does the same thing.

        private List<Album> GetTopSellingAlbums(int count)
        {
            // Group to order details by album and return
            // the ablums with highest count

            //return  storeDB.Albums
            //    .OrderByDescending(a => a.OrderDetails.Count())
            //    .Take(count)
            //    .ToList();

            string MySql;
            MySql = @"with TotAlbumsSold
                    as ( 
                        select od.AlbumId, sum(od.Quantity) as TotSold
                        from OrderDetails od
                        group by od.AlbumId
                        )
                     select a.*
                     from TotAlbumsSold ts inner join
	                 Albums a on ts.AlbumId = a.AlbumId
                     order by ts.TotSold desc";
            return storeDB.Albums
                .SqlQuery(MySql)
                .Take(count)
                .ToList();

        }