Linq to Entities filter navigation collection properties

Question

I have an order class that has two navigation properties of type collections; OrderDetails and Categories. There is a one to many relationship between Order and both OrderDetail and Category. An Order may or may not have a Category associated to it. An OrderDetail record has a CustomerID field.

I am trying to retrieve a list of Orders that have categories associated to them and their corresponding OrderDetail records for a specific customer. I want to achieve this using linq to entities if possible.

public class order
{
    public order()
    {
        OrderDetails = new list<OrderDetail>();
        Categories = new list<Category>();
    }
    public int OrderID { get; set; }
    public DateTime OrderDate { get; set; }
    public virtual List<OrderDetail> OrderDetails { get; set; }
    public virtual List<Category> Categories{ get; set; }
}

public class OrderDetail
{
    public int OrderDetailID { get; set; }
    public int CustomerID { get; set; }
    public virtual Order Order { get; set; }
}

public class Category
{
    public int CategoryID { get; set; }
    public string CategoryName { get; set; }
    public virtual Order Order { get; set; }
}

I can get it to work if I start with the OrderDetail entity first as shown below but how would I write this if I want to start with the Order entity first?

var query = from od in _dbCtx.OrderDetails
                .Include("Order")
                .Include("Order.Categories")
                where od.CustomerID == custID && od.Order.Categories.Count > 0
                select od;

Answer 1

You can try this:

var query =_dbCtx.Orders.Include("OrderDetails")
                        .Include("Categories")
                        .Where(o=>o.Categories.Count>0)
                        .SelectMany(o=>o.OrderDetails.Where(od=>od.CustomerID == custID));

The key in this query is the SelectMany extension method, which is used to flatten the Where's result into one single collection.

Edit 1

Due to you have disabled lazy loading, the Order navigation property in the OrderDetails that you get when you execute my query are null. One option could be using the Load method when you use the result:

foreach(var od in query)
{
   // Load the order related to a given OrderDetail
   context.Entry(od).Reference(p => p.Order).Load();

   // Load the Categories related to the order
   context.Entry(blog).Collection(p => p.Order.Categories).Load();
}

Another option could be returning an anonymous type:

var query =_dbCtx.Orders.Include("OrderDetails")
                        .Include("Categories")
                        .Where(o=>o.Categories.Count>0)
                        .SelectMany(o=>o.OrderDetails.Where(od=>od.CustomerID == custID).Select(od=>new {Order=o,OrderDetail=od}));

But I don't like anyone of these solutions.The most direct way is the query that you already had from the beginning.