Ordering and Filtering Associated EntityCollections during Loading

Ordering and Filtering Associated

EntityCollections during Loading

LINQ to SQL provides the LoadOptions.AssociateWith( LambdaExpression ) directive to enable filtering

and ordering of associated entity collections at the DataContext level. EF offers an EntityInstance

. EntityCollection. Attach( EntityType.EntityCollection . CreateSourceQuery()

. LambdaExpression ) method. Unfortunately, filtering and ordering must be done for each instance of

the top - level object in a foreach iterator loop; if the object graph has three levels or more, the number of

iterators is one less than the depth of the hierarchy.

It ’ s a common practice to work from the topmost to the bottommost entity in the hierarchy. For example,

the following top - down snippet returns the Customer - Order - Order_Detail hierarchy with last five

Order entities and Order_Details EntitySet s for each Customer entity in Brazil:

C# 3.0

txtQuery.Text = “SELECT VALUE c FROM NorthwindEntities.Customers “ +

“AS c WHERE c.Country = ‘Brazil’; “

List < Customer > custList = new List < Customer > ();

using (NorthwindEntities ocNwind = new NorthwindEntities(“name=NorthwindEntities”))

{

ObjectQuery < Customer > customerQuery =

new ObjectQuery < Customer > (txtQuery.Text, ocNwind, mergeOpt);

foreach (Customer customer in customerQuery)

{

// Get the last five orders for each customer

customer.Orders.Attach(customer.Orders.CreateSourceQuery()

.OrderByDescending(o = > o.OrderID).Take(5));

foreach (Order order in customer.Orders)

{

order.Order_Details.Attach(order.Order_Details.CreateSourceQuery());

}

custList.Add(customer);

}

}

The preceding approach is drastic overkill to return the same object graph (less materialized Employee

and Shipper EntityRefs ) as the preceding examples. The only modification of the earlier eSQL query

to return the identical result more than five times faster is emphasized here:

C# 3.0

txtQuery = “SELECT VALUE o FROM NorthwindEntities.Orders AS o “ +

“WHERE o.Customer.Country = ‘Brazil’;”;

The performance - crushing culprits are the two loops required to obtain the last five Orders for each

customer and each of the customer ’ s Order_Details Entity Set . The moral of the story is always

start from the middle ground, preferably with the entity requiring a Where constraint, when returning

multiple copies of three or higher tiered object graphs.

Clearing the check box runs the following top - down implementation that uses the

Include( “ QueryPath ” ) directive for the two lower layers:

C# 3.0

List < Customer > custList = null;

txtQuery.Text = “SELECT VALUE c FROM NorthwindEntities.Customers “ +

“AS c WHERE c.Country = ‘Brazil’; “

using (NorthwindEntities ocNwind = new NorthwindEntities(“name=NorthwindEntities”))

{

// Enable eager loading of all Orders and Order_Details with Include operators

ObjectQuery < Customer > customerQuery = new ObjectQuery < Customer > (txtQuery.Text,

ocNwind, mergeOpt).Include(“Orders”).Include(“Orders.Order_Details”);

// Materialize the sequence

custList = customerQuery.ToList();

}

This implementation executes three times faster than the two - loop version, while delivering all Order s

and Order_Detail s for each customer in a 705 KB DCS XML document, which includes the associated

Employees and Shippers entities. The 362 KB document for the two - loop version doesn ’ t include the

extra associated entities.