All geek questions in one place

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

I have three small tables that look like this:

enter image description here

Now I want to get some reports on how many food orders were ordered per day per room.

And I have a ROOM property (it's called AULA ) in the CafeteriaClients table.

I want to indicate how many ordered items were ordered for ROOM. And if OrderedItems from MenuType == EXTRA were ordered in this ROOM, then a list of all clients (Name + Surname) was also ordered. Something like that:

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 have done so far:

    [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);
        }           
    }

, , , LINQ, , SelectMany GroupBy, . , EXTRAS .

    {
      "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"
  }
Hide result

, , LINQ to Entities , . , , 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; }

}
+4
3

,

    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; }
    }

...

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


  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()
         });
+1

:

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 { })

select many, ...

+1

:

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

Source: https://habr.com/ru/post/1676359/

More articles:


All Articles