C#Linq_根据条件创建新的产品元组集合

Question

假设我有一个类：

class Product
{
public int Id { get; set; }
public string Name { get; set; }
public string Category { get; set; }
public double Price { get; set; }
public int Number { get; set; }
}

和类对象列表：

List<Product> productList = new()
{
new Product { Id = 1, Name = "Pasta", Category = "Beverages", Price = 18.0, Number = 39 },
new Product { Id = 2, Name = "Anchius", Category = "Beverages", Price = 19.0, Number = 0 },
new Product { Id = 3, Name = "Syrup", Category = "Condiments", Price = 10.0, Number = 13 },
new Product { Id = 4, Name = "Seasoning", Category = "Condiments", Price = 22.0, Number = 53 },
new Product { Id = 5, Name = "Gumbo", Category = "Condiments", Price = 21.35, Number = 41 },
new Product { Id = 6, Name = "Spread", Category = "Condiments", Price = 25.0, Number = 120 },
new Product { Id = 7, Name = "Dried", Category = "Confections", Price = 30.0, Number = 0 },
new Product { Id = 8, Name = "Bread", Category = "Confections", Price = 16.0, Number = 14 },
new Product { Id = 9, Name = "Sauce", Category = "Condiments", Price = 40.0, Number = 20 },
new Product { Id = 10, Name = "Niku", Category = "Meat", Price = 97.0, Number = 29 },
new Product { Id = 11, Name = "Niku", Category = "Meat", Price = 34.0, Number = 61 },
new Product { Id = 12, Name = "Ragoo", Category = "Seafood", Price = 31.0, Number = 31 }
};

TODO 1:如果"Number"属性等于0，则创建新的元组集合。元组的第一个元素应该是"；类别"；以及第二系列产品。返回类型：

IEnumerable<(string category, IEnumerable<Product> products)> result1

结果应该是：

("Beverages",
new Product[]
{
new Product { Id = 1, Name = "Pasta", Category = "Beverages", Price = 18.0, Number = 39 },
new Product { Id = 2, Name = "Anchius", Category = "Beverages", Price = 19.0, Number = 0 },
}
),
("Confections",
new Product[]
{
new Product { Id = 7, Name = "Dried", Category = "Confections", Price = 30.0, Number = 0 },
new Product { Id = 8, Name = "Bread", Category = "Confections", Price = 16.0, Number = 14 },
}
)

TODO 2:如果"Number"属性不等于0，则创建新的元组集合。元组的第一个元素应该是"；类别"；以及第二系列产品。返回类型：

IEnumerable<(string category, IEnumerable<Product> products)> result2

结果应该是：

("Condiments",
new Product[]
{
new Product { Id = 3, Name = "Syrup", Category = "Condiments", Price = 10.0, Number = 13 },
new Product { Id = 4, Name = "Seasoning", Category = "Condiments", Price = 22.0, Number = 53 },
new Product { Id = 5, Name = "Gumbo", Category = "Condiments", Price = 21.35, Number = 41 },
new Product { Id = 6, Name = "Spread", Category = "Condiments", Price = 25.0, Number = 120 },
}
),
("Meat",
new Product[]
{
new Product { Id = 10, Name = "Niku", Category = "Meat", Price = 97.0, Number = 29 },
new Product { Id = 11, Name = "Niku", Category = "Meat", Price = 34.0, Number = 61 },
}
),
("Seafood",
new Product[]
{
new Product { Id = 12, Name = "Ragoo", Category = "Seafood", Price = 31.0, Number = 31 },
}
)

请帮忙。请给出查询语法和方法语法解决方案。

Answer 1

您可以简单地使用这个linq:

var result1 = productList
.GroupBy(x => x.Category, (category, products) => new List<(string Category, IEnumerable<Product> Products)>
{ (category, products) }
).Where(x => x[0].Products.Any(s => s.Number == 0)).Select(x => x[0]);
var result2 = productList
.GroupBy(x => x.Category, (category, products) => new List<(string Category, IEnumerable<Product> Products)>
{ (category, products) }
).Where(x => x[0].Products.All(s => s.Number != 0)).Select(x => x[0]);

T-Sql查询有点棘手，请记住，必须将T-Sql结果转换为元组模型，这就是查询：

CREATE TABLE #product(Id INT, Name NVARCHAR(50), Category NVARCHAR(50),Price DECIMAL(10,1), Number INT)
INSERT INTO #product
(
Id,
Name,
Category,
Price,
Number
)
VALUES
(   1,N'Pasta',N'Beverages',18.0,39),
(   2,N'Anchius',N'Beverages',19.0,0),
(   3,N'Syrup',N'Condiments',10,13),
(   4,N'Seasoning',N'Condiments',22,53),
(   5,N'Gumbo',N'Condiments',18.0,41),
(   6,N'Spread',N'Condiments',18.0,120),
(   7,N'Dried',N'Confections',18.0,0),
(   8,N'Bread',N'Confections',18.0,14),
(   9,N'Sauce',N'Condiments',18.0,20),
(   10,N'Niku',N'Meat',18.0,29),
(   11,N'Niku',N'Meat',18.0,61),
(   12,N'Ragoo',N'Seafood',18.0,31)
--get product with 0 nubmers
SELECT * FROM #product WHERE Category IN (
SELECT x.Category FROM (
SELECT *,CASE Number WHEN
0 THEN 1 ELSE 0 END NewNumber FROM #product) x
GROUP BY x.Category
HAVING SUM(x.NewNumber)>0
)
--get product without 0 nubmers
SELECT * FROM #product WHERE Category IN (
SELECT x.Category FROM (
SELECT *,CASE Number WHEN
0 THEN -1 ELSE 0 END NewNumber FROM #product) x
GROUP BY x.Category
HAVING SUM(x.NewNumber)=0
)

Answer 2

(仅方法语法(

这可能是一个有些过头的解决方案，但它只允许您通过创建一个字典来遍历productList一次，其中键为true(用于result1(和false(用于result2(。自从第一次发布这个答案以来，我已经用第二个实现版本对其进行了补充。

---

第一个版本根据每个Product的Number值(Number == 0和Number != 0(按类别对产品进行分组；遵守您的书面要求：

TODO 1：如果"Number"属性等于0，则创建新元组集合
TODO 2：如果"Number"属性不等于0 ，则新建元组集合

IDictionary<bool, IEnumerable<( string category, IEnumerable<Product> products)>> results = productList
.GroupBy(
product => product.Number == 0,
( isZero, products ) => ( 
NumberIsZero: isZero, 
ProductsByCategory: products.GroupBy(
product => product.Category,
( category, products ) => ( category, products ))))
.ToDictionary(item => item.NumberIsZero, item => item.ProductsByCategory);
var result1 = results[true];
var result2 = results[false];

小提琴的例子。

---

第二个版本根据每个类别是否包含Product和Number == 0来按类别对产品进行分组；遵循您要求的示例输出：

IDictionary<bool, IEnumerable<( string Category, IEnumerable<Product> Products )>> results = productList
.GroupBy(product => product.Category,
( category, products ) => ( Category: category, Products: products ))
.GroupBy(productsByCategory => productsByCategory.Products.Any(p => p.Number == 0),
( containsZero, productsByCategory ) => ( ContainsZero: containsZero, ProductsByCategory: productsByCategory ))
.ToDictionary(
gr => gr.ContainsZero,
gr => gr.ProductsByCategory);
var result1 = results[true];
var result2 = results[false];

小提琴的例子。