How to get nested chain of objects in Linq and MVC2 application?

I am getting all confused about how to solve this problem in Linq. I have a working solution, 开发者_如何学Gobut 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?

UPDATE:

I think I'm looking for something along these lines: How do I create a nested group-by dictionary using LINQ?. If it is something like this, I'd really appreciate some help translating it into my problem, because I tried but failed. But I may be off on this strategy (to be honest it too seems more complex than I would have expected this sort of query to be), and if so, please tell me!

UPDATE 2: Regarding the link above, that doesn't seem to be quite right either, because when I try the example from Jon Skeet (literally, not translated to my problem) I don't get the Dictionary type he specifies at all... The compiler says it can't convert it. And I need to have a specific type to return, since this result must be passed to the View as a ViewModel.

---

Since the response hasn't been overwhelming :-) I'll attempt an answer myself. It's still not quite satisfactory in my own opinion, because I have to create a bunch of intermediate helper classes to hold the values in the ViewModel. So I'd be happy to accept another answer, if anyone can show me a better way to do it.

Anyway, what I have done is I created a ViewModel with a few classes to hold the filtered objects:

public class MonthlyReportViewModel
    {
        public List<CurrentProject> Projects { get; set; }
        public string Employee { get; set; }
        public Customer Customer { get; set; }
        public int TotalHours { get; set; }
        public int TotalCost { get; set; }
        public double TotalCostAndVAT { get; set; }
        public double VAT { get; set; }

        public string Month { get; set; }
        public string StartDate { get; set; }
        public string EndDate { get; set; }
    }

    public class CurrentProject
    {
        public string Name { get; set; }
        public List<CurrentTask> CurrentTasks { get; set; }
    }

    public class CurrentTask
    {
        public string Name { get; set; }
        public List<TimeSegment> CurrentTimeSegments { get; set; }
        public int Cost { get; set; }
        public int Hours { get; set; }
        public int Fee { get; set; }
    }

And then I have the same method as before to get the filtered TimeSegments:

    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();
    }

And finally I created a new GetProjects() method to group and return filtered projects based on the filtered timesegments:

    public List<CurrentProject> GetProjects(List<TimeSegment> timeSegments)
    {
        IEnumerable<CurrentProject> currentProjects = from timeSegment in timeSegments
                         group timeSegment by timeSegment.Task.Project
                             into projectTimeSegments
                             select new CurrentProject()
                                        {
                                            Name = projectTimeSegments.Key.ProjectName,
                                            CurrentTasks = (from projectTimeSegment in projectTimeSegments
                                                            group projectTimeSegment by projectTimeSegment.Task
                                                                into taskTimeSegments
                                                                let fee = taskTimeSegments.Key.Fee
                                                                let hours = taskTimeSegments.Sum(t=>t.Hours)
                                                                select new CurrentTask
                                                                           {
                                                                               Name = taskTimeSegments.Key.TaskName,
                                                                               Fee = fee,
                                                                               Cost = hours*fee,
                                                                               Hours = hours,
                                                                               CurrentTimeSegments =
                                                                                   taskTimeSegments.ToList()
                                                                           }).ToList()
                                        };
        return currentProjects.ToList();
    }

Is this the best way to achieve this or am I still overcomplicating things?

---

i think that best way for resolve this is create store procedure that will return all needed data such as Project Task TimeSegment. Something like this :

select * from Projects where ...
select * from Tasks where ...
select * from TimeSegment where ...

Also you can use LoadWith opportunity.