How to write aggregate query in LINQ reusing part of the select code
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 开发者_JS百科= 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()
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();
精彩评论