C # query List to limit the number of children but parent return

Question

I have a nested list structure with Customers → Orders → OrderItems. I am trying to find LINQ or another query that will return Customers and their nested elements, where the quantity OrderItem = 1. However, it should not return any Orders or OrderItems where the quantity! = 1.

I tried this:

var customers2 = customers.Where(c => c.Orders.Any(o => o.OrderItems.Exists(oi => oi.Quantity == 1))); 

It correctly returns only Customers with order elements quantity = 1, but also returns all other orders and order elements.

I can get the desired results with a pair of For-each loops, but I would like to find something more elegant:

  foreach (var customer in customers2) { customer.Orders = customer.Orders.Where(o => o.OrderItems.Exists(oi => oi.Quantity == 1)).ToList(); foreach (var order in customer.Orders) { order.OrderItems = order.OrderItems.Where(oi => oi.Quantity == 1).ToList(); } } 

Here is the structure of the object and some sample data.

 public class Customer { public int CustomerId { get; set; } public string Name { get; set; } public string Address { get; set; } public List<Order> Orders { get; set; } } public class Order { public int OrderId { get; set; } public int CustomerId { get; set; } public DateTime OrderDate { get; set; } public bool Shipped { get; set; } public List<OrderItem> OrderItems { get; set; } } public class OrderItem { public int OrderItemId { get; set; } public int OrderId { get; set; } public string ItemName { get; set; } public int Quantity { get; set; } } var customers = new List<Customer> { new Customer { CustomerId = 1, Name = "Shawn", Address = "123 Main Street", Orders = new List<Order>() { new Order() { OrderId = 100, CustomerId = 1, OrderDate = DateTime.Now, Shipped = true, OrderItems = new List<OrderItem>() { new OrderItem() { OrderItemId = 200, OrderId = 100, ItemName = "Computer", Quantity = 1 }, new OrderItem() { OrderItemId = 206, OrderId = 100, ItemName = "Hard Drive", Quantity = 2 } } }, new Order() { OrderId = 106, CustomerId = 1, OrderDate = DateTime.Now, Shipped = true, OrderItems = new List<OrderItem>() { new OrderItem() { OrderItemId = 207, OrderId = 106, ItemName = "Monitor", Quantity = 3 }, new OrderItem() { OrderItemId = 208, OrderId = 106, ItemName = "DVD Burner", Quantity = 2 } } } } }, new Customer { CustomerId = 2, Name = "Arianna", Address = "456 Main Street", Orders = new List<Order>() { new Order() { OrderId = 101, CustomerId = 2, OrderDate = DateTime.Now.AddDays(-10), Shipped = true, OrderItems = new List<OrderItem>() { new OrderItem() { OrderItemId = 201, OrderId = 101, ItemName = "barbie", Quantity = 2 } } } } }, new Customer { CustomerId = 3, Name = "Ryan", Address = "789 Main Street", Orders = new List<Order>() { new Order() { OrderId = 102, CustomerId = 3, OrderDate = DateTime.Now.AddDays(-5), Shipped = true, OrderItems = new List<OrderItem>() { new OrderItem() { OrderItemId = 203, OrderId = 103, ItemName = "Minecraft", Quantity = 2 } } } } } }; 

Answer 1

You are on the right track with

 var customers2 = customers .Where(c => c.Orders.Any(o => o.OrderItems.Exists(oi => oi.Quantity == 1))); 

You just need an additional step, since you cannot filter orders and customers at the same time, you have already filtered customers to get only those that interest you, now filter the orders themselves

 var customers2 = customers .Where(c => c.Orders.Any(o => o.OrderItems.Exists(oi => oi.Quantity == 1))) .Select(c => c.Orders.Where(o => o.OrderItems(o => o.OrderItems.Exists(oi => oi.Quantity == 1))); 

However, this leaves you with ienumerable ienumerable orders, not customers, but you cannot do exactly what you want (restore customers and change their order property), as this will change their original list of orders, what you can do is create an anonymous type for storage of both orders and customer in your request in the selected form:

 var customers2 = customers .Where(c => c.Orders.Any(o => o.OrderItems.Exists(oi => oi.Quantity == 1))) .Select(c => new { Customer = c, FilteredOrders = c.Orders.Where(o => o.OrderItems(o => o.OrderItems.Exists(oi => oi.Quantity == 1)) }); 

Now you can use it as such

 foreach(var cust in customers2) { cust.Customer // your original Customer object cust.Customer.Orders // your original orders collection for this Customer cust.FilteredOrders // only the orders you're interested in for this customer } 

Answer 2

I assume there is a shorter solution, but this works:

 var goodCusts = new List<Customer>(); foreach(var customer in customers) { var testCust = customer; for (int i = testCust.Orders.Count - 1; i >= 0; i--) { if (testCust.Orders[i].OrderItems.Count != 1) testCust.Orders.RemoveAt(i); } if (testCust.Orders.Any()) goodCusts.Add(testCust); } 

However, he is creating a new collection. It just goes through each customer, deletes any Orders using OrderItems.Count != 1 , then checks to see if this customer has Orders . If so, it is added to the results of the List<Customer> .

Answer 3

Thanks to @StripplingWarrior, I think I came to the answer, although the problem is still not the most elegant:

 var customers2 = customers.Where(x => x.Orders != null && x.Orders.Any(y => y.OrderItems != null && y.OrderItems.Any(z => z.Quantity == 1))); customers2.ToList().ForEach(x => { x.Orders.ForEach(y => { y.OrderItems.RemoveAll(z => z == null || z.Quantity != 1); }); x.Orders.RemoveAll(y => y == null || y.OrderItems.Count == 0); }); return customers2; 

Answer 4

This will lead to the fact that all customers will have specific orders in which the number of order items will be desired. To use this, it will remove all orderitems that do not have one number of items. Therefore, before using this function, you need to clone the list.

  public static List<Customer> GetCustomersWithOrderItemQuantity(List<Customer> customers, int quantity) { var customers2 = customers.TakeWhile(c => c.Orders.Any(o => o.OrderItems.Any(oi => oi.Quantity == quantity))).ToList(); customers2.ForEach(cust => cust.Orders.ForEach(o => o.OrderItems.RemoveAll(oi => oi.Quantity != quantity))); return customers2; } 

You can use, like this, to enter a specific quantity.

 var customers2 = GetCustomersWithOrderItemQuantity(customers, 1); 

If you need all orders in which at least one item has a quantity of 1, use this.

  public static IEnumerable<Customer> GetCustomersWithOrderItemQuantity(List<Customer> customers, int quantity) { return customers.TakeWhile(c => c.Orders.Any(o => o.OrderItems.Any(oi => oi.Quantity == quantity))); } 

The above can be used in the same way as the other, but will display all orders with at least one number of order elements 1 in the above example.