Grouping a get items between an timerange

Question

I have this EntityFramework DatabaseContext given:

class Worker
{
    int Id,
    string Name,
    ...
    ICollection 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 = "..." }
        }
    }
}

user_844006 · Answer

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

Grouping a get items between an timerange

1 Answers