C# Linq _ Create new tuple collection of products according to conditions-CodePudding

let's say I have a class:

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

and list of class objects:

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: create new tuple collection if 'Number' property equals 0. The first element of tuple should be "Category" and second - array of Products. return type:

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

result should be:

("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: create new tuple collection if 'Number' property doesn't equals 0. The first element of tuple should be "Category" and second - array of Products. return type:

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

result should be:

("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 },
    }
)

Please help. Please give both Query syntax and Method syntax solutions.

CodePudding user response：

You can simply use this 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]);

The T-Sql query is a little tricky and remember you must turn t-sql result to your tuple model, this is the query:

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
)

CodePudding user response：

This is perhaps a somewhat overkill solution (for the method syntax only), but it lets you traverse productList only once by creating a dictionary where the keys are true (Number == 0) and false (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];

Example fiddle here.