C# Question
Grouping a get items between an timerange
I have this EntityFramework DatabaseContext given:
class Worker
{
int Id,
string Name,
...
ICollection<Shift> Shifts
}
class Shift
{
int Id,
DateTime ShiftStart
DateTime ShiftEnd
...
Worker Worker
}
class CustomerAction
{
int Id,
Worker Worker,
DateTime ArrivalTime,
int ActionType
...
string Comment
}
Now i want to group all Workers with their Shifts and then get all CustomerActions with ActionType 2 or 4.
Sometimes the Workers dont add their Shifts then all other CustomerActions done by the Worker should listed with empty shift informations.
The Output should look like this:
List =
{
new Item
{
WorkerId = 1, WorkerName = "Worker1" ...
ShiftId = 1, ShiftStart = 10/1/2017 1:00:00 AM, ShiftEnd = 10/1/2017 5:00:00 AM ...
// Sorted by ArrivalTime, only in between the ShiftStart / ShiftEnd Range, done by this Worker
CustomerActions =
{
new CustomerAction { ActionId = 1, ActionType = 4, ArrivalTime = 10/1/2017 1:00:00 AM, Comment = "My comment" }
new CustomerAction { ActionId = 2, ActionType = 2, ArrivalTime = 10/1/2017 1:30:00 AM, Comment = "Some other comment" }
new CustomerAction { ActionId = 3, ActionType = 4, ArrivalTime = 10/1/2017 2:00:00 AM, Comment = "No comment" }
}
}
new Item
{
WorkerId = 2, WorkerName = "Worker2" ...
ShiftId = null, ShiftStart = null, ShiftEnd = null ...
// Sorted by ArrivalTime, done by this Worker, Without an Shift
CustomerActions =
{
new CustomerAction { ActionId = 4, ActionType = 4, ArrivalTime = 10/2/2017 1:00:00 AM, Comment = "..." }
new CustomerAction { ActionId = 5, ActionType = 2, ArrivalTime = 10/3/2017 1:30:00 AM, Comment = "..." }
new CustomerAction { ActionId = 6, ActionType = 4, ArrivalTime = 10/4/2017 2:00:00 AM, Comment = "..." }
}
}
}
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
If I understood your question correctly, you don't need grouping. Instead, you need to expand, for each workers shift the actions worked upon. The code below should point you to the right direction:
var registeredShifts = dbContext.Workers
.SelectMany(w => w.Shifts.Select(s => new
{
WorkerId = w.Id,
WorkerName = w.Name,
ShiftId = s.Id,
ShiftStart = s.ShiftStart,
ShiftEnd = s.ShiftEnd,
CustomerActions = dbContext.CustomerActions
.Where(a => a.Worker.Id == w.Id &&
a.ArrivalTime >= s.ShiftStart &&
a.ArrivalTime <= s.ShiftEnd &&
(a.ActionType == 2 || a.ActionType == 4))
.ToList()
}))
EDIT: To the same results for actions outside the registered shifts you'll have to use grouping.
var outsideShifts = dbContrxt.CustomerActions
.Where(a => a.ActionType == 2 || a.ActionType == 4)
.Where(a => a.Worker.Shifts.All(s => a.ArrivalTime < s.ShiftStart ||
a.ArrivalTime > s.ShiftEnd))
.GroupBy(a => new
{
WorkerId = a.Worker.Id,
WorkerName = a.Worker.Name
})
.Select(g => new
{
WorkerId = g.Key.WorkerId,
WorkerName = g.Key.WorkerName,
ShiftId = null,
ShiftStart = null,
ShiftEnd = null,
CustomerActions = g.ToList()
});
Finally, to get the required data, Union() the results above:
var result = registeredShifts.Union(outsideShifts);
return result.ToArray();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download