LINQ and Grouping from a simple SQL DB relationship

Question

I have two tables with the following layout and relationships:

Tasks:

TaskID    StartTime    EndTime    TaskTypeID    ProductionID
------------------------------------------------------------
1         12:30        14:30      1             1
2         14:30        15:30      2             1
3         11:10        13:40      2             2
4         10:25        15:05      1             2


TaskTypes:

TaskTypeID    Name
---------------------------------------------
1             Hardware Development
2             Software Development

The relationship is:

Primary key in TaskTypes.TaskTypeID and foreign key in Tasks.TaskTypeID.

The same with the ProductionID (i've left out the table layout): Primary key in Productions.ProductionID and foreign key in Tasks.ProductionID.

What i would like todo is receive a grouped list that displays all the tasks for each task type for a certain production. I guess this is pretty simple but i just can't get it to work with LINQ.

The query is used to display all the TaskTypes for a certain production along with the sum of the total time used for each TaskType for that production.

I use LINQ to SQL auto-generated classes in C#.

I tried this:

var = from TaskType in db.TaskTypes
      join Task in db.Tasks on TaskType.TaskTypeID equals Tasks.TaskTypeID
      where Task.ProductionID == p.ProductionID
      group TaskType by TaskType.TaskTypeID;

Answer 1

It feels to me like you want to group and then join:

from task in db.Tasks
where task.ProductionID = p.ProductionID
group task by task.TaskTypeID into grouped
select new { TaskTypeID = grouped.Key,
             TotalTime = grouped.Sum(x => x.EndTime - x.StartTime) } into total
join task in db.Tasks on sum.TaskTypeID equals total.TaskTypeID
select new { TaskType = task.TaskType, TotalTime = total.TotalTime };

I think that's at least fundamentally right - but it may be tricky in terms of the EndTime - StartTime part.

Answer 2

var result=db.Tasks.GroupBy(p=p.TaksTypeID, (tn, tt)=>
new {
 Key=tn.Name, TakesTime=>tt.Sum(p=>(p.EndTime-p.StartTime))
});