LINQ - filter child collection

Question

I'd like to be able to query parent entities and filter the contents of a child collection.

For example, I have a collection of OrderHeaders. I want to query this collection using LINQ to return all OrderHeaders, but I only want some of the related OrderDetail rows to be included.

I am preferably looking for a solution where I can do all of this in a single LINQ statement.

The following console app demonstrates this.

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace LINQ { class Program { static void Main(string[] args) { List<OrderHeader> orders = GetOrderHeaders(); var filteredOrders = from p in orders where p.Detail.Where(e => e.StockCode == "STK2").Count() > 0 select p; foreach (var order in filteredOrders) { Console.WriteLine("Account {0} ", order.AccountCode); foreach (var detail in order.Detail) { Console.WriteLine("StockCode {0}, Quantity {1}", detail.StockCode, detail.Quantity); } Console.WriteLine(); } // The above will return the following: // Account CUST1 // StockCode STK1, Quantity 1 // StockCode STK2, Quantity 2 // // Account CUST2 // StockCode STK2, Quantity 1 // StockCode STK4, Quantity 2 // How can I get the following? // Account CUST1 // StockCode STK2, Quantity 2 // // Account CUST2 // StockCode STK2, Quantity 1 Console.ReadLine(); } public static List<OrderHeader> GetOrderHeaders() { List<OrderHeader> orders = new List<OrderHeader>(); OrderHeader header = new OrderHeader { AccountCode = "CUST1", Detail = new List<OrderDetail>()}; header.Detail.Add( new OrderDetail { StockCode = "STK1", Quantity = 1 }); header.Detail.Add( new OrderDetail { StockCode = "STK2", Quantity = 2 }); orders.Add(header); header = new OrderHeader { AccountCode = "CUST2", Detail = new List<OrderDetail>() }; header.Detail.Add( new OrderDetail { StockCode = "STK2", Quantity = 1 }); header.Detail.Add( new OrderDetail { StockCode = "STK4", Quantity = 2 }); orders.Add(header); return orders; } } public class OrderHeader { public string AccountCode { get; set; } public List<OrderDetail> Detail {get; set;} } public class OrderDetail { public string StockCode { get; set; } public int Quantity { get; set; } } }

Many thanks in advance for any suggestions.

Paul

I should give more background to this question. In actual fact I am using LINQ to Entity Framework where I am generating the LINQ statement using the Dynamic Expression API (as the result of the user building up a search criteria with a UI). Because of this I would prefer to do it on one LINQ statement. — P2l
– P2l, Commented Jan 17, 2012 at 14:55

Marcos Dimitrio · Accepted Answer · 2015-11-27 14:53:41Z

Try the following:

var filtered = orders .Where(o => o.Detail.Any(d => d.StockCode == "STK2")) .Select(o => new { Order = o, Details = o.Detail.Where(d => d.StockCode == "STK2") });

Which is then useable like so:

foreach (var entity in filtered) { Console.WriteLine("Account {0} ", entity.Order.AccountCode); foreach (var detail in entity.Details) { Console.WriteLine("StockCode {0}, Quantity {1}", detail.StockCode, detail.Quantity); } Console.WriteLine(); }

Doesn't the variable in Any have to be something other than "o" so as not to change the meaning of "o" in parent?
@ScottA.Lawrence Yes, you're right - my code snippet wouldn't compile. Answer updated, thanks.

Collectives™ on Stack Overflow

LINQ - filter child collection

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related