How to write aggregate query in LINQ reusing part of the select code

Question

I have a reusable select query method called by two other methods.

These are the sample routines I have in my project. The SalesQry() method is actually very complex in our project.

Private IEnumerable<Sales> SalesQry(SalesDataContext sContext, int customerID)
{
   return 
      from SALE in sContext.Sales
      where SALE.CustomerID = customerID
      select SALE
}

Public List<SalesAGG> SalesByArea()
{
   SalesDataContext oSContext = new SalesDataContext (.....);

  return from SALE in SalesQry(sContext,1230)
              group SALE by
              new
              {
                Type = SALE.SaleType,
                Area = SALE.Area
              }   into aggAREA
             select new  SalesAGG()
             {
                Type = aggAREA.Key.Type,
                Place =  aggAREA.Key.Area,
                TotalSales = aggAREA.Count()
             }.ToList();     
}

Public List<SalesAGG> SalesByState()
{
   SalesDataContext oSContext = new SalesDataContext (.....);

  return from SALE in SalesQry(sContext,1230)
              group SALE by
              new
              {
                Type = SALE.SaleType,
                State = SALE.State
              }   into aggSTATE
             select new  SalesAGG()
             {
                Type = aggSTATE.Key.Type,
                Place =  aggSTATE.Key.State,
                TotalSales = aggSTATE.Count()
             }.ToList();            
}

The problem I am having is, when SalesByState() or SalesByArea() function is executed sql server is not running the aggregate query, instead it is just running this part returning large number of rows

from SALE in sContext.Sales
          where SALE.CustomerID = customerID
          select SALE

and remaining part is being executed inside the application. If I don't call the function SalesQry() and changed the query like below, sql server ran an aggregate query returning very less number of rows.

 from SALE in sContext.Sales
 where SALE.CustomerID = 1230
 group SALE by
  new
  {
     Type = SALE.SaleType,
     State = SALE.State
   }   into aggSTATE
  select new  SalesAGG()
  {
    Type = aggSTATE.Key.Type,
    Place =  aggSTATE.Key.State,
   TotalSales = aggSTATE.Count()
  }.ToList();

I need to share the code because it is very complex and used in many other places. How should I write the query to make the sql server run the entire aggregate query on the server and also use the function SalesQry()

Chris Wenham · Accepted Answer · 2011-04-07 21:56:57Z

3

Edit: It's probably that you're returning IEnumerable<Sales> instead of IQueryable<Sales>. I didn't catch that the first time.

I believe it's being forced to perform the aggregate on the client because it can't run new SalesAGG() on the database server. Try plucking it out with a Let statement, eg:

return from SALE in SalesQry(sContext,1230)
          group SALE by
          new
          {
            Type = SALE.SaleType,
            Area = SALE.Area
          }   into aggAREA
         let totalSales = aggAREA.Count()
         select new  SalesAGG()
         {
            Type = aggAREA.Key.Type,
            Place =  aggAREA.Key.Area,
            TotalSales = totalSales
         }.ToList();

If that isn't working, then my next hunch is that it's the group by clause that groups on a newed-up object. Try using string concatenation as the group by criteria instead, eg:

return from SALE in SalesQry(sContext,1230)
          group SALE by SALE.SaleType + SALE.Area into aggAREA
         select new  SalesAGG()
         {
            Type = aggAREA.First().Type,
            Place =  aggAREA.First().Area,
            TotalSales = aggAREA.Count()
         }.ToList();

edited Apr 7, 2011 at 21:56

answered Apr 7, 2011 at 20:41

Chris Wenham

24.1k13 gold badges62 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

kishore Over a year ago

Thanks Lawrence for your reply. I tried your suggestion, but it didn't work. I also tried removing SalesAGG() and just made it "select new {Type = aggAREA.Key.Type, Place = aggAREA.Key.Area, TotalSales = totalSales}.ToList()" using let totalSales = aggAREA.Count(). It is still returning all the data from the server.

Chris Wenham Over a year ago

See edit. It might work if the group-by is changed to a concatenated string value instead of an anonymous type.

kishore Over a year ago

No luck..It didn't work..The problem doesn't seem to be with groupBy, if I don't use the function SalesQry(sContext,1230), it is working correctly

Chris Wenham Over a year ago

Don't know why I missed this before, but it's because you're returning IEnumerable instead of IQueryable.

kishore Over a year ago

Thanks a TONN Lawrence. That was it. It would take a day for me to find this bug without your help..

Collectives™ on Stack Overflow

How to write aggregate query in LINQ reusing part of the select code

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related