I am getting all confused about how
to solve this problem in
Linq. I have a working solution, but the code
to do it is way too complicated and circular I think:
I have a timesheet application in MVC 2. I want
to query the database that has the following tables (simplified):
Project
Task
TimeSegment
The relationships are as follows: A project can have many tasks and a task can have many timesegments.
I need
to be able
to query this in different ways. An example is this: A View is a report that will show a list of projects in a table. Each project's tasks will be listed followed by a Sum of the number of hours worked on that task. The timesegment object is what holds the hours.
Here's the View:
<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Report.Master" Inherits="System.Web.Mvc.ViewPage<Tidrapportering.ViewModels.MonthlyReportViewModel>" %>
<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
Månadsrapport
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<h1>
Månadsrapport</h1>
<div style="margin-top: 20px;">
<span style="font-weight: bold">Kund: </span>
<%: Model.Customer.CustomerName %>
</div>
<div style="margin-bottom: 20px">
<span style="font-weight: bold">Period: </span>
<%: Model.StartDate %> - <%: Model.EndDate %>
</div>
<div style="margin-bottom: 20px">
<span style="font-weight: bold">Underlag för: </span>
<%: Model.Employee %>
</div>
<table class="mainTable">
<tr>
<th style="width: 25%">
Projekt
</th>
<th>
Specifikation
</th>
</tr>
<% foreach (var project in Model.Projects)
{
%>
<tr>
<td style="vertical-align: top; padding-top: 10pt; width: 25%">
<%:project.ProjectName %>
</td>
<td>
<table class="detailsTable">
<tr>
<th>
Aktivitet
</th>
<th>
Timmar
</th>
<th>
Ex moms
</th>
</tr>
<% foreach (var task in project.CurrentTasks)
{%>
<tr class="taskrow">
<td class="task" style="width: 40%">
<%: task.TaskName %>
</td>
<td style="width: 30%">
<%: task.TaskHours.ToString()%>
</td>
<td style="width: 30%">
<%: String.Format("{0:C}", task.Cost)%>
</td>
</tr>
<% } %>
</table>
</td>
</tr>
<% } %>
</table>
<table class="summaryTable">
<tr>
<td style="width: 25%">
</td>
<td>
<table style="width: 100%">
<tr>
<td style="width: 40%">
Totalt:
</td>
<td style="width: 30%">
<%: Model.TotalHours.ToString() %>
</td>
<td style="width: 30%">
<%: String.Format("{0:C}", Model.TotalCost)%>
</td>
</tr>
</table>
</td>
</tr>
</table>
<div class="price">
<table>
<tr>
<td>Moms: </td>
<td style="padding-left: 15px;">
<%: String.Format("{0:C}", Model.VAT)%>
</td>
</tr>
<tr>
<td>Att betala: </td>
<td style="padding-left: 15px;">
<%: String.Format("{0:C}", Model.TotalCostAndVAT)%>
</td>
</tr>
</table>
</div>
</asp:Content>
Here's the action method:
[HttpPost]
public ActionResult MonthlyReports(FormCollection collection)
{
MonthlyReportViewModel vm = new MonthlyReportViewModel();
vm.StartDate = collection["StartDate"];
vm.EndDate = collection["EndDate"];
int customerId = Int32.Parse(collection["Customers"]);
List<TimeSegment> allTimeSegments = GetTimeSegments(customerId, vm.StartDate, vm.EndDate);
vm.Projects = GetProjects(allTimeSegments);
vm.Employee = "Alla";
vm.Customer = _repository.GetCustomer(customerId);
vm.TotalCost = vm.Projects.SelectMany(project => project.CurrentTasks).Sum(task => task.Cost); //Corresponds
to above foreach
vm.TotalHours = vm.Projects.SelectMany(project => project.CurrentTasks).Sum(task => task.TaskHours);
vm.TotalCostAndVAT = vm.TotalCost * 1.25;
vm.VAT = vm.TotalCost * 0.25;
return View("MonthlyReport", vm);
}
And the "helper" methods:
public List<TimeSegment> GetTimeSegments(int customerId, string startdate, string enddate)
{
var timeSegments = _repository.TimeSegments
.Where(timeSegment => timeSegment.Customer.CustomerId == customerId)
.Where(timeSegment => timeSegment.DateObject.Date >= DateTime.Parse(startdate) &&
timeSegment.DateObject.Date <= DateTime.Parse(enddate));
return timeSegments.ToList();
}
public List<Project> GetProjects(List<TimeSegment> timeSegments)
{
var projectGroups = from timeSegment in timeSegments
group timeSegment by timeSegment.Task
into g
group g by g.Key.Project
into pg
select new
{
Project = pg.Key,
Tasks = pg.Key.Tasks
};
List<Project> projectList = new List<Project>();
foreach (var group in projectGroups)
{
Project p = group.Project;
foreach (var task in p.Tasks)
{
task.CurrentTimeSegments = timeSegments.Where(ts => ts.TaskId == task.TaskId).ToList();
p.CurrentTasks.Add(task);
}
projectList.Add(p);
}
return projectList;
}
Again, as I mentioned, this works, but of course is really complex and I get confused myself just looking at it even now that I'm coding it. I sense there must be a much easier way
to achieve what I want. Basically you can tell from the View what I want
to achieve:
I want
to get a collection of projects. Each project should have it's associated collection of tasks. And each task should have it's associated collection of timesegments for the specified date period. Note that the projects and tasks selected must also only be the projects and tasks that have the timesegments for this period. I don't want all projects and tasks that have no timesegments within this period.
It seems the group by
Linq query beginning the GetProjects() method sort of achieves this (if extended
to have the conditions for date and so on), but I can't return this and pass it
to the view, because it is an anonymous object. I also tried creating a specific type in such a query, but couldn't wrap my head around that either...
I hope there is something I'm missing and there is some easier way
to achieve this, because I need
to be able
to do several other different queries as well eventually.
I also don't really like the way I solved it with the "CurrentTimeSegments" properties and so on. These properties don't really exist on the model objects in the first place, I added them in partial classes
to have somewhere
to put the filtered results for each part of the nested object chain...
Any ideas?
