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

Question

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.

@Jamiec can you please answer this question? – Developer Mister Feb 21 '22 at 20:28 — Developer Mister, Feb 21 '22 at 20:28

sa-es-ir · Answer 1 · 2022-02-21T20:54:48.290

4

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
)

edited Feb 21 '22 at 20:54

answered Feb 21 '22 at 19:48

sa-es-ir

3,722
2
13
31

1

Thanks but It's not a type of "IEnumerable<(string category, IEnumerable products)>" and you did not gave Query sintax solution – Developer Mister Feb 21 '22 at 19:59
1

@DeveloperMister you mean t-sql query? – sa-es-ir Feb 21 '22 at 20:01
Yes, and return type should be "IEnumerable<(string category, IEnumerable products)>" @Saeed Esmaeelinejad – Developer Mister Feb 21 '22 at 20:03
can you please convert your results to "IEnumerable<(string category, IEnumerable products)>" ? @Saeed Esmaeelinejad – Developer Mister Feb 21 '22 at 20:46
2

@DeveloperMister Now that's what you want. – sa-es-ir Feb 21 '22 at 20:55
You gave a type of "new { string category, IEnumerable products }" but I need: ("Meat", new Product[] { new Product { ... }, new Product { ... }, } ), ("Seafood", new Product[] { new Product { ... }, new Product { ... }, } ) @Saeed Esmaeelinejad – Developer Mister Feb 21 '22 at 20:56
tank you for sql solution but query syntax is this: https://static.javatpoint.com/tutorial/linq/images/linq-syntax3.png @Saeed Esmaeelinejad – Developer Mister Feb 21 '22 at 20:58
1

I gave the idea to get what you need and the SO is place to get idea not exact code, the tuple has some limitation maybe this help https://stackoverflow.com/questions/8002455/how-to-easily-initialize-a-list-of-tuples and also you said T-Sql not linq query! – sa-es-ir Feb 21 '22 at 20:59
Anyway thank you very much. your last update solved my problem. Tanks! @Saeed Esmaeelinejad – Developer Mister Feb 21 '22 at 21:02

Astrid E. · Answer 2 · 2022-02-22T18:16:09.670

(Method syntax only)

This is perhaps a somewhat overkill solution, but it lets you traverse productList only once by creating a dictionary where the keys are true (for result1) and false (for result2). Since first posting this answer, I have supplemented it with a second implementation version.

The first version groups products by category according to each Product's Number value (Number == 0 and Number != 0); adhering to your written requirements:

TODO 1: create new tuple collection if 'Number' property equals 0
TODO 2: create new tuple collection if 'Number' property doesn't equals 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.

The second version groups products by category based on whether each category contains any Product with Number == 0, or not; adhering to your requested example outputs:

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

Example fiddle here.

@ Astrid E. No, it's not working at all. please read carefully question and result pease of code — Developer Mister, Feb 21 '22 at 20:04
@DeveloperMister I find that your example results don't fit your written requirements. I tried to match your written requirements, filtering on the `Number` value for each `Product`. — Astrid E., Feb 21 '22 at 20:13
Everything is ok with my question. 'Saeed Esmaeelinejad' has almost answered my question but can't convert to "IEnumerable<(string category, IEnumerable products)> type — Developer Mister, Feb 21 '22 at 20:18
Then we simply disagree on that. For what it's worth, I have added an implementation that adheres to your example outputs. Method syntax only, though. — Astrid E., Feb 22 '22 at 18:17

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

2 Answers2