Using Load Operator to Lazy Load Collection and entity reference and entity reference

Console.WriteLine("Category:{0} TotalProducts:{1}",

beverage.CategoryName,beverage.Products.Count());

On the above code, I am retrieving the Category and its related products.

Since I am only interested in related products where supplierid equal to one, I am apply the where to filter the Products returned for a given category. Then using the Select operator, I retrieve the first category from the anonymous types. Similarly to retrieve the Products I use SelectMany operator to fetch the product collection from the anonymous type. To build the complete graph in memory I attach the products retrieved from the anonymous type to the product collection of the category. This is one of the ways you can return a partial collection for products for a given category.

3.2 Using Load Operator to Lazy Load Collection and entity reference

Problem: You have navigation properties on your class. Some of the properties are collection and some are entity reference. You want to know how to use Load operator to lazy load navigation properties.

Solution: Unlike linq to sql, entity framework requires every database operation to be explicit. When you access a navigation property such as an entity reference or entity collection, by default relationships are not loaded. In the case of entity reference when it’s not loaded, the object would be set to null. In the case of entity collection, the collection would be empty. To load either of the navigation properties you have to call Load method on entity reference or entity collection. Load method is designed to always make a database call regardless if the collection or reference is loaded or not. To prevent reloading a collection that is already loaded, you can check for IsLoaded property which would be set to true once the collection is loaded.

Code below shows example of using load method to load orders for a customer and also loading entity reference customer for a given order.

Listing 1-1

var db = new LazyLoadingEntities();

Console.WriteLine("Total database calls " + databasecalls);

Discussion: In the above code, I am checking to see if orders for ALFKI customer are loaded by calling IsLoaded property. Since this is the first time I am loading the orders, IsLoaded property returns false and a database call is made to fetch the orders for ALFKI customer. After orders entity collection is populated, IsLoaded property is set to true. This way if the method is called second time around, Load will not get called.

In Listing 1-1, I am also loading Orders with ship city of London. Order entity exposes a navigation relationship Customer which by default is not loaded. I am loading the customer only if the customer has not been loaded before. Checking for isloaded is very crucial in this case because there are 33 orders returned from the query. However those 33 orders only belong to 5 unique customers. Therefore we really do not want to make 33 database calls.

By checking the IsLoaded property we only make 5 database calls that equal the unique customers for the 33 orders retrieved from the query.

Following example covers various usages of Load in different scenarios.

var db = new LazyLoadingEntities();

var ALFKI = db.Customers.First(c => c.CustomerID == "ALFKI");

ALFKI.Orders.Load();

//confirms the count is 0

Console.WriteLine("after reloading " + ALFKI.Orders.Count());

In the above example, I am clearing all the orders for ALFKI customer. To discard changes and reload again from database, I use Load again. However, the count value for orders still remains 0. The reason is, if source entity customer, is being tracked, calling Load on Orders collection uses

MergeOption of AppendOnly. If you have made changes to collection on the client side such as changing a property on an order, adding new orders or removing an order, those changes will be preserved and will not get overwritten by Load Call. To make sure that we take changes from the

database and overwrite the changes we have made on the client side, we must call Load with OverWriteChanges. Following code use Load with

MergeOption set to OverWriteChanges.

Listing 1-2

//we need to reload our orders and discard our client changes.

ALFKI.Orders.Load(MergeOption.OverwriteChanges);

//confirm total orders is greater than 0 Console.WriteLine("reload with overwrite " + ALFKI.Orders.Count());

In Listing 1-2, after calling Load with OverWriteChanges option, I confirm the count for orders is greater than 0 and only contains orders as defined by the database.

If you makes changes to properties on the Order and call Load with

AppendOnly option, your changes will stay intact. In listing 1-3 I am calling Load with AppendOnly option and therefore property changes on my Order entity is not lost.

Listsing 1-3

var ALFKIorder = ALFKI.Orders.Single(o => o.OrderID == 10643);

ALFKIorder.ShipCity = "London";

Console.WriteLine("existing order's city changed to London");

ALFKI.Orders.Load(MergeOption.AppendOnly);

//ship city still remains as London

Console.WriteLine("After load with appendly only existing order city " + ALFKIorder.ShipCity);

In listing 1-4, I am grabbing order id 10643 for alfki customer and assigning it to ANTARA customer. This results in ALFKI customer’s orders collection to reduce by 1 and ANTARA’s order collection to increase by 1. When I call Load on ALFKI customer to reload its order collection using

OverWriteChanges it not only fixes the orders for ALFKI but also removes the order that we assigned earlier to ANTARA customer since the order 10643 once again belongs to ALFKI as shown in the code below.

Listing 1-4

var ALFKIorder = ALFKI.Orders.Single(o => o.OrderID == 10643);

//assign the alfki order to anatr customer ALFKIorder.Customer = ANATR;

//alfki orders reduces by 1

