Nate Greene Nate Greene - 1 month ago 13
C# Question

Performing multiple Linq queries against the same Linq result

I have created a dashboard that all data displayed on it shares 4 common elements (startDate,endDate,CompanyID,StoreID) that are used as Where clauses in a Linq statement. The result of that statement is then queried in a variety of ways to group and sort the data and used in charts, lists etc. Here is a short snippit to show the duplication that is currently going on:

var dashboardEntity = new BlueStreakSalesDWEntities();

//Get Total Sales
ViewBag.companySalesTotal = dashboardEntity.FactSales.Where(d => d.DateKey >= startDate)
.Where(d => d.DateKey <= endDate)
.Where(c => c.CompanyID == companyID)
.Sum(a => a.Amount);

//get list of all items sold
var companyStoreTotalItem = dashboardEntity.FactSales.Where(d => d.DateKey >= startDate)
.Where(d => d.DateKey <= endDate)
.Where(c => c.CompanyID == companyID).GroupBy(m => new { m.Description })
.Select(g => new DescriptionAmountModel { Amount = g.Sum(a => a.Amount).Value, Description = g.Key.Description })
.OrderByDescending(x => x.Amount);


I have like 15 of these calls on the dashboard and it can get very slow at times from what I imagine are multiple calls when in reality the database only needs to be queried once then that result needs to be queried for different results.

How can I do this?

Any help would be greatly appreciated

Answer

In your current solution each query executes separatly, on the same data. You can first execute the shared parts of the queries and bring the results from database. In your examples it is these where conditions

//Executes in database
var entities = dashboardEntity.FactSales.Where(d => d.DateKey >= startDate)
                                        .Where(d => d.DateKey <= endDate)
                                        .Where(c => c.CompanyID == companyID)
                                        .ToList();

Now that this data is filtered to only what you want you can in memory do the rest of the aggregations:

//Happens in the List<T> in memory
ViewBag.companySalesTotal = entities.Sum(a => a.Amount);

var companyStoreTotalItem = entities.GroupBy(m => new { m.Description })
                                    .Select(g => new DescriptionAmountModel { Amount = g.Sum(a => a.Amount).Value, Description = g.Key.Description })
                                    .OrderByDescending(x => x.Amount);

The different in memory queries you can also wrap with Tasks to execute them in parallel