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.

Answer 2

The default setting for Entity Framework is to allow lazy loading and dynamic proxies.

And in this case when you are using the virtual keyword on the relational properties these 'should' (in case you have not disabled it in EF) load with Lazy Loading.

Lazy Loading Loads the relational properties when you need it. Example:

var load = data.Orders.OrderDetails.Tolist() // Would load all OrderDetails to a list.

//Below would load all OrderDetails that has a OrderId smaller than 5
var loadSpecific = data.Orders.Where(x=> x.OrderId < 5).OrderDetails.ToList() 

---

The case you are describing is Eager Loading('Include' statements), Nothing wrong with it. But if you are planning on using it I would consider using below syntax instead. This would give compilation error if you decide to change the name of the relational property.

var load = data.Orders
.Include(x => x.OrderDetails)
.Include(x => x.Categories)

---

I suggest you take 10-15 minutes of time and read up on it in this article: https://msdn.microsoft.com/en-us/data/jj574232.aspx

Answer 3

If you are using EF Core this might help you:

using (var context = new BloggingContext())
{
    var filteredBlogs = context.Blogs
        .Include(blog => blog.Posts.Where(post => post.BlogId == 1))
        .ThenInclude(post => post.Author)
        .Include(blog => blog.Posts)
        .ThenInclude(post => post.Tags.OrderBy(postTag => postTag.TagId).Skip(3))
        .ToList();
}

https://learn.microsoft.com/pt-br/ef/core/querying/related-data/eager#filtered-include