How do I group data from the database in a DTO?

Question

How do I group data from the database in a dto?

For example I want my output to be like this:

{
    id: "random-id-tst",
    years: [
        {
            year: 1,
            productIds: ['guid-id-1', 'guid-id-2'],
        },
        {
            year: 2,
            productIds: ['guid-id-3'],
        }
    ]
}

But I am struggling and tried many things and got to this point:

{
    id: "random-id-tst",
    years: [
        {
            year: 1,
            productIds: ['guid-id-1'],
        },
        {
            year: 1,
            productIds: ['guid-id-2'],
        },
        {
            year: 2,
            productIds: ['guid-id-3'],
        },
    ]
}

public class Order
{
    public Guid ID{ get; set; }

    public ICollection<OrderLine> OrderLines { get; set; }
}

public class OrderLine
{ 
    public Guid OrderID { get; set; }
    public Order Order { get; set; }
    public Guid ProductID { get; set; }
    public Product Product { get; set; }
    public int Year { get; set }
}

public class Product
{
    public Guid ID { get; set; }        
    public ICollection<OrderLine> OrderLines { get; set; }
}

 public class OrderDto
    {
        public Guid ID { get; set; }
        public IEnumerable<YearsDto> Years { get; set; }
    }

public class YearDto
{
    public int Year { get; set; }
    public ICollection<Guid> ProductIds { get; set; }
}

I tried many things but get different errors each time.

This is what I did:

var orderLines = (await _context.OrderLine
    .Where(s => s.orderID == orderId)
    .ToListAsync()).GroupBy(ol => ol.Year);


var order = (await _context.Order
    .Where(order => order.ID == orderId)
    .Select(x =>
        new OrderDto()
        {
            ID = order.ID,
            Years = orderLines.Select(ss => new YearsDto()
            { 
                Year = 1,
            }),
        }
    ).FirstAsync());


return order;

As you can see I hardcoded Year and didnt include productIds because for some reason I cant reference ss.Year or ss.Product, also when I run this, it gives me an error: InvalidOperationException: variable '__Select_1' of type 'System.Collections.Generic.IEnumerable`1[YearsDto]' referenced from scope '', but it is not defined

Can someone point me in the right direction on how to solve my problem?

Answer 1

This code should get you the result you need:

            var order = _context.OrderLine
                .Include(o => o.OrderLines)
                .Where(s => s.Id == orderId)
                .First();

            var orderDto = new OrderDto()
            {
                ID = order.Id,
                Years = order.OrderLines.ToLookup(ol => ol.Year).Select(g => new YearDto() {Year = g.Key, ProductIds = g.ToList().Select(x => x.ProductID).ToList()})
            };