LINQ to Entities query to flatten results and select and group desired information

Question

I have three small tables that look as follows:

Now I want to have some reports regarding how many meal items were ordered for a day per ROOM.

And I have the ROOM property (it is named AULA) in the CafeteriaClients table.

I want to list how many OrderedItems were ordered per ROOM. And if there were OrderedItems of MenuType == EXTRA ordered in that ROOM then list all the Customers (Name+Surname) who ordered it also. Something like this:

ROOM 1
          PASTA: 12
          STEAK: 13
          SALAD: 12
           EXTRAS:
             TIRAMISU: 12
                 UserName 1
                 UserName 2
                 ....
                 UserName 12
             MACEDONIA: 2
                 UserName 3
                 UserName 4
 ROOM 2
          .....
          ......

What I've done so far is this:

    [HttpGet]
    public IHttpActionResult GetOrdersForRooms()
    {
        using (var ctx = new CafeteriaContext())
        {
            var Date = DateTime.Today;

            var orders = ctx.CafOrders.Where(d => d.Date == Date)
                .SelectMany(o => o.OrderedItems
                .Select(c => new { 
                            Room = c.CafClient.AULA, 
                            MealItem = c.Name, 
                            Type = c.MenuType, 
                            ClientName = c.CafClient.Name }))
                 .ToList();



            return Ok(orders);
        }           
    }

And this brings me results in the following form, which is Okay but I am sure there is MORE to LINQ and there must be a way by better using SelectMany or GroupBy which I did not find. Right now I have to go over the list, read each object's properties and the arrange them by ROOMS and EXTRAS and so on.

    {
      "Room": "Aula 2",
      "MealItem": "Pizza Italiana",
      "Type": "EXTRA",
      "ClientName": "Riki Gervais"
    }, 
    {
      "Room": "Aula 3",
      "MealItem": "Spaghetti",
      "Type": "EXTRA",
      "ClientName": "John M Meyer"
    },
    {
      "Room": "Aula 3",
      "MealItem": "Sausage",
      "Type": "INTERNAL",
      "ClientName": "Steve O'Dwayer"
  },
  {
      "Room": "Aula 3",
      "MealItem": "Pasta",
      "Type": "EXTRA",
      "ClientName": "Allan Parker"
  },
  {
      "Room": "Aula 6",
      "MealItem": "Riggatoni",
      "Type": "EXTERNAL",
      "ClientName": "John Susack"
  }

I then filter these results and arrange them in the desired form but I wonder if there can be done more with LINQ to Entities from the start and get information grouped without redundancy. For example get results directly grouped per ROOMS.

    [Table("CafOrders")]
public class CafOrders
{
    [Key]
    [Required]
    public int Id { get; set; }

    [Required]
    public DateTime Date { get; set; }

    public string Note { get; set; }

    public decimal Total { get; set; }

    public int ClientId { get; set; }

    [ForeignKey("ClientId")]
    public CafeteriaClients CafeteriaClient { get; set; }

    public ICollection<OrderedItems> OrderedItems { get; set; }

}



   [Table("CafeteriaClients")]
public class CafeteriaClients
{
    [Key]
    [Required]
    public int Id { get; set; }

    [Required]
    public string Name { get; set; }

    [Required]
    public string Surname { get; set; }

    public string AULA { get; set; }

    public string MATR { get; set; }

    [Required]
    public string CustomerType { get; set; }

    public string GRUPPO { get; set; }


    public ICollection<OrderedItems> OrderedItems { get; set; }
}




    public class OrderedItems
{

    public int Id { get; set; }

    public int OrderId { get; set; }

    public int ClientId { get; set; }

    public string Name { get; set; }

    public decimal Price { get; set; }

    public string Description { get; set; }

    public string MenuType { get; set; }

    [ForeignKey("OrderId")]
    public CafOrders CafOrder { get; set; }

    [ForeignKey("ClientId")]
    public CafeteriaClients CafClient { get; set; }

}

Answer 1

Make a class pattern like you want

    public class Room
    {
        public string Name { get; set; }

        public List<Meal> Meals { get; set; }
    }

    public class Meal
    {
        public string Name { get; set; }
        public int Count { get; set; }

        public List<EXTRA> Extras { get; set; }
     }

    public class EXTRA
    {
        public string Name { get; set; }

        public List<User> UserNames { get; set; }
    }

    public class User
    {
        public string Name { get; set; }

        public int Count { get; set; }
    }

