C# Linq: Cross Apply Inner List with Outer List to create Duplicates

Question

I want to Cross Apply an Inner List with an Outer List, to generate duplicate rows.

ProductSales List has nested Customer List.

List<ProductSales>
  List<Customer> --  within ProductSales is nested List

Each customer needs to be separated as 1. Original and Final result is below. How can this be done?

Original:

ProductSale   ProductName  Date        Customer      ProductItems
1             Car         5/1/2019     Tom, Sally    tires, engine

New List:

ProductSale   ProductName  Date        Customer      ProductItems
1             Car         5/1/2019     Tom           tires, engine
1             Car         5/1/2019     Sally         tires, engine

Note: Product Sales contains single members like Date, and other nested list members like Product Items. Its only to be separated on Customer.

• Order and data structure must be preserved in original ProductSale class, just Customer items are separated.
• Its coming in as IQueryable, and also be kept as IQueryable, so Paging/Sorting can occur later.
• Last, I have 70 columns so trying to prevent retyping all of them if possible.

--

 public ProductSales  
 {

   public int ProductName {get; set;}
   public DateTime Date {get; set;}
   public List<string> Customers {get; set;}
   public List<string> ProductItems {get; set;} 
   public float SalesCost {get; set;}
   + 70 other members..
 }

question from:https://stackoverflow.com/questions/66054431/c-sharp-linq-cross-apply-inner-list-with-outer-list-to-create-duplicates

Answer 1

SelectMany is the key of such query. Without more information, it is what you can get:

var query = 
   from ps in ctx.ProductSales
   from c in ps.Customers
   select new 
   {
      ProductSale = ps.Id,
      ps.ProductName,
      ps.Date,
      Customer = c,
      ProductItems = ps.ProductItems.ToList()
   }