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, Jan 17 '12 at 14:55

score 27 · Accepted Answer · edited Nov 27 '15 at 14:53

27

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();
}

edited Nov 27 '15 at 14:53

Marcos Dimitrio

6,651
5
38
62

answered Jan 17 '12 at 14:39

Rich O'Kelly

41,274
9
83
114

2

Doesn't the variable in Any have to be something other than "o" so as not to change the meaning of "o" in parent? – Scott Lawrence Jan 16 '13 at 20:51
@ScottA.Lawrence Yes, you're right - my code snippet wouldn't compile. Answer updated, thanks. – Rich O'Kelly Jan 17 '13 at 09:15

LINQ - filter child collection

1 Answers1

Linked