public void LoadMonthCategorySummary()
{
List<monthsummary> MonthSummaryList;
using ( budgetEntities = new BudgetEntities() )
{
var initial = from m in budgetEntities.SpendingSet
join mc in budgetEntities.MthCategorySet on m.MthCategory.MthCategoryId equals mc.MthCategoryId
join c in budgetEntities.CategorySet on mc.Category.CategoryId equals c.CategoryId
where m.DateSpent.Month == DateTime.Now.Month && m.DateSpent.Year == DateTime.Now.Year
select new
{
CategoryName = c.CategoryName,
BudgetAmount = mc.BudgetAmount,
AmountSpent = m.Amount
};
var list = from i in initial
group i by i.CategoryName into g
orderby g.Key ascending
select new
{
CategoryName = g.Key,
AmountSpent = g.Sum( ms => ms.AmountSpent ),
MonthSummary = g.FirstOrDefault()
};
MonthSummaryList = ( from ms in list
select new MonthSummary
{
CategoryName = ms.CategoryName,
AmountSpent = ms.AmountSpent,
BudgetAmount = ms.MonthSummary.BudgetAmount
} ).ToList();
}
gvMonthSummary.DataSource = MonthSummaryList;
gvMonthSummary.DataBind();
}As you can see this is not your simple CRUD functionality with joins across three different tables.
So what approach did I take to resolve this?
Firstly, you need to understand that each data service is dependent on a corresponding repository. For example, the SpendingsDataService is dependent on IRepository<Spending>.
My first approach was to try and expose the datacontext, but it occurred to me that this was actually breaking the repository pattern.
The only other alternative was to introduce a second IRepository<T> object, which meant that I needed to create a second constructor.
The code I ended up with looks something like this...
public IList<MonthSummary> GetMonthCategorySummary( DateTime analysisDate )
{
IList<MonthSummary> mcSummaryList;
using ( sRepo )
{
using ( mcRepo )
{
var initial = from s in sRepo.Query()
join mc in mcRepo.Query() on s.MthCategoryId equals mc.MthCategoryId
where s.DateSpent.Month == analysisDate.Month && m.DateSpent.Year == analysisDate.Year
select new
{
CategoryName = mc.Category.CategoryName,
BudgetAmount = mc.BudgetAmount,
AmountSpent = s.Amount
};
var list = from i in initial
group i by i.CategoryName into g
orderby g.Key ascending
select new
{
CategoryName = g.Key,
AmountSpent = g.Sum( ms => ms.AmountSpent ),
MonthSummary = g.FirstOrDefault()
};
mcSummaryList = ( from ms in list
select new MonthSummary
{
CategoryName = ms.CategoryName,
AmountSpent = ms.AmountSpent,
BudgetAmount = ms.MonthSummary.BudgetAmount
} ).ToList();
}
}
return mcSummaryList;
}
