Dividing entities into groups

Question

I have a LINQ entity framework query that returns something like this:

{Start: 1/1/2000T18:00:00, FirstName: John, LastName: Doe},
{Start: 1/1/2000T18:00:00, FirstName: Bob, LastName: Doe},
{Start: 1/1/2000T18:00:00, FirstName: Jack, LastName: Doe},

{Start: 1/1/2000T22:00:00, FirstName: John, LastName: Doe},
{Start: 1/1/2000T22:00:00, FirstName: Bob, LastName: Doe},
{Start: 1/1/2000T22:00:00, FirstName: Jack, LastName: Doe},

{Start: 2/1/2000T10:00:00, FirstName: John, LastName: Doe},
{Start: 2/1/2000T10:00:00, FirstName: Bob, LastName: Doe},
{Start: 2/1/2000T10:00:00, FirstName: Jack, LastName: Doe},

{Start: 2/1/2000T14:00:00, FirstName: John, LastName: Doe},
{Start: 2/1/2000T14:00:00, FirstName: Bob, LastName: Doe},
{Start: 2/1/2000T14:00:00, FirstName: Jack, LastName: Doe},

e.g shifts of people in different days and different times.

I want to create an object where the shifts are divided into days and hours like so:

1/1/2000:
[
    18:00:00:
    [
        {FirstName: John, LastName: Doe},
        {FirstName: Bob, LastName: Doe},
        {FirstName: Jack, LastName: Doe}
    ]
]

etc...

Is there a convenient way to do that with LINQ to entities?

Thanks!

Answer 1

You can use "group by" to group by multiple properties, but you first need to extract out the date and time portions of the Start. You could group first by date and then by time, or a syntactically simpler way is to group by both and then group the inner collection by the time:

var x = from shift in Shifts
                    let date = EntityFunctions.TruncateTime(shift.Start)
                    let time = EntityFunctions.CreateTime(shift.Start.Hour, shift.Start.Minute, shift.Start.Second)
                    group shift by new { date, time } into groupedByDate
                    select new
                    {
                       Date = groupedByDate.Key.date,
                       ShiftsByTime = from g in groupedByDate group g by groupedByDate.Key.time
                    };

You could also just group by pairs of {date, time} if you don't care about the hierarchy:

  var x = from shift in Shifts
                let date = EntityFunctions.TruncateTime(shift.Start)
                let time = EntityFunctions.CreateTime(shift.Start.Hour, shift.Start.Minute, shift.Start.Second)
                group shift by new {date, time};

This is a more verbose but semantically equivalent query to the first one above. I checked the generated SQL and it's more or less equivalent:

var x = from shift in Shifts
                let date = EntityFunctions.TruncateTime(shift.Start)
                group shift by date into groupedByDate
                select new
                       {
                           Date = groupedByDate.Key,
                           ShiftsByTime = from g in groupedByDate
                               let time = EntityFunctions.CreateTime(g.Start.Hour, g.Start.Minute, g.Start.Second)
                               group g by time
                       };

Answer 2

You need to use GroupBy to nest your results...

I posted my whole test app for testing purposes

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.Script.Serialization;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var objs = new Shift[] {
                new Shift {Start= DateTime.Parse("1/1/2000 18:00:00"), FirstName= "John", LastName= "Doe"},
                new Shift {Start= DateTime.Parse("1/1/2000 18:00:00"), FirstName= "Bob", LastName= "Doe"},
                new Shift {Start= DateTime.Parse("1/1/2000 18:00:00"), FirstName= "Jack", LastName= "Doe"},
                new Shift {Start= DateTime.Parse("1/1/2000 22:00:00"), FirstName= "John", LastName= "Doe"},
                new Shift {Start= DateTime.Parse("1/1/2000 22:00:00"), FirstName= "Bob", LastName= "Doe"},
                new Shift {Start= DateTime.Parse("1/1/2000 22:00:00"), FirstName= "Jack", LastName= "Doe"},
                new Shift {Start= DateTime.Parse("2/1/2000 10:00:00"), FirstName= "John", LastName= "Doe"},
                new Shift {Start= DateTime.Parse("2/1/2000 10:00:00"), FirstName= "Bob", LastName= "Doe"},
                new Shift {Start= DateTime.Parse("2/1/2000 10:00:00"), FirstName= "Jack", LastName= "Doe"},
                new Shift {Start= DateTime.Parse("2/1/2000 14:00:00"), FirstName= "John", LastName= "Doe"},
                new Shift {Start= DateTime.Parse("2/1/2000 14:00:00"), FirstName= "Bob", LastName= "Doe"},
                new Shift {Start= DateTime.Parse("2/1/2000 14:00:00"), FirstName= "Jack", LastName= "Doe"}
            };

            var dates = objs
                .GroupBy(i => i.Start.Date)
                .Select(i => new
                {
                    Date = i.Key,
                    Times = i.GroupBy(s => s.Start.TimeOfDay).Select(s => new
                    {
                        Time = s.Key,
                        People = s.Select(ss => new { ss.FirstName, ss.LastName })
                    })
                });

            Console.ReadLine();
        }
    }

    class Shift
    {
        public DateTime Start { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }
}

Answer 3

Try to use .Select and .GroupBy like this:

var res = list.Select(x => new 
                        {
                            Person = x,
                            Date = x.Start.Date,
                            Time = x.Start.TimeOfDay
                        })
                .GroupBy(x => x.Date)
                .Select(personGroupByDate => new 
                {
                    Date = personGroupByDate.Key,
                    Persons = personGroupByDate
                        .Select(x => x.Person)
                        .GroupBy(x => x.Time)
                        .Select(person => new 
                        {
                            FirstName = person.FirstName,
                            LastName = person.LastName
                        }).ToList()
                }).ToList()