and then try this...

  var groupOrders = CafOrders.GroupBy(x => x.CafeteriaClient.AULA)
        .Select(x => new Room
         {
             Name = x.Key,
             Meals = x.SelectMany(y => y.OrderedItems)
                      .GroupBy(y => y.Name)
                      .Select(z => new Meal
         {
           Name = z.Key,
           Count = z.Count(),
           Extras = z.Where(t => t.MenuType == "EXTRA")
                     .GroupBy(t => t.Name)
                     .Select(t => new EXTRA
         {
            Name = t.Key,
            UserNames = t.GroupBy(k => new { k.CafClient.Name, 
                                             k.CafClient.Surname 
            })
            .Select(k => new User {
            Name = k.Key.Name + k.Key.Surname, 
             Count = k.Count() }).ToList()
            }).ToList()
           }).ToList()
         });

Update

  public class Meal
  {
        public string Name { get; set; }
        public int Count { get; set; }
  }

 public class Room
 {
        public string Name { get; set; }

        public List<Meal> Meals { get; set; }

        public List<EXTRA> Extras { get; set; }

  }

   var groupOrders = CafOrders.GroupBy(x => x.CafeteriaClient.AULA)
        .Select(x => new Room
         {
             Name = x.Key,
             Meals = x.SelectMany(y => y.OrderedItems)
                      .Where(y => y.MenuType != "EXTRA")
                      .GroupBy(y => y.Name)
                      .Select(z => new Meal
                       {
                           Name = z.Key,
                           Count = z.Count()
                       }).ToList(),
             Extras = x.SelectMany(y => y.OrderedItems)
                      .Where(y => y.MenuType == "EXTRA")
                      .GroupBy(y => y.Name)
                      .Select(z => new EXTRA
                      {
                          Name = z.Key,
                          Count = z.Count(),
                          UserNames = z.GroupBy(k => new { k.CafClient.Name, 
                                                           k.CafClient.Surname })
                                       .Select(k => new User {
                                           Count = k.Count(), 
                                           Name = k.Key.Name + k.Key.Surname })
                                         .ToList()

                      }).ToList()
         });              

Answer 2

I change my answer based on your data structure:

var cafOrders = new CafOrders
{
    OrderedItems = new List<OrderedItems>
    {
        new OrderedItems
        {
            CafClient = new CafeteriaClients { AULA = "Aula 2", Name = "Riki ", Surname = "Gervais" },
            Name = "Pizza Italiana",
            MenuType = "EXTRA"
        },
        new OrderedItems
        {
            CafClient = new CafeteriaClients { AULA = "Aula 3", Name = "John ", Surname = "M Meyer" },
            Name = "Spaghetti",
            MenuType = "EXTRA"
        },
        new OrderedItems
        {
            CafClient = new CafeteriaClients { AULA = "Aula 3", Name = "Steve", Surname = "O'Dwayer" },
            Name = "Sausage",
            MenuType = "EXTRA"
        },
        new OrderedItems
        {
            CafClient = new CafeteriaClients { AULA = "Aula 3", Name = "Allan", Surname = "Parker" },
            Name = "Pasta",
            MenuType = "EXTRA"
        },
        new OrderedItems
        {
            CafClient = new CafeteriaClients { AULA = "Aula 6", Name = "John", Surname = "Susack" },
            Name = "Riggatoni",
            MenuType = "EXTERNAL"
        }
    }
};

var orders = cafOrders.OrderedItems.GroupBy(o => o.CafClient.AULA)
            .Select(g => new
            {
                Room = g.First().CafClient.AULA,
                NumberOfRooms = g.Count(),
                Clients = g.Where(x => x.MenuType == "EXTRA").Select(e => e.CafClient.Name),
                MealItemToCount = g.GroupBy(m => m.Name).ToDictionary(k => k.First().Name, v => v.Count())
            })
            .ToList();
        }

Answer 3

You could just:

var orders = ctx.CafOrders.Where(d => d.Date == Date)
            .GroupBy(n => new { n.OrderedItems, n.(any other field you want to group}).Select(n => new { })

Or you could use select many and then group by the same way...