LINQ to separate column value of a row to different rows in .net

Question

Consider i have a datatable retrieved from oracle database in the following format

SNo.  |      Product                      |  Cost
-------------------------------------------------
1     |      colgate,closeup,pepsodent    |  50
2     |      rin,surf                     |  100

I need to change this into the following format using linq.Need to separate the product column with the help of comma by keeping the other columns same.

SNo.  |        Product      |   Cost
-------------------------------------
1     |        colgate      |    50
1     |        closeup      |    50
1     |        pepsodent    |    50
2     |        rin          |    100
2     |        surf         |    100

Answer 1

Please try this:

List<Product> uncompressedList = compressedProducts
    .SelectMany(singleProduct => singleProduct.ProductName
                                    .Split(',')
                                    .Select(singleProductName => new Product 
                                    { 
                                        SNo = singleProduct.SNo, 
                                        ProductName = singleProductName, 
                                        Cost = singleProduct.Cost 
                                    }))
    .ToList();

EDIT:

Product class is defined as follows:

public class Product
{
    public Int32 SNo { get; set; }
    public String ProductName { get; set; }
    public Int32 Cost { get; set; }
}

and compressedProducts is just the initial list of products from your first example.

Answer 2

I know that this is not a single-line Linq statement but, try this.

var output = new List<Product>();

foreach (var p in SqlResult)
{
    var products = p.Product.Split(',');
    output.AddRange(products.Select(product => new Product { SNo = p.SNo, ProductName = product, Cost = p.Cost }));
}

By-the-way, SqlResult is your results set from the database.

Answer 3

This appears to work from my limited testing...

var test = p1.Product.Split(',').Select(p => new { product = p }).Select(r => new { p1.SNo, r.product, p1.Cost, })

This is only for a single line, but can easily be explanded to include mutiple lines....