英文:
Group rows based on an order containing multiple items
问题
以下是您要翻译的内容:
"Whereas I have the following table:
order_number | order_status | item_id | item_name | item_price |
---|---|---|---|---|
123 | awaiting_payment | 222 | beans | 98.76 |
123 | awaiting_payment | 333 | rice | 54.32 |
456 | shipped | 222 | beans | 98.76 |
456 | shipped | 333 | rice | 54.32 |
456 | shipped | 444 | pasta | 76.54 |
789 | canceled | 222 | beans | 98.76 |
And considering that the desired json is as follows:
[
{
"order_number": 123,
"order_status": "awaiting_payment",
"lines": [
{
"item_id": 222,
"item_name": "beans",
"item_price": 98.76
},
{
"item_id": 333,
"item_name": "rice",
"item_price": 54.32
}
]
},
{
"order_number": 456,
"order_status": "shipped",
"lines": [
{
"item_id": 222,
"item_name": "beans",
"item_price": 98.76
},
{
"item_id": 333,
"item_name": "rice",
"item_price": 54.32
},
{
"item_id": 444,
"item_name": "pasta",
"item_price": 76.54
}
]
},
{
"order_number": 789,
"order_status": "canceled",
"lines": [
{
"item_id": 333,
"item_name": "rice",
"item_price": 98.76
}
]
}
]
I'm trying in different ways to reproduce this scenario using C#, but I'm not succeeding...
For this, I declared the following model:
public class Order
{
public int order_number { get; set; }
public string order_status { get; set; }
public List<OrderItems> lines { get; set; }
}
public class OrderItems
{
public int item_id { get; set; }
public string item_name { get; set; }
public decimal item_price { get; set; }
}
To represent the list according to the table, I declared the following model:
public class FullRow
{
public int order_number { get; set; }
public string order_status { get; set; }
public int item_id { get; set; }
public string item_name { get; set; }
public decimal item_price { get; set; }
}
And I added the lines according to the following code:
List<FullRow> row = new List<FullRow>();
row.Add(new FullRow
{
order_number=123,
order_status= "awaiting_payment",
item_id=222,
item_name= "beans",
item_price=98.76M
});
row.Add(new FullRow
{
order_number = 123,
order_status = "awaiting_payment",
item_id = 333,
item_name = "rice",
item_price = 54.32M
});
row.Add(new FullRow
{
order_number = 456,
order_status = "shipped",
item_id = 222,
item_name = "beans",
item_price = 98.76M
});
row.Add(new FullRow
{
order_number = 456,
order_status = "shipped",
item_id = 333,
item_name = "rice",
item_price = 54.32M
});
row.Add(new FullRow
{
order_number = 456,
order_status = "shipped",
item_id = 444,
item_name = "pasta",
item_price = 76.54M
});
row.Add(new FullRow
{
order_number = 789,
order_status = "canceled",
item_id = 222,
item_name = "beans",
item_price = 98.76M
});
Could someone please help me with this task?
Thank you very much in advance.
I've tried several ways, for example according to the following code, but without success:
foreach (Order listItem in row)
{
foreach (OrderItems line in listItem)
{
... my code to create the json...
}
}
英文:
Whereas I have the following table:
order_number | order_status | item_id | item_name | item_price |
---|---|---|---|---|
123 | awaiting_payment | 222 | beans | 98.76 |
123 | awaiting_payment | 333 | rice | 54.32 |
456 | shipped | 222 | beans | 98.76 |
456 | shipped | 333 | rice | 54.32 |
456 | shipped | 444 | pasta | 76.54 |
789 | canceled | 222 | beans | 98.76 |
And considering that the desired json is as follows:
[
{
"order_number": 123,
"order_status": "awaiting_payment",
"lines": [
{
"item_id": 222,
"item_name": "beans",
"item_price": 98.76
},
{
"item_id": 333,
"item_name": "rice",
"item_price": 54.32
}
]
},
{
"order_number": 456,
"order_status": "shipped",
"lines": [
{
"item_id": 222,
"item_name": "beans",
"item_price": 98.76
},
{
"item_id": 333,
"item_name": "rice",
"item_price": 54.32
},
{
"item_id": 444,
"item_name": "pasta",
"item_price": 76.54
}
]
},
{
"order_number": 789,
"order_status": "canceled",
"lines": [
{
"item_id": 333,
"item_name": "rice",
"item_price": 98.76
}
]
}
]
I'm trying in different ways to reproduce this scenario using C#, but I'm not succeeding...
For this, I declared the following model:
public class Order
{
public int order_number { get; set; }
public string order_status { get; set; }
public List<OrderItems> lines { get; set; }
}
public class OrderItems
{
public int item_id { get; set; }
public string item_name { get; set; }
public decimal item_price { get; set; }
}
To represent the list according to the table, I declared the following model:
public class FullRow
{
public int order_number { get; set; }
public string order_status { get; set; }
public int item_id { get; set; }
public string item_name { get; set; }
public decimal item_price { get; set; }
}
And I added the lines according to the following code:
List<FullRow> row = new List<FullRow>();
row.Add(new FullRow
{
order_number=123,
order_status= "awaiting_payment",
item_id=222,
item_name= "beans",
item_price=98.76M
});
row.Add(new FullRow
{
order_number = 123,
order_status = "awaiting_payment",
item_id = 333,
item_name = "rice",
item_price = 54.32M
});
row.Add(new FullRow
{
order_number = 456,
order_status = "shipped",
item_id = 222,
item_name = "beans",
item_price = 98.76M
});
row.Add(new FullRow
{
order_number = 456,
order_status = "shipped",
item_id = 333,
item_name = "rice",
item_price = 54.32M
});
row.Add(new FullRow
{
order_number = 456,
order_status = "shipped",
item_id = 444,
item_name = "pasta",
item_price = 76.54M
});
row.Add(new FullRow
{
order_number = 789,
order_status = "canceled",
item_id = 222,
item_name = "beans",
item_price = 98.76M
});
Could someone please help me with this task?
Thank you very much in advance.
I've tried several ways, for example according to the following code, but without success:
foreach (Order listItem in row)
{
foreach (OrderItems line in listItem)
{
... my code to create the json...
}
}
答案1
得分: 0
`row` 是 `List<FullRow>` 类型。在迭代每个项目时,你不能使用
```csharp
foreach (Order listItem in row)
因为 listItem
是 FullRow
而不是 Order
类型。
根据你的情况,你正在尝试按 order_number
或 (order_number
和 order_status
) 进行分组,你可以使用 System.Linq 中的 .GroupBy()
来进行分组并返回所需的结果。
对于基于方法的语法:
using System.Linq;
var result = row.GroupBy(x => new { x.order_number, x.order_status })
.Select(x => new Order
{
order_number = x.Key.order_number,
order_status = x.Key.order_status,
lines = x.Select(y => new OrderItems
{
item_id = y.item_id,
item_name = y.item_name,
item_price = y.item_price
}).ToList()
})
.ToList();
对于查询语法:
using System.Linq;
var result = (from x in row
group x by new { x.order_number, x.order_status } into g
select new Order
{
order_number = g.Key.order_number,
order_status = g.Key.order_status,
lines = g.Select(y => new OrderItems
{
item_id = y.item_id,
item_name = y.item_name,
item_price = y.item_price
}).ToList()
}
).ToList();
<details>
<summary>英文:</summary>
The `row` is `List<FullRow>` type. While iterating each item, you can't work with
```csharp
foreach (Order listItem in row)
as listItem
is FullRow
but not Order
type.
From your scenario, you are trying to group by order_number
or (order_number
and order_status
), you can work with System.Linq with .GroupBy()
to group and return the desired result.
For method-based syntax:
using System.Linq;
var result = row.GroupBy(x => new { x.order_number, x.order_status })
.Select(x => new Order
{
order_number = x.Key.order_number,
order_status = x.Key.order_status,
lines = x.Select(y => new OrderItems
{
item_id = y.item_id,
item_name = y.item_name,
item_price = y.item_price
}).ToList()
})
.ToList();
For query syntax:
using System.Linq;
var result = (from x in row
group x by new { x.order_number, x.order_status } into g
select new Order
{
order_number = g.Key.order_number,
order_status = g.Key.order_status,
lines = g.Select(y => new OrderItems
{
item_id = y.item_id,
item_name = y.item_name,
item_price = y.item_price
}).ToList()
}
).ToList();
答案2
得分: 0
使用 LINQ 与 GroupBy
:
var groupedRows = row.GroupBy(x => new { x.order_number, x.order_status })
.Select(x => new { x.Key.order_number, x.Key.order_status, lines = x });
解释:
根据 order_number
和 order_status
对行进行分组
获取结果并投影到所需的形状 { "order_number:", "order_status": "", "lines": [] }
英文:
Using linq with GroupBy
var groupedRows = row.GroupBy(x => new { x.order_number, x.order_status })
.Select(x => new { x.Key.order_number, x.Key.order_status, lines = x });
Explanation:
Group the rows on order_number
& order_status
Take the result and project into desired shape { "order_number:"", "order_status":"", "lines":[] }
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论