Console.WriteLine("ALFKI orders after assigning order to ANATR " + ALFKI.Orders.Count());

ALFKI.Orders.Load(MergeOption.OverwriteChanges);

//alfki ordres is fixed and increase by 1

If you have a scenario where you have loaded part of the orders for a

customer and later you decide that you need to get all the order for customer, you can call Load with AppendOnly option which will append all the orders that were previously not present in the collection. It will not overwrite any existing order in the collection. In Listing 1-5, I am retrieving part of the order by attaching orders with ship freight less than and equal to 20. Later I am loading rest of the collection from the database by calling Load.

Listing 1-5

//loading only pat of the orders.

ANATR.Orders.Attach(ANATR.Orders.CreateSourceQuery().Where(o

=> o.Freight <= 20));

Console.WriteLine("Antarr orders partly loaded " + ANATR.Orders.Count());

//calling load will load the entire graph.

ANATR.Orders.Load(MergeOption.OverwriteChanges);

Console.WriteLine("ANATR orders after load with overwrite "

+ ANATR.Orders.Count());

MergeOptions has another option that I have not covered which is No

Tracking. When you use No Tracking, you’re hinting that entity or collection you want to load should not be tracked. You can only use No Tracking option in Load when the source entity is also loaded with No Tracking option.

Listing 1-6 demonstrates using Load with No Tracking.

Listing 1-6

var ANTON = db.Customers.First(c => c.CustomerID == "ANTON");

//code crashes because you cant load related entity with no

//AROUT.Orders.Load(MergeOption.OverwriteChanges);

//works because customer was retrieved using no tracking as well.

AROUT.Orders.Load(MergeOption.NoTracking);

In Listing 1-6, when I load orders for anton with NoTracking option, I get an exception because the source query customer was loaded with tracking option an therefore Loading orders with NoTracking is not allowed. Entity

framework will only allow navigation relations to be loaded with No Tracking when source entity is also loaded with No Tracking option. When I change the query for AROUT customer to use NoTracking option and call Load, I get no errors. The reason I don’t get exception is because by default Load uses the same option that was used to retrieve the source entity customer. Since I fetched customer using no Tracking option, Load uses NoTracking option to retrieve orders as well. You also have the ability to call Load with explicit Merge option of NoTracking which is same as calling Load with no options.

If you have loaded an entity collection such as orders with no tracking, calling Load again will raise an exception stating that collection loaded with

NoTracking cannot be reloaded. It is defined as one of the constraints in entity framework that you cannot reload an entity collection that was initially loaded with No Tracking option.

There are certain states of entity when calling load is not allowed such as when source entity is in Added, Deleted or Detached. Calling Load in these states causes invalid operation exception. Code in listing 1-7 illustrates some of these issues.

Listing 1-7

var testcustomer = new Customer { CustomerID = "ALFK5", City

= "Dallas", CompanyName = "XYZ" };

//cant call load on customer that is in added state.

In listing 1-7, I am adding a new customer and then calling Load on its order collection. This operation results in an exception because customer is in added state and calling load is not permitted. Similarly I am marking BERGS customer for deletion and then calling Load on its orders collection. This also results in an exception because load is also not permitted on source entities marked for deletion. Also if you detach source entity from the object context such as customer in our case, calling Load will raise an exception.

When Load is called on entity reference, it does not make use of the entity key exposed on the entity reference to load related entity. When order entity is loaded, order.CustomerReference.EntityKey contains the CustomerId for the order. However when Load call is issued against

order.CustomerReference, EF does not use that customerid to load the customer. Instead to get the customerid for the order, the query for the customer is joined against order table to retrieve the customerid from the order’s table.

Code below shows an example that demonstrates this behavior.

var db = new NorthwindFullEntities();

db.Orders.MergeOption = MergeOption.NoTracking;

var order = db.Orders.First();

Console.WriteLine("CustomerID is {0}",

order.CustomerReference.EntityKey == null ? "null": "not null");

order.CustomerReference.Load();

Console.WriteLine("Order's Customer is {0}",order.Customer.CustomerID);

The above code uses MergeOption.NoTracking to retrieve the order from the database. The side effect of notracking is there won’t be any customerid for the order entity which is confirmed by printing this information on the console. Then to load the customer for the order, I am calling Load. Notice that despite that there was no customerid for the order, EF managed to load the customer reference because it queried the order table to get the customerid to load instead of reading the client side value from

order.CustomerReference.EntityKey. The sql below contains the profile capture for the above load query.

exec sp_executesql N'SELECT

FROM [dbo].[Orders] AS [Extent1]

INNER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerID] = [Extent2].[CustomerID]

WHERE ([Extent1].[CustomerID] IS NOT NULL) AND ([Extent1].[OrderID] =

@EntityKeyValue1)',N'@EntityKeyValue1 int',@EntityKeyValue1=10248

Figure below shows the result on the console window.

