.Net Core 3.1. Need Nested Group By item daily Quantity show

Question

I want to nested group by .
I take the order items weekly and group them by type name, then I want to collect the daily sales amount of the product with this type name and get the results as below.
But I am making a mistake in the Linq query I did and I couldn't solve the reason.

  return [
      {
        name: 'Shoes',
        data: [502, 635, 809, 947, 1402, 3634, 5268],
      },
      {
        name: 'Africa',
        data: [106, 107, 111, 133, 221, 767, 1766],
      },
      {
        name: 'tshirt',
        data: [163, 203, 276, 408, 547, 729, 628],
      },
      {
        name: 'pants',
        data: [18, 31, 54, 156, 339, 818, 1201],
      },
      {
        name: 'Oceania',
        data: [2, 2, 2, 6, 13, 30, 46],
      },
    ];

My Code:

   IReadOnlyList<OrderItemReportDto> query = await _context.OrderItems
                                    .GroupBy(x => new { x.ItemOrdered.ProductType })
                                         .Select(y => new OrderItemReportDto
                                         {
                                             Name = y.Key.ProductType,
                                             Quantity = y.Sum(c => c.Quantity),
                                             Count = y.GroupBy(x => x.CreatedTime.Date).Select(x => x.Sum(c => c.Quantity)).ToArray()
                                         })
                                         .ToListAsync();
        return query;

OrderItemReportDto :

   public class OrderItemReportDto
    {
        public string Name { get; set; }
        public int Quantity { get; set; }
        public int[] Count { get; set; }
    }

Order Items :
 

  public class OrderItem : BaseEntity
    {
        public OrderItem()
        {
        }

        public OrderItem(OrderProductItem ıtemOrdered, decimal price, int quantity)
        {
            ItemOrdered = ıtemOrdered;
            Price = price;
            Quantity = quantity;
            CreatedTime = DateTime.Now;
        }

        public OrderProductItem ItemOrdered { get; set; }
        public decimal Price { get; set; }
        public int Quantity { get; set; }
        public DateTime CreatedTime
        {
            get
            {
                return this.dateCreated.HasValue
                   ? this.dateCreated.Value
                   : DateTime.Now;
            }

            set { this.dateCreated = value; }
        }
        private DateTime? dateCreated = null;

    }

but error is :

{
    "StatusCode": 500,
    "Message": "The LINQ expression '(GroupByShaperExpression:\r\nKeySelector: new { ProductType = (t.ItemOrdered_ProductType) }, \r\nElementSelector:(EntityShaperExpression: \r\n    EntityType: OrderItem\r\n    ValueBufferExpression: \r\n        (ProjectionBindingExpression: EmptyProjectionMember)\r\n    IsNullable: False\r\n)\r\n)\r\n    .GroupBy(x => x.CreatedTime.Date)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information."
}

Answer 1

When you use _context.OrderItems.GroupBy, the compiler uses the GroupBy extension method from IQueryable.

When you run your program, the runtime will try to convert LINQ expressions to a SQL query. However, not all LINQ expressions can be converted to a SQL query, and therefore you get an exception.

However, if you are using an IEnumerable, the runtime will not generate a SQL expression. Instead, it will load all the items in memory and then apply your LINQ expressions on those collections.

Try changing to

var query = context.OrderItems.AsEnumerable()
    .GroupBy(x => new {x.ItemOrdered.ProductType})
    .Select(y => new OrderItemReportDto
    {
        Name = y.Key.ProductType,
        Quantity = y.Sum(c => c.Quantity),
        Count = y.GroupBy(x => x.CreatedTime.Date).Select(x => x.Sum(c => c.Quantity)).ToArray()
    })
    .ToList();

Now, bear in mind that this is going to be an expensive operation since you will load all the items in memory and then apply the LINQ clauses. That means that all the fields/column, which you may not want will also be loaded in memory. If you are doing any filter operations using Where, you can try to do them before you convert the query to IEnumerable

I am using ToList() above because ToListAsync() is not available for IEnumerable.

If you do need to use async, you can do this

var query = context.OrderItems.AsEnumerable()
    .GroupBy(x => new {x.ItemOrdered.ProductType})
    .Select(y => new OrderItemReportDto
    {
        Name = y.Key.ProductType,
        Quantity = y.Sum(c => c.Quantity),
        Count = y.GroupBy(x => x.CreatedTime.Date).Select(x => x.Sum(c => c.Quantity)).ToArray()
    }).AsQueryable()
    .ToListAsync();

However, I do not think that this async workaround will get you anything.