LINQ to split row column value into different rows in .net

Question

Suppose I have data retrieved from an oracle database in the following format

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

I need to change this in the following format using linq.Need to separate the product column with a comma while preserving the other columns.

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

Answer 1

Please try the following:

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

EDIT:

The 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 compressProducts is just the initial list of products from your first example.

Answer 2

I know 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 })); } 

In turn, SqlResult is your result set from the database.

Answer 3

This seems 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 for only one line, but it can be easily expanded to include multiple lines ....

Answer 4

I would prefer a simple foreach because it is known than LINQ is slower than the usual loop instructions, but if you really want to go this route, you can use something like what may be easier for you to read:

Considering

  class ProductOracle { public int SNo { get; set; } public string Product { get; set; } public decimal Cost { get; set; } } class ProductEntity { public int SNo { get; set; } public string Product { get; set; } public decimal Cost { get; set; } } 

Execution

  var entities = new List<ProductOracle> { new ProductOracle{SNo=1,Product="colgate,closeup,pepsodent", Cost=50}, new ProductOracle{SNo=2,Product="rin,surf", Cost=100} }; 

WITH

  var products = new List<ProductEntity>(); entities.ForEach(element => { element.Product.Split(',').ToList().ForEach(product => { products.Add(new ProductEntity { SNo = element.SNo, Product = product, Cost = element.Cost }); }); }); 

or

  var products = entities.SelectMany(element => { var ProductEntities = new List<ProductEntity>(); element.Product.Split(',').ToList().ForEach(product => { ProductEntities.Add(new ProductEntity { SNo = element.SNo, Product = product, Cost = element.Cost }); }); return ProductEntities; });