Load option is not limited to many to 1 association and 1 to many

associations. You can use load option to lazy load many to many navigation relations as well.

3.3 CreateSourceQuery

3.3.1 CreateSourceQuery to filter associations

Problem: Figure below shows the EDM model for Customer, Orders and OrderDetails entity.

You have retrieved a customer entity from the database and you want to retrieve only Orders for the customer which has a freight greater than thirty dollars and in addition you also want to retrieve the OrderDetails for the Orders that match that criteria.

Solution: If there was no requirement to filter the Orders collection for a customer, we could use Load operator to load the Orders. Since we want to apply a filter to retrieve only Orders with freight greater than 30, we can use CreateSourceQuery method exposed on EntityCollection and

EntityReference. CreateSourceQuery does not execute the query; it only creates a query that represents the collection you would get if you were to execute the query. At this moment we can pick up the query and apply further transformations such as apply Frieght filter and order Orders collection by OrderDate. In addition we can also apply include operator on the

CreateSourceQuery to also return the OrderDetails for the filtered Orders. The results returned from the CreateSourceQuery call needs to be attached to the Customer entity’s order collection to connect the relationship between

Customer and Orders retrieved.

Discussion: To retrieve part of the Orders collection, sorted by OrderDate and also fetch the OrderDetails for those Orders, we need to use the

CreateSourceQuery method to retrieve the Query that would otherwise return the Orders for the given Customer. Using that query we can apply filtering and Include operation to meet our above requirements. Once we retrieve the Orders, we must attach the Orders entity back to the Customer’s Order Collection. Code below shows the required code needed.

var db = new CSQEntities();

var cust = db.Customers.First(c => c.CustomerID == "ALFKI");

On the above code, I am using Include to indicate that I want to load

OrderDetails for every order that is part of the result set. In addition I am also applying a where filter and order by clause to only retrieve Orders with

freight greater than 30 dollars and then sort them by OrderDate. Since the orders are now separated from the ALFKI customer, I am using Attach

method to attach the Orders to Customer’s Orders collection. Rest of the code iterates through the collection to confirm that we only have the Orders that meet the filter criteria and also that we have all the OrderDetails for every Order in the collection. Figure below shows the screenshot on the console window.

3.3.2 CreateSourceQuery to Execute Aggregate operation on Child collections

Problem: You have Customer and Orders entity defined on EDM model.

Given a customer instance you want to retrieve the total orders customer has placed without loading all the orders in memory.

Solution: We can use CreateSourceQuery method to get access to the query that would return all the Orders for a given customer. Instead of actually returning the orders we can apply Count Operator on top of the query to get total order count. The query would be translated into a count operation executed on the database ensuring that we only return the total number of orders a customer has placed rather than return all orders.

Discussion: Code below shows how to return the total orders placed by ALFKI customer without bringing all the orders for the customer.

var db = new CSQEntities();

var cust = db.Customers.First(c => c.CustomerID == "ALFKI");

var totalorders = cust.Orders.CreateSourceQuery().Count();

Console.WriteLine("Total Orders:{0}",totalorders);

To get the total orders for ALFKI customer, I am accessing the Orders query with CreateSourceQuery and applying count operation on it. To confirm the count operation is applied on the database I have captured the sql statement send to the database.

SELECT

COUNT(cast(1 as bit)) AS [A1]

FROM [dbo].[Orders] AS [Extent1]

WHERE [Extent1].[CustomerID] = @EntityKeyValue1 @EntityKeyValue1=N'ALFKI'

3.3.3 CreateSourceQuery to retrieve specific derived type from entity collection

Problem: Figure below shows the EDM model for School, Student, Administrator and Course entity.

School has many persons. A person can be either a student or an

Administrator. A student can be enrolled in many courses. Given an instance of school, you only want to retrieve Persons that are students. In addition for those students you also want to retrieve the courses they are enrolled in.

Solution: To retrieve only students for a school, we have to use

CreateSourceQuery to get access to the query that would return all persons for a school. To only get Students, we can filter that query using OfType operator to only return students. Since we also want to return Courses for the student, we can also append an Include statement for courses which would ensure that for every student, we will have their courses preloaded too.

Discussion: Code below shows how to retrieve only students for a school and also eagerly load their courses.

var db = new CRSContainer();

var school = db.Schools.First(s => s.SchoolName == "Habib");

var student = school.Persons

.CreateSourceQuery()

.OfType<Student>().Include("Courses") .First();

Console.WriteLine("Student:{0} Courses Enrolled:{1}", student.Name, student.Courses.Count());

On the above query, I am applying OfType operator on CreateSourceQuery to only get Students for the school. Along with Students I am also retrieving the courses using Include followed by First operator to only return the first student. To confirm the results, I am printing the name of the Student and total courses student is registered for. Figure below shows the output on the console window.

In document Entity Framework Learning Guide (Page 113-125)