LINQ Query with GROUP and SUM

Please help me to get my head around querying using LINQ with a GROUP and SUM.

// Query the database
IEnumerable<BestSeller> best_sellers = from bs in (db.MYDATABASE).Take(25)
                                       where bs.COMPANY == "MY COMPANY"
                                       group bs by bs.PRODCODE into g
                                       orderby g.Sum(g.MQTY)
                                       select new BestSeller()
                                       {
                                           product_code = ,
                                           product_description = ,
                                           total_quantity =  
                                      };

I wish to:

  • Take the top 25 items from db.MYDATABASE
  • Group all the results by bs.PRODCODE
  • Order it by the sum total for each bs.PRODCODE
  • Where the company is "MY COMPANY"
  • Then pipe the data in to my BestSeller() objects

I'm confused, because as soon as I add my group in to the mix, my bs variable becomes useless.

Answers


I'm confused, because as soon as I add my group in to the mix, my bs variable becomes useless.

Yes, because you no longer have a single item - you're now processing a sequence of groups of items. You can get at first item for each group, which I assume would be a valid way of getting at the description?

var query =  from bs in db.MYDATABASE.Take(25)
             where bs.COMPANY == "MY COMPANY"
             group bs by bs.PRODCODE into g
             orderby g.Sum(x => x.MQTY)
             select new BestSeller
             {
                 product_code = g.Key,
                 product_description = g.First().DESCRIPTION,
                 total_quantity = g.Sum(x => x.MQTY) 
             };

Note that without specifying an ordering, "the top 25 items from db.MYDATABASE" makes no sense. "Top" in what way? You may well want:

from bs in db.MYDATABASE.OrderByDescending(x => x.Price).Take(25)

or something similar. Note that if none of those have a company of "MY COMPANY" you'll end up with no results...

Or if you want the top 25 bestsellers, you want the "take" part at the very end:

var query =  from bs in db.MYDATABASE
             where bs.COMPANY == "MY COMPANY"
             group bs by bs.PRODCODE into g
             orderby g.Sum(x => x.MQTY) descending
             select new BestSeller
             {
                 product_code = g.Key,
                 product_description = g.First().DESCRIPTION,
                 total_quantity = g.Sum(x => x.MQTY) 
             };
var top25 = query.Take(25);

Need Your Help

ASP .Net Web API RC: Multipart file-upload to Memorystream

c# file-upload asp.net-web-api

I'm trying to save (an) uploaded file(s) to a database/memorystream, but I can't figure it out.

How far did DevExpress get with Javascript refactoring?

javascript refactoring devexpress

Over a year ago, I remember watching one of DevExpress evangelists previewing or at least promoting rich Javascript refactoring (beyond just limited intellisense) within the Visual Studio shell, I ...