LINQ and Grouping from a simple SQL DB relationship
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 Tas开发者_高级运维ks.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;
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.
var result=db.Tasks.GroupBy(p=p.TaksTypeID, (tn, tt)=>
new {
Key=tn.Name, TakesTime=>tt.Sum(p=>(p.EndTime-p.StartTime))
});
精彩评论