Entity Framework - selective condition for the included navigation property

Question

Suppose I have these simplified generated EF objects ...

public class PurchaseOrder { public int POID {get;set;} public int OrderID {get;set;} public int VendorID {get;set;} public IEnumerable<Order> Orders {get;set;} } public class Order { public int OrderID {get;set;} public decimal Price {get;set;} public IEnumerable<Item> Items {get;set;} } public class Item { public int OrderID {get; set;} public string SKU {get;set;} public int VendorID {get;set;} public Order Order {get;set;} } 

Business logic:

An order can have several POs, one for each individual supplier in the order (suppliers are defined at the element level).

How can I selectively include child objects?

When requesting PO addresses, I want to automatically include child elements for Order and Item.

I accomplish this using Include () ...

 Context.PurchaseOrders.Include("Orders.Items"); 

This does the job and discards related objects, but I want to include Item objects whose VendorID matches the VendorID of the PurchaseOrder .

With traditional SQL, I would just include this in the JOIN clause, but EF builds them inside.

What LINQ magic can I use to tell EF to apply the condition without manually creating a JOIN between entities?

Answer 1

You cannot selectively repel certain child objects that match a specific condition. The best you can do is manually filter the relevant orders yourself.

 public class PurchaseOrder { public int POID {get;set;} public int OrderID {get;set;} public int VendorID {get;set;} public IEnumerable<Order> Orders {get;set;} public IEnumerable<Order> MatchingOrders { get { return this.Orders.Where(o => o.VendorId == this.VendorId); } } } 

Answer 2

You can not. EF does not provide conditions for intensive loading. You should either use multiple queries, for example:

 var pos = from p in context.PurchaseOrders.Include("Order") where ... select p; var items = from i in context.Items join o in context.Orders on new { i.OrderId, i.VendorId} equals new { o.OrderId, o.PurchaseOrder.VendorId } where // same condition for PurchaseOrders select i; 

Or you can use projection in a single query:

 var data = from o in context.Orders where ... select new { Order = o, PurchaseOrder = o.PurchaseOrder, Items = o.Items.Where(i => i.VendorId == o.PurchaseOrder.VendorId) }; 

Answer 3

Here you can use IQueryable-Extensions:

https://github.com/thiscode/DynamicSelectExtensions

The extension builds a dynamically anonymous type. This will be used for design as described by @ Ladislav-Mrnka.

Then you can do this:

 var query = query.SelectIncluding( new List<Expression<Func<T,object>>>>(){ //Example how to retrieve only the newest history entry x => x.HistoryEntries.OrderByDescending(x => x.Timestamp).Take(1), //Example how to order related entities x => x.OtherEntities.OrderBy(y => y.Something).ThenBy(y => y.SomeOtherThing), //Example how to retrieve entities one level deeper x => x.CollectionWithRelations.Select(x => x.EntityCollectionOnSecondLevel), //Of course you can order or subquery the deeper level //Here you should use SelectMany, to flatten the query x => x.CollectionWithRelations.SelectMany(x => x.EntityCollectionOnSecondLevel.OrderBy(y => y.Something).ThenBy(y => y.SomeOtherThing)), });