Menu
I have a nested list structure with Customers -> Orders -> OrderItems. I am trying to find a LINQ or other query that will return the Customers and their nested items where the OrderItem quantity = 1. However, it should not return any Orders or OrderItems where the quantity != 1.
I have 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 items quantity = 1, but it also returns all other Orders and Order Items as well.
I can get the desired results with a couple 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 object structure 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
}
}
}
}
}
};
480
About the query The query we're going to run performs a basic search of the code for if statements that are redundant, in the sense that they have an empty then branch. For example, code such as: if (error) { }
So in summary, C is a language used to give commonly-understood commands to any arbitrary CPU while SQL is a language used to give commonly-understood commands to any arbitrary database back-end.
A query function is a mathematical expression evaluated against each item returned by a query, and whose output is stored in a dynamic, temporary field generated at query time.
You can code SQL statements in a C or C++ program wherever you can use executable statements. Each SQL statement in a C or C++ program must begin with EXEC SQL and end with a semicolon (;). The EXEC and SQL keywords must appear on one line, but the remainder of the statement can appear on subsequent lines.
You're on the right path with
var customers2 = customers
.Where(c => c.Orders.Any(o => o.OrderItems.Exists(oi => oi.Quantity == 1)));
You just need an additional step as you can't filter the orders and the customers at the same time, you already filtered the customers to get only those you're interested in, 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)));
This however leaves you with an ienumerable of ienumerable of orders, and not the customers, but you can't do exactly what you want (retrieve the customers and have their order property changed) as that would change their original order list, what you can do is create an Anonymous type to store both the orders and Customer in your query in the select as such:
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 this 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
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
