Caching aggregate results in Linq2SQL

Question

We have a small affiliate program and a simple tool to view affiliate statistics.

The Linq for showing the report:

from e in linq0
select new
{
    Id = e.Id,
    Name = e.CompanyName,
    EnquiryCount = (int?)e.CampaignCodes.Sum(f => f.Enquiries.Count()) ?? 0,
    EnquiryOrderSum = (int?)e.CampaignCodes.Sum(f => (int?)f.Enquiries.Sum(g => (int?)g.Orders.Sum(h => h.OrderPrice) ?? 0) ?? 0) ?? 0
    ...
}

Where the calculation of EnquiryCount and EnquiryOrderSum is taking increasingly longer time as the database is growing. (Indexes are defined, I promise).

One idea is to cache EnquiryCount and EnquiryOrderSum to a sepparate table and recalculate these every hour och night.

Is there any way (in linq2sql/asp.net) to generically cache these result without creating a static tables/columns for each propertly?

I'm thinking about creating something like:

EnquiryCount = Cache(g => (int?)e.CampaignCodes.Sum(f => f.Enquiries.Count()) ?? 0, 3600000 /*ms lifetime*/, e.Id/*key*/, "AffiliateService.EnquiryCount"/*property*/)

Where each time called looks for a cached values. But I'm skeptic that this will be very efficient?

Answer 1

Maybe this is useful for you:

Caching LinqToSql results with SqlCacheDependency