Loading Entities and Navigation Properties
Entity Framework provides a rich modeling environment that enables the developer to work visually with entity classes that map to database tables, views, stored procedures, and relationships. The recipes in this chapter show you how to control the loading of related entities in your query operations.
The default behavior for Entity Framework is to load only the entities directly needed by your application. In general, this is exactly what you want. If Entity Framework aggressively loaded all of the entities related through one or more associations, you would likely end up loading more entities than you needed. This would increase the memory footprint of your application as well as impact performance.
In Entity Framework, you can control when the loading of related entities occurs and optimize the number of database queries executed. Carefully managing if and when related entities are loaded can increase application performance and provide you more control over your data.
In this chapter, we illustrate the various options available for loading related data along with an explanation about the benefits and drawbacks of each. Specifically, we discuss the default behavior of lazy loading and what it really means. Then we’ll look at a number of recipes illustrating the various options you have to load some or all of the related entities in a single query. This type of loading, called eager loading, is used both to reduce the number of round trips to the database and, more precisely, to control which related entities are loaded.
Sometimes you need to defer loading of certain related entities because they may be expensive to load or are not used very often. For these cases, we’ll cover yet another approach to loading related entities, entitled explicit loading, and demonstrate a number of scenarios using the Load() method to control precisely when to load one or more related entities.
Finally, we’ll take a brief look at some of the asynchronous operations that are now available.
5-1. Lazy Loading Related Entities
Problem
You want to load an entity and then load related entities, only if and when they are needed by your application.
Solution
Let’s say that you have a model like the one shown in Figure 5-1.
Figure 5-1. A model with a Customer and its related information
In this model, we have a Customer entity with a single CustomerType and many CustomerEmail addresses. The association with CustomerType is one-to-many with CustomerType on the one side of the association. This is an entity reference.
The association with CustomerEmail is also one-to-many but with CustomerEmail on the many side of the association. This is an entity collection.
When we put all three entity classes together, we arrive at a construct called an object graph. An object graph is a set of individual, but related entities, which together form a logical whole unit. Specifically, an object graph is a view of a given entity and its related entities at a specific point in time. For example, during an operation in our application, a customer with an Id of 5 may contain the name “John Smith,” have a customer type of “preferred,” and a collection of 10 customer emails.
Listing 5-1 demonstrates the lazy loading behavior of Entity Framework, which is the default behavior for loading related entity objects.
Listing 5-1. Lazy Loading of Instances of Customertype and Customeremail Along with Instances of Customer
using (var context = new EFRecipesEntities())
{
var customers = context.Customers;
Console.WriteLine("Customers");
Console.WriteLine("=========");
// Only information from the Customer entity is requested
foreach (var customer in customers)
{
Console.WriteLine("Customer name is {0}", customer.Name);
}
// Now, application is requesting information from the related entities, CustomerType
// and CustomerEmail, resulting in Entity Framework generating separate queries to each
// entity object in order to obtain the requested information.
foreach (var customer in customers)
{
Console.WriteLine("{0} is a {1}, email address(es)", customer.Name,
customer.CustomerType.Description);
foreach (var email in customer.CustomerEmails)
{
Console.WriteLine(" {0}", email.Email);
}
}
// Extra credit:
// If you enable SQL Profiler, the following query will not requery the database
// for related data. Instead, it will return the in-memory data from the prior query.
foreach (var customer in customers)
{
Console.WriteLine("{0} is a {1}, email address(es)", customer.Name,
customer.CustomerType.Description);
foreach (var email in customer.CustomerEmails)
{
Console.WriteLine(" {0}", email.Email);
}
}
}
The output of the code in Listing 5-1 is the following:
Customers
=========
Customer name is Joan Smith
Customer name is Bill Meyers
Joan Smith is a Web Customer, email address(es)
[email protected]
[email protected]
Bill Meyers is a Retail Customer, email address(es)
[email protected]
Joan Smith is a Web Customer, email address(es)
[email protected]
[email protected]
Bill Meyers is a Retail Customer, email address(es)
[email protected]
How It Works
By default, Entity Framework loads only entities that you specifically request. This is known as lazy loading, and it is an important principle to keep in mind. The alternative, loading the parent and every associated entity, known as eager loading , may load a much larger object graph into memory than you need, not to mention the added overhead of retrieving, marshaling, and materializing a larger amount of data.
In this example, we start by issuing a query against the Customer entity to load all customers. Interestingly, the query itself is not executed immediately, but rather when we first enumerate the Customer entity in the first foreach construct. This behavior follows the principle of deferred loading upon which LINQ is built.
In the first foreach construct, we only request data elements from the underlying Customer table and not any data from the CustomerType or CustomerEmail table. In this case, Entity Framework only queries the Customer table and not the related CustomerType or CustomerEmail tables.
Then, in the second foreach construct, we explicitly reference the Description property from the CustomerType entity and the Email property from the CustomerEmail entity. Directly accessing these properties results in Entity Framework generating a query to each related table for the requested data. It’s important to understand that Entity Framework generates a separate query the first time either of the related tables are accessed. Once a query has been invoked for a property from a related entity, Entity Framework will mark the property as loaded and will retrieve the data from memory as opposed to requerying the underlying table over and over again. In this example, four separate queries are generated for child data:
- A select statement against CustomerType and CustomerEmail for Joan Smith
- A select statement against CustomerType and CustomerEmail for Bill Meyers
This separate query for each child table works well when a user is browsing your application and requests different data elements depending on his or her needs at the moment. It can improve application response time, since data is retrieved as needed with a series of small queries, as opposed to loading a large amount of data up front, potentially causing a delay in rendering the view to the user.
This approach, however, is not so efficient when you know, up front, that you will require a large set of data from related tables. In those cases, a query with eager loading may be a better option as it can retrieve all of the data (from both the parent and related tables) in a single query.
The last code block, entitled ‘Extra Credit,’ demonstrates that once child properties are loaded, Entity Framework will retrieve their values from in-memory and not requery the database. Turn on the SQL Server Profiler Tool, run the example and note how the ‘Extra Credit’ code block does not generate SQL Select statements when child properties are referenced.
Note SQL Server Profiler is a great tool for inspecting the actual query statements generated by SQL Server. It is free and included with SQL Server Developer Edition and better: http://technet.microsoft.com/en-us/library/ms181091.aspx
5-2. Eager Loading Related Entities
Problem
You want to load an entity along with some related entities in a single trip to the database.
Solution
Let’s say that you have a model like the one shown in Figure 5-2.
Figure 5-2. A model with a Customer and its related information
Similar to Recipe 5-1, in this model we have a Customer entity with a single CustomerType and many CustomerEmail addresses. The association with CustomerType is one-to-many with CustomerType on the one side of the association. This is an entity reference.
To fetch the parent customer entity objects and all of the related CustomerEmail entities and CustomerType entity objects at once, we use the Include() method syntax, as shown in Listing 5-2.
Listing 5-2. Eager Loading of Instances of Customertype and Customeremail Along with Instances of Customer
using (var context = new EFRecipesEntities())
{
var web = new CustomerType { Description = "Web Customer",
CustomerTypeId = 1 };
var retail = new CustomerType { Description = "Retail Customer",
CustomerTypeId = 2 };
var customer = new Customer { Name = "Joan Smith", CustomerType = web };
customer.CustomerEmails.Add(new CustomerEmail
{ Email = "[email protected]" });
customer.CustomerEmails.Add(new CustomerEmail { Email = "[email protected]" });
context.Customers.Add(customer);
customer = new Customer { Name = "Bill Meyers", CustomerType = retail };
customer.CustomerEmails.Add(new CustomerEmail
{ Email = "[email protected]" });
context.Customers.Add(customer);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
// Include() method with a string-based query path to the
// corresponding navigation properties
var customers = context.Customers
.Include("CustomerType")
.Include("CustomerEmails");
Console.WriteLine("Customers");
Console.WriteLine("=========");
foreach (var customer in customers)
{
Console.WriteLine("{0} is a {1}, email address(es)", customer.Name,
customer.CustomerType.Description);
foreach (var email in customer.CustomerEmails)
{
Console.WriteLine(" {0}", email.Email);
}
}
}
using (var context = new EFRecipesEntities())
{
// Include() method with a strongly typed query path to the
// corresponding navigation properties
var customerTypes = context.CustomerTypes
.Include(x => x.Customers)
.Select(y =>y.CustomerEmails));
Console.WriteLine(" Customers by Type");
Console.WriteLine("=================");
foreach (var customerType in customerTypes)
{
Console.WriteLine("Customer type: {0}", customerType.Description);
foreach (var customer in customerType.Customers)
{
Console.WriteLine("{0}", customer.Name);
foreach (var email in customer.CustomerEmails)
{
Console.WriteLine(" {0}", email.Email);
}
}
}
}
The output of the code in Listing 5-2 is the following:
Customers
=========
Joan Smith is a Web Customer, email address(es)
[email protected]
[email protected]
Bill Meyers is a Retail Customer, email address(es)
[email protected]
Customers by Type
=================
Customer type: Web Customer
Joan Smith
[email protected]
[email protected]
Customer type: Retail Customer
Bill Meyers
[email protected]
How It Works
By default, Entity Framework loads only entities that you specifically request. This is known as lazy loading and can be quite efficient in the use case where a user is browsing your application and may navigate to different views based upon his or her needs.
An alternative, loading the parent and related entities (keep in mind that our object graph is a set of parent/child entities based on relationships, similar to parent/child database tables with foreign key relationships) at once, is known as eager loading. This approach can be efficient when you know, up front, that you will require a large set of related data, as it can retrieve all data (both from the parent and related entities) in a single query.
In Listing 5-2, to fetch the object graph all at once, we use the Include() method twice. In the first use, we start the object graph with Customer and include an entity reference to the CustomerType entity. This is on the one side of the one-to-many association. Then, in the subsequent Include() method (contained in the same line of code, chained together), we get the many side of the one-to-many association, bringing along all of the instances of the CustomerEmail entity for the customer. By chaining together the Include() method twice in a fluent API manner, we fetch referenced entities from both of the Customer’s navigation properties. Note that in this example we use string representations of the navigation properties, separated by the “.” character, to identify the related entity objects. The string representation is referred as the query path of the related objects.
In the following foreach construct, we perform the exact same operation, but using strongly typed query paths. Note here how we use lambda expressions to identify each of the related entities. The strongly typed usage provides us with both IntelliSense, compile-time safety and refactoring support.
Note that the SQL query that is generated in Listing 5-3 is generated from usage of the Include() method. Entity Framework automatically removes data that is duplicated by the query, as shown in Figure 5-3, before the result is materialized and sent back to the application.
Listing 5-3. The SQL Query Resulting from Our Use of the Include() Method
SELECT
[Project1].[CustomerId] AS [CustomerId],
[Project1].[Name] AS [Name],
[Project1].[CustomerTypeId] AS [CustomerTypeId],
[Project1].[CustomerTypeId1] AS [CustomerTypeId1],
[Project1].[Description] AS [Description],
[Project1].[C1] AS [C1],
[Project1].[CustomerEmailId] AS [CustomerEmailId],
[Project1].[CustomerId1] AS [CustomerId1],
[Project1].[Email] AS [Email]
FROM ( SELECT
[Extent1].[CustomerId] AS [CustomerId],
[Extent1].[Name] AS [Name],
[Extent1].[CustomerTypeId] AS [CustomerTypeId],
[Extent2].[CustomerTypeId] AS [CustomerTypeId1],
[Extent2].[Description] AS [Description],
[Extent3].[CustomerEmailId] AS [CustomerEmailId],
[Extent3].[CustomerId] AS [CustomerId1],
[Extent3].[Email] AS [Email],
CASE WHEN ([Extent3].[CustomerEmailId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM [Chapter5].[Customer] AS [Extent1]
INNER JOIN [Chapter5].[CustomerType] AS [Extent2] ON [Extent1].[CustomerTypeId] = [Extent2].[CustomerTypeId]
LEFT OUTER JOIN [Chapter5].[CustomerEmail] AS [Extent3] ON [Extent1].[CustomerId] = [Extent3].[CustomerId]
) AS [Project1]
ORDER BY [Project1].[CustomerId] ASC, [Project1].[CustomerTypeId1] ASC, [Project1].[C1] ASC
Figure 5-3. Redundant data resulting from the Include() method
5-3. Finding Single Entities Quickly
Problem
You want to load a single entity, but you do not want to make another trip to the database if the entity is already loaded in the context. Additionally, you want to implement the Code-First approach for Entity Framework 6 to manage data access.
Solution
Let’s say that you have a model like the one shown in Figure 5-4.
Figure 5-4. A simple model that represents Club entity objects
In this model, we have a Club entity that we can query to obtain information about various clubs.
Start by adding a console application project to Visual Studio entitled Recipe3. Be certain to reference the Entity Framework 6 libraries. Leveraging the NuGet Package Manager does this best. Right-click on Reference, and select Manage NuGet Packages. From the Online tab, locate and install the Entity Framework 6 package. Doing so will download, install, and configure the Entity Framework 6 libraries in your project.
To create the club entity, create a class entitled Club and copy the properties into it from Listing 5-4.
Listing 5-4. Club Entity Class
public class Club
{
public int ClubId { get; set; }
public string Name { get; set; }
public string City { get; set; }
}
Next create a class entitled Recipe3Context and add the code from Listing 5-5 to it, ensuring the class derives from the Entity Framework DbContext class.
Listing 5-5. Context Class
public class Recipe3Context : DbContext
{
public Recipe3Context()
: base("Recipe3ConnectionString")
{
// Disable Entity Framework Model Compatibility
Database.SetInitializer<Recipe3Context>(null);
}
public DbSet<Club> Clubs { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Club>().ToTable("Chapter5.Club");
}
}
Next add an App.Config class to the project, and add the code from Listing 5-6 to it under the ConnectionStrings section.
Listing 5-6. Connection String
<connectionStrings>
<add name="Recipe3ConnectionString"
connectionString="Data Source=.;
Initial Catalog=EFRecipes;
Integrated Security=True;
MultipleActiveResultSets=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
If we are searching for an entity by a key value, a common operation, we can leverage the Find() method first to search the in-memory context object for a requested entity before attempting to fetch it from the database. Keep in mind that the default behavior of Entity Framework is to query the database each time you issue an operation to retrieve data, even if that data has already been loaded into memory in the context object.
The Find() method is a member of the DbSet class, which we use to register each entity class in the underlying DbContext object. The pattern is demonstrated in Listing 5-7.
Listing 5-7. Leveraging the Find() Method in Entity Framework to Avoid Fetching Data That Has Already Been Loaded into the Context
using (var context = new Recipe3Context())
{
var starCity = new Club {Name = "Star City Chess Club", City = "New York"};
var desertSun = new Club {Name = "Desert Sun Chess Club", City = "Phoenix"};
var palmTree = new Club {Name = "Palm Tree Chess Club", City = "San Diego"};
context.Clubs.Add(starCity);
context.Clubs.Add(desertSun);
context.Clubs.Add(palmTree);
context.SaveChanges();
// SaveChanges() returns newly created Id value for each club
starCityId = starCity.ClubId;
desertSunId = desertSun.ClubId;
palmTreeId = palmTree.ClubId;
}
using (var context = new Recipe3Context())
{
var starCity = context.Clubs.SingleOrDefault(x => x.ClubId == starCityId);
starCity = context.Clubs.SingleOrDefault(x => x.ClubId == starCityId);
starCity = context.Clubs.Find(starCityId);
var desertSun = context.Clubs.Find(desertSunId);
var palmTree = context.Clubs.AsNoTracking().SingleOrDefault(x => x.ClubId == palmTreeId);
palmTree = context.Clubs.Find(palmTreeId);
var lonesomePintId = -999;
context.Clubs.Add(new Club {City = "Portland", Name = "Lonesome Pine", ClubId = lonesomePintId,});
var lonesomePine = context.Clubs.Find(lonesomePintId);
var nonexistentClub = context.Clubs.Find(10001);
}
How It Works
When querying against the context object, a round trip will always be made to the database to retrieve requested data, even if that data has already been loaded into the context object in memory. When the query completes, entity objects that do not exist in the context are added and then tracked. By default, if the entity object is already present in the context, it is not overwritten with more recent database values.
However, the DbSet object, which wraps each of our entity objects, exposes a Find() method. Specifically, Find() expects an argument that represents the primary key of the desired object. Find() is very efficient, as it will first search the underlying context for the target object. If the object is not found, it then automatically queries the underlying data store. If still not found, Find() simply returns NULL to the caller. Additionally, Find() will return entities that have been added to the context (think, having a state of “Added”), but not yet saved to the underlying database. Fortunately, the Find() method is available with any of three modeling approaches: Database First, Model First, or Code First.
In this example, we start by adding three new clubs to the Club entity collection. Note how we are able to reference the newly created Id for each Club entity immediately after the call to SaveChanges(). The context will return the Id for the new object immediately after the SaveChanges() operation completes.
We next query the Clubs entity from the DbContext object to return the StarCity Club entity. Note how we leverage the SingleOrDefault() LINQ extension method, which returns exactly one object, or NULL, if the object does not exist in the underlying data store. SingleOrDefault() will throw an exception if more than one object with the search criteria is found. SingleOfDefault() is an excellent approach to querying entities by a primary key property. If you should desire the first object when many exist, consider the FirstOrDefault() method.
If you were to run SQL Profiler tool (available in SQL Server Developer Edition or better, not in SQL Express) to examine the underlying database activity, you would see that the SQL query shown in Figure 5-5 was generated.
Figure 5-5. SQL query returning the Star City Club
Note in Figure 5-5 how querying Clubs in the context object always results in a SQL query generated against the underlying data store. Here we retrieve the Club with the Id of 80, materialize the data into a Club entity object, and store it in the context. Interestingly, note how the SingleOrDefault() LINQ extension method always generates a Select Top 2 SQL query. Interestingly, the Select Top 2 SQL query ensures that only one row is returned. If more than one row is returned, Entity Framework will throw an exception as the SingleOrDefault() method guarantees a single result.
The next line of code re-queries the database for the exact same Star City Club. Note that, even though this entity object already exists in the context, the default behavior of the DbContext is to re-query the database for the record. In profiler, we see the exact same SQL query generated. What’s more, since the Star City entity is already loaded in the context, the DbContext does not overwrite the current values with updated values from the database, as shown in Figure 5-6.
Figure 5-6. SQL query returning the Star City Club
In the next line of code we once again search for the Star City Club. This time, however, we leverage the Find() method that is exposed by the DbSet Class. Since the Club entity is a DbSet class, we simply call the Find() method on it and pass in the primary key of the entity as an argument to Find(), which in this case is the value of 80.
Find() first searches the context object in memory for Star City, finds the object, and returns a reference to it. The key point is that Find() only queries the database if it cannot find the requested object in the context object. Note in Figure 5-7 how a SQL query was not generated.
Figure 5-7. The Find() method locates the object in the context, and it never generates a query to the database
Next we again use the Find() method to retrieve the entity for the Desert Sun Club. This Find() does not locate the target entity in the context object, and it next queries the underlying data store to return the information. Note in Figure 5-8 the SQL query that is generated to retrieve the data.
Figure 5-8. SQL query generated to return the Desert Sun Club
In the next query, we retrieve entity information for the Palm Tree Club, but pay particular attention to the LINQ query. Note the AsNoTracking() clause that has been appended to Clubs. The NoTracking option disables object state tracking for the specific entity. With NoTracking, Entity Framework will not track changes to the Palm Tree object, nor will it load it into the underlying context object.
When we issue a subsequent request to obtain the Palm Tree club entity object, Find() generates a SQL query to retrieve the entity from the data store, as shown in Figure 5-9. The round trip to the database is necessary as we instructed Entity Framework not to track the object in the context object with the AsNoTracking() clause. Keep in mind that Find() requires the entity object to be tracked in the context in order to avoid a call to the database.
Figure 5-9. Another SQL query generated to return the Desert Sun Club
Next we add a new Club entity object to the context. We instantiate an instance of the Club entity class and populate it with the necessary data. We assign it a temporary Id of −999. Keep in mind that we have not yet requested a SaveChanges() operation to commit this new club, the Lonesome Pine Club, to the data store. Interestingly, when we issue a Find() operation and pass in the argument −999, Entity Framework returns the newly created Lonesome Pine Club entity from the context object. You can see in Figure 5-10 that the Find() call generated no database activity. Take note: Find() will return a newly added entity instance from the underlying context object that has not yet been saved to the data store.
Figure 5-10. The Find() method locates the newly created, but not yet saved object in the context and returns it without generating a query to the database
Finally, we issue a Find() query passing in an argument value that does not exist in the data store. Here we pass an Id value of 10001. In Figure 5-11, we see that Find() issues a SQL query to the database attempting to return a record with an Id of 10001. Similar to the SingleOrDefault() LINQ extension method, Find() returns NULL to calling method when it does not find the record.
Figure 5-11. The Find() method generates a SQL query and returns NULL if the record is not found in the database
5-4. Querying In-Memory Entities
Problem
You want to work with entity objects from your model, but do not want to make a round trip to the database if the desired entity is already loaded in the in-memory context object. Additionally, you want to implement the Code-First approach for Entity Framework 6 to manage data access.
Solution
Let’s say that you have a model like the one shown in Figure 5-12.
Figure 5-12. A simple model that represents Club entity objects
Start by adding a console application project to Visual Studio entitled Recipe4. Be certain to reference the Entity Framework 6 libraries. Leveraging the NuGet Package Manager does this best. Right-click on Reference, and select Manage NuGet Packages. From the Online tab, locate and install the Entity Framework 6 package. Doing so will download, install, and configure the Entity Framework 6 libraries in your project.
To create the club entity, create a class entitled Club and copy the information into it from Listing 5-8.
Listing 5-8. Club Entity Class
public class Club
{
public int ClubId { get; set; }
public string Name { get; set; }
public string City { get; set; }
}
Next create a class entitled Recipe4Context, and add the code from Listing 5-9 to it, ensuring the class derives from the Entity Framework DbContext class.
Listing 5-9. Context Class
public class Recipe4Context : DbContext
{
public Recipe4Context()
: base("Recipe4ConnectionString")
{
// disable Entity Framework Model Compatibility
Database.SetInitializer<Recipe4Context>(null);
}
public DbSet<Club> Clubs { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Club>().ToTable("Chapter5.Club");
}
}
Next add an App.Config classd to the project, and add the code from Listing 5-10 to it under the ConnectionStrings section.
Listing 5-10. Connection String
<connectionStrings>
<add name="Recipe4ConnectionString"
connectionString="Data Source=.;
Initial Catalog=EFRecipes;
Integrated Security=True;
MultipleActiveResultSets=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
In this model, we have a Club entity from which we can query information about various clubs. We can reduce round trips to the database by directly querying the Local property of the underlying DbSet, which we use to wrap the Club entity. The Local property exposes an observable collection of in-memory entity objects, which stays in sync with the underlying context. Usage of the Local collection is demonstrated in Listing 5-11.
Listing 5-11. Common Usage of the Local Property for a DbSet Object
using (var context = new Recipe4Context())
{
Console.WriteLine(" Local Collection Behavior");
Console.WriteLine("=================");
Console.WriteLine(" Number of Clubs Contained in Local Collection: {0}", context.Clubs.Local.Count);
Console.WriteLine("=================");
Console.WriteLine(" Clubs Retrieved from Context Object");
Console.WriteLine("=================");
foreach (var club in context.Clubs.Take(2))
{
Console.WriteLine("{0} is located in {1}", club.Name, club.City);
}
Console.WriteLine(" Clubs Contained in Context Local Collection");
Console.WriteLine("=================");
foreach (var club in context.Clubs.Local)
{
Console.WriteLine("{0} is located in {1}", club.Name, club.City);
}
context.Clubs.Find(desertSunId);
Console.WriteLine(" Clubs Retrieved from Context Object - Revisted");
Console.WriteLine("=================");
foreach (var club in context.Clubs)
{
Console.WriteLine("{0} is located in {1}", club.Name, club.City);
}
Console.WriteLine(" Clubs Contained in Context Local Collection - Revisted");
Console.WriteLine("=================");
foreach (var club in context.Clubs.Local)
{
Console.WriteLine("{0} is located in {1}", club.Name, club.City);
}
// Get reference to local observable collection
var localClubs = context.Clubs.Local;
// Add new Club
var lonesomePintId = -999;
localClubs.Add(new Club
{
City = "Portland",
Name = "Lonesome Pine",
ClubId = lonesomePintId
});
// Remove Desert Sun club
localClubs.Remove(context.Clubs.Find(desertSunId));
Console.WriteLine(" Clubs Contained in Context Object - After Adding and Deleting");
Console.WriteLine("=================");
foreach (var club in context.Clubs)
{
Console.WriteLine("{0} is located in {1} with a Entity State of {2}",
club.Name, club.City, context.Entry(club).State);
}
Console.WriteLine(" Clubs Contained in Context Local Collection - After Adding and Deleting");
Console.WriteLine("=================");
foreach (var club in localClubs)
{
Console.WriteLine("{0} is located in {1} with a Entity State of {2}",
club.Name, club.City, context.Entry(club).State);
}
Console.WriteLine(" Press <enter> to continue...");
Console.ReadLine();
}
The code in Listing 5-11 produces the following output:
Local Collection Behavior
=================
Number of Clubs Contained in Local Collection: 0
=================
Clubs Retrieved from Context Object
=================
Star City Chess Club is located in New York
Desert Sun Chess Club is located in Phoenix
Clubs Contained in Context Local Collection
=================
Star City Chess Club is located in New York
Desert Sun Chess Club is located in Phoenix
Clubs Retrieved from Context Object - Revisted
=================
Star City Chess Club is located in New York
Desert Sun Chess Club is located in Phoenix
Palm Tree Chess Club is located in San Diego
Clubs Contained in Context Local Collection - Revisted
=================
Star City Chess Club is located in New York
Desert Sun Chess Club is located in Phoenix
Palm Tree Chess Club is located in San Diego
Clubs Contained in Context Object – After Adding and Deleting
=================
Star City Chess Club is located in New York with a Entity State of Unchanged
Desert Sun Chess Club is located in Phoenix with a Entity State of Deleted
Palm Tree Chess Club is located in San Diego with a Entity State of Unchanged
Clubs Contained in Context Local Collection – After Adding and Deleting
=================
Star City Chess Club is located in New York with a Entity State of Unchanged
Palm Tree Chess Club is located in San Diego with a Entity State of Unchanged
Lonesome Pine is located in Portland with a Entity State of Added
How It Works
This example works with Club entity objects. We begin by requesting a count of Club entity objects from the observable collection that is exposed by the Local property from the Club entity object. Note in Figure 5-13 that no SQL query is generated, as a query against the Local Property never generates a SQL query to the data store.
Figure 5-13. Accessing the Local collection never generates a SQL query
Right now, the result is zero, as we have not yet executed a query for Clubs against the context object. Keep in mind that the Local collection is automatically kept in sync with the underlying context object.
Next we query the context object for the first two Club entities in the data store and loop through them, rendering the name and location of each, as shown in Figure 5-14.
Figure 5-14. Querying the context object always generates a SQL query
Immediately after, we loop through the corresponding Local collection for Clubs and get the same result. Remember that the results are identical, as the Local collection automatically synchronizes with the DbContext. When new entities are fetched into the context, the Local collection is automatically updated with those entities. However, note in Figure 5-15 that no SQL query was generated when accessing the Local collection.
Figure 5-15. Accessing the Local collection never generates a SQL query
To demonstrate further the Local Property default behavior, we fetch a third Club entity by querying from the underlying context object. Once again, as we loop through both the context and Local collection, we get the same result. Note in Figure 5-16 that querying the context object always generates a SQL statement and that querying the Local collection does not, as shown in Figure 5-17.
Figure 5-16. Querying the context object always generates a SQL query
Figure 5-17. Accessing the Local collection never generates a SQL query
Next we add a new Club entity entitled the Lonesome Pine Club to the Local collection and, at the same time, remove the Desert Sun Club from the Local collection. We then iterate through the context object for Clubs, which as expected, generates a SQL query against the underlying data store, as shown in Figure 5-18.
Figure 5-18. Querying the context object always generates a SQL query
Interestingly, in the context, we see that the Desert Sun Club has been marked for deletion, but we do not see the newly added Lonesome Pine Club. Keep in mind that Lonesome Pine has been added to the Context object, but we have not yet called the SaveChanges() operation to update the underlying data store.
However, when we iterate through the Local collection for Clubs, we do not generate a query to the underlying data store, as shown in Figure 5-19. Instead, we see the newly added Lonesome Pine Club, but we no longer see the Desert Sun Club that is marked for deletion. The default behavior of the Local collection is to hide any entities that are marked for deletion, as these objects are no longer valid.
Figure 5-19. Accessing the Local collection never generates a SQL query
The bottom line: Accessing the Local collection never causes a query to be sent to the database; accessing the context object always causes a query to be sent to the database.
To summarize, each entity set exposes a property called Local, which is an observable collection that mirrors the contents of the underlying context object. As demonstrated in this recipe, querying the Local Collection can be very efficient in that doing so never generates a SQL query to the underlying data store.
5-5. Loading a Complete Object Graph
Problem
You have a model with several related entities, and you want to load the complete object graph of all the instances of each entity in a single query. Normally, when a specific view requires a set of related entities in order to render, you’ll prefer this approach as opposed to the lazy loading approach that fetches related data with a number of smaller queries.
Solution
Suppose you have a conceptual model like the one in Figure 5-20. Each course has several sections. Each section is taught by an instructor and has several students.
Figure 5-20. A model with a few related entities
To retrieve all of the courses, sections, instructors, and students represented in the database in a single query, use the Include() method with a query path parameter, as shown in Listing 5-12.
Listing 5-12. Retrieving an Entire Object Graph in a Single Query
using (var context = new EFRecipesEntities())
{
var course = new Course { Title = "Biology 101" };
var fred = new Instructor { Name = "Fred Jones" };
var julia = new Instructor { Name = "Julia Canfield" };
var section1 = new Section { Course = course, Instructor = fred };
var section2 = new Section { Course = course, Instructor = julia };
var jim = new Student { Name = "Jim Roberts" };
jim.Sections.Add(section1);
var jerry = new Student { Name = "Jerry Jones" };
jerry.Sections.Add(section2);
var susan = new Student { Name = "Susan O'Reilly" };
susan.Sections.Add(section1);
var cathy = new Student { Name = "Cathy Ryan" };
cathy.Sections.Add(section2);
course.Sections.Add(section1);
course.Sections.Add(section2);
context.Students.Add(jim);
context.Students.Add(jerry);
context.Students.Add(susan);
context.Students.Add(cathy);
context.Courses.Add(course);
context.SaveChanges();
}
// String query path argument for the Include method
using (var context = new EFRecipesEntities())
{
var graph = context.Courses
.Include("Sections.Instructor")
.Include("Sections.Students");
Console.WriteLine("Courses");
Console.WriteLine("=======");
foreach (var course in graph)
{
Console.WriteLine("{0}", course.Title);
foreach (var section in course.Sections)
{
Console.WriteLine(" Section: {0}, Instrutor: {1}", section.SectionId, section.Instructor.Name);
Console.WriteLine(" Students:");
foreach (var student in section.Students)
{
Console.WriteLine(" {0}", student.Name);
}
Console.WriteLine(" ");
}
}
}
// Strongly typed query path argument for the Include method
using (var context = new EFRecipesEntities())
{
var graph = context.Courses
.Include(x => x.Sections.Select(y => y.Instructor))
.Include(x => x.Sections.Select(z => z.Students));
Console.WriteLine("Courses");
Console.WriteLine("=======");
var result = graph.ToList();
foreach (var course in graph)
{
Console.WriteLine("{0}", course.Title);
foreach (var section in course.Sections)
{
Console.WriteLine(" Section: {0}, Instrutor: {1}", section.SectionId, section.Instructor.Name);
Console.WriteLine(" Students:");
foreach (var student in section.Students)
{
Console.WriteLine(" {0}", student.Name);
}
Console.WriteLine(" ");
}
}
}
Console.WriteLine("Press <enter> to continue...");
Console.ReadLine();
}
The code in Listing 5-12 produces the following output:
Courses
Courses
=======
Biology 101
Section: 19, Instructor: Fred Jones
Students:
Jim Roberts
Susan O'Reilly
Section: 20, Instructor: Julia Canfield
Students:
Jerry Jones
Cathy Ryan
How It Works
A query path is a string or strongly typed argument that is passed to the Include() method. A query path represents the entire path of the object graph that you want to load with the Include() method. The Include() method extends the query to include the entities referenced along the query path.
In Listing 5-12, we start by demonstrating the Include() method with string-based query parameters. Include() is invoked first with a query path parameter that includes the part of the graph extending through Section to Instructor. This modifies the query to include all of the Sections and their Instructors. Then, chained to the first Include() method is another Include() method that includes a path extending through Section to Student. This modifies the query to include Sections and their Students. The result is a materialization of the complete object graph including all Course entities along with entities on each end of the associations in the model.
In the second part of Listing 5-12, we demonstrate the usage of the Include() method with strongly typed query path parameters. Notice how both Include() methods here combine one parameter, Sections, with the associated Instructor and Student entity objects by using a Select() method.
Note The overloaded Include() method that accepts strongly-typed parameters is an extension method that is exposed from the System.Data.Entity namespace. You will need to add a using directive to your class that references this namespace in order to use the overloaded version of this method.
You can construct query paths from navigation properties to any depth. This gives you a great deal of flexibility in partial or complete object graph loading. Entity Framework attempts to optimize the final query generation by pruning off overlapping or duplicate query paths.
The syntax and semantics of the Include() method are deceptively simple. Don’t let the simplicity fool you into thinking that there is no performance price to be paid when using the Include() method. Eager loading with several Include() method invocations can rapidly increase the complexity of the query sent to the database and dramatically increase the amount of data returned from the database. The complex queries generated can lead to poor performance plan generation, and the large amount of returned data can cause Entity Framework to spend an inordinate amount of time removing duplicate data. You would be wise to profile all queries generated from usage of the Include() method to ensure that you are not causing potential performance problems for your application.
5-6. Loading Navigation Properties on Derived Types
Problem
You have a model with one or more derived types that are in a Has-a relationship (wherein one object is a part of another object) with one or more other entities. You want to eagerly load all of the related entities in one round trip to the database.
Solution
Suppose that you have a model like the one in Figure 5-21.
Figure 5-21. A model for Plumbers with their JobSite and other related entities
In this model, the Plumber entity extends the Tradesman entity. A Plumber has a JobSite that is represented by a one-to-many association. The JobSite type extends the Location entity. Location has a Phone, which is represented by a one-to-many association. Finally, a JobSite can have zero or more Foremen. A one-to-many association also represents this.
Suppose that you want to retrieve a plumber, the job site she works on, the job site’s phone number, and all of the foremen at the job site. You want to retrieve all of this in one round trip to the database.
The code in Listing 5-13 illustrates one way to use the Include() method to eagerly load the related entities in one query.
Listing 5-13. Retrieving Related Entities in One Round Rrip to the Database Using Eager Loading with the Include() Method
using (var context = new EFRecipesEntities())
{
var foreman1 = new Foreman { Name = "Carl Ramsey" };
var foreman2 = new Foreman { Name = "Nancy Ortega" };
var phone = new Phone { Number = "817 867-5309" };
var jobsite = new JobSite { JobSiteName = "City Arena",
Address = "123 Main", City = "Anytown",
State = "TX", ZIPCode = "76082",
Phone = phone };
jobsite.Foremen.Add(foreman1);
jobsite.Foremen.Add(foreman2);
var plumber = new Plumber { Name = "Jill Nichols",
Email = "[email protected]",
JobSite = jobsite };
context.Tradesmen.Add(plumber);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
var plumber = context.Tradesmen.OfType<Plumber>()
.Include("JobSite.Phone")
.Include("JobSite.Foremen").First();
Console.WriteLine("Plumber's Name: {0} ({1})", plumber.Name, plumber.Email);
Console.WriteLine("Job Site: {0}", plumber.JobSite.JobSiteName);
Console.WriteLine("Job Site Phone: {0}", plumber.JobSite.Phone.Number);
Console.WriteLine("Job Site Foremen:");
foreach (var boss in plumber.JobSite.Foremen)
{
Console.WriteLine(" {0}", boss.Name);
}
}
The following output is produced by code in Listing 5-13:
Plumber's Name: Jill Nichols ([email protected])
Job Site: City Arena
Job Site Phone: 817 867-5309
Job Site Foremen:
Carl Ramsey
Nancy Ortega
How It Works
Our query starts by selecting instances of the derived type Plumber. To fetch them, we use the OfType<Plumber>() method. The OfType<>() method selects instances of the given subtype from the entity set.
From Plumber, we want to load the related JobSite and the Phone for the JobSite. Notice that the JobSite entity does not have a Phone navigation property, but JobSite derives from Location, which does have a Phone navigation property. Because Phone is a property of the base entity, it’s also available on the derived entity. That’s the beauty of inheritance. This makes the query path simply: JobSite.Phone.
Then we again use the Include() method with a query path that references the Foreman entities from the JobSite entity. Here we have a one-to-many association, JobSite and Foreman. Notice that the wizard pluralized the navigation property (from Foreman to Foremen).
Finally, we use the First() method to select just the first Plumber instance. Doing so returns a type of Plumber, as opposed to a collection of Plumber objects.
The resulting query is somewhat complex; involving several joins and sub-selects. The alternative, leveraging the default lazy loading behavior of Entity Framework, would require several round trips to the database and could result in a performance hit, especially if we retrieved many Plumbers.
5-7. Using Include( ) with Other LINQ Query Operators
Problem
You have a LINQ query that uses operators such as group by, join, and where; and you want to use the Include() method to eagerly load additional entities. Additionally, you want to implement the Code-First approach for Entity Framework 6 to manage data access.
Solution
Let’s say that you have a model like the one shown in Figure 5-22.
Figure 5-22. A simple model with a one-to-many association between Club and Event
Start by adding a console application project to Visual Studio entitled Recipe7. Be certain to reference the Entity Framework 6 libraries. Leveraging the NuGet Package Manager does this best. Right-click on Reference, and select Manage NuGet Packages. From the Online tab, locate and install the Entity Framework 6 package. Doing so will download, install, and configure the Entity Framework 6 libraries in your project.
To create our entity objects, create a class entitled Club and Event and add the code from Listing 5-14.
Listing 5-14. Club Entity Class
public class Club
{
public Club()
{
Events = new HashSet<Event>();
}
public int ClubId { get; set; }
public string Name { get; set; }
public string City { get; set; }
public virtual ICollection<Event> Events { get; set; }
}
public class Event
{
public int EventId { get; set; }
public string EventName { get; set; }
public DateTime EventDate { get; set; }
public int ClubId { get; set; }
public virtual Club Club { get; set; }
}
Next create a class entitled Recipe7Context, and add the code from Listing 5-15 to it, ensuring the class derives from the Entity Framework DbContext class.
Listing 5-15. Context Class
public class Recipe7Context : DbContext
{
public Recipe7Context()
: base("Recipe7ConnectionString")
{
// Disable Entity Framework Model Compatibility
Database.SetInitializer<Recipe7Context>(null);
}
public DbSet<Club> Clubs { get; set; }
public DbSet<Event> Events { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Club>().ToTable("Chapter5.Club");
modelBuilder.Entity<Event>().ToTable("Chapter5.Event");
}
}
Next add an App.Config class to the project, and add the code from Listing 5-16 to it under the ConnectionStrings section.
Listing 5-16. Connection String
<connectionStrings>
<add name="Recipe7ConnectionString"
connectionString="Data Source=.;
Initial Catalog=EFRecipes;
Integrated Security=True;
MultipleActiveResultSets=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
To use the Include() method in combination with a group by clause, the Include() method must be placed after filtering and grouping operations for the parent entity. The code in Listing 5-17 demonstrates this approach.
Listing 5-17. The Correct Placement of the Include Method When Applying Filtering and Grouping Expressions on the Parent Entity
using (var context = new Recipes7Context())
{
var club = new Club {Name = "Star City Chess Club", City = "New York"};
club.Events.Add(new Event
{
EventName = "Mid Cities Tournament",
EventDate = DateTime.Parse("1/09/2010"), Club = club
});
club.Events.Add(new Event
{
EventName = "State Finals Tournament",
EventDate = DateTime.Parse("2/12/2010"), Club = club
});
club.Events.Add(new Event
{
EventName = "Winter Classic",
EventDate = DateTime.Parse("12/18/2009"), Club = club
});
context.Clubs.Add(club);
context.SaveChanges();
}
using (var context = new Recipes7Context())
{
var events = from ev in context.Events
where ev.Club.City == "New York"
group ev by ev.Club
into g
select g.FirstOrDefault(e1 => e1.EventDate == g.Min(evt => evt.EventDate));
var eventWithClub = events.Include("Club").First();
Console.WriteLine("The next New York club event is:");
Console.WriteLine(" Event: {0}", eventWithClub.EventName);
Console.WriteLine(" Date: {0}", eventWithClub.EventDate.ToShortDateString());
Console.WriteLine(" Club: {0}", eventWithClub.Club.Name);
}
The output of the code in Listing 5-17 is the following:
The next New York club event is:
Event: Winter Classic
Date: 12/18/2009
Club: Star City Chess Club
How It Works
We start by creating a Club and three Events. In the query, we grab all of the events at clubs in New York, group them by club, and find the first one in date order. Note how the FirstOrDefault() LINQ extension method is cleverly embedded in the Select, or projection, operation. However, the events variable holds just the expression. It hasn’t executed anything on the database yet.
Next we leverage the Include() method to eagerly load information from the related Club entity object using the variable, events, from the first LINQ query as the input for the second LINQ query. This is an example of composing LINQ queries—breaking a more complex LINQ query into a series of smaller queries, where the variable of the preceding query is in the source of the query.
Note how we use the First() method to select just the first Event instance. Doing so returns a type of Event, as opposed to a collection of Event objects. Entity Framework 6 contains a new static class entitled IQueryableExtensions, which exposes an Include() method prototype that accepts either a string-based or strongly typed query path parameter. The IQueryableExtensions class replaces the DbExtensions class from EF 4 and EF 5.
Many developers find the Include() method somewhat confusing. In some cases, IntelliSense will not show it as available (because of the type of the expression). At other times, it will be silently ignored at runtime. Surprisingly, the compiler rarely complains unless it cannot determine the resulting type. The problems usually show up at runtime when they can be a more difficult fix. Here are some simple rules to follow when using Include():
- The Include() method is an extension method on type IQueryable<T>.
- Include() applies only to the final query results. When Include() is applied to a subquery, join, or nested from clause, it is ignored when the command tree is generated. Under the hood, Entity Framework translates your LINQ-to-Entities query into a construct called a command tree, which is then handed to the database provider to construct a SQL query for the target database.
- Include() can be applied only to results that are entities. If the expression projects results that are not entities, Include() will be ignored.
- The query cannot change the type of the results between the Include() and the outermost operation. A group by clause, for example, changes the type of the results.
- The query path used in the Include() expression must start at a navigation property on the type returned from the outermost operation. The query path cannot start at an arbitrary point.
Let’s see how these rules apply to the code in Listing 5-17. The query groups the events by the sponsoring club. The group by operator changes the result type from Event to a grouping result. Here Rule 4 says that we need to invoke the Include() method after the group by clause has changed the type. We do this by invoking Include() at the very end. If we applied the Include() method earlier as in from ev in context.Events.Include(), the Include() method would have been silently dropped from the command tree and never applied.
5-8. Deferred Loading of Related Entities
Problem
You have an instance of an entity, and you want to defer the loading of two or more related entities in a single query. Especially important here is how we use the Load() method to avoid requerying the same entity twice. Additionally, you want to implement the Code-First approach for Entity Framework 6 to manage data access.
Solution
Suppose that you have a model like the one in Figure 5-23.
Figure 5-23. A a model with an employee, her department, and the department’s company
Start by adding a console application project to Visual Studio entitled Recipe8. Be certain to reference the Entity Framework 6 libraries. Leveraging the NuGet Package Manager does this best. Right-click on Reference, and select Manage NuGet Packages. From the Online tab, locate and install the Entity Framework 6 package. Doing so will download, install, and configure the Entity Framework 6 libraries in your project.
Next we create three entity objects: Company, Department, and Employee, and copy the code from Listing 5-18 into three classes.
Listing 5-18. Entity Classes
public class Company
{
public Company()
{
Departments = new HashSet<Department>();
}
public int CompanyId { get; set; }
public string Name { get; set; }
public virtual ICollection<Department> Departments { get; set; }
}
public class Department
{
public Department()
{
Employees = new HashSet<Employee>();
}
public int DepartmentId { get; set; }
public string Name { get; set; }
public int CompanyId { get; set; }
public virtual Company Company { get; set; }
public virtual ICollection<Employee> Employees { get; set; }
}
public class Employee
{
public int EmployeeId { get; set; }
public string Name { get; set; }
public int DepartmentId { get; set; }
public virtual Department Department { get; set; }
}
Next create a class entitled Recipe8Context, and add the code from Listing 5-19 to it, ensuring that the class derives from the Entity Framework DbContext class.
Listing 5-19. Context Class
public class Recipe8Context : DbContext
{
public Recipe8Context()
: base("Recipe8ConnectionString")
{
// Disable Entity Framework Model Compatibility
Database.SetInitializer<Recipe8Context>(null);
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Company>().ToTable("Chapter5.Company");
modelBuilder.Entity<Employee>().ToTable("Chapter5.Employee");
modelBuilder.Entity<Department>().ToTable("Chapter5.Department");
}
public DbSet<Company> Companies { get; set; }
public DbSet<Department> Departments { get; set; }
public DbSet<Employee> Employees { get; set; }
}
Next add an App.Config class to the project, and add the code from Listing 5-20 to it under the ConnectionStrings section.
Listing 5-20. Connection String
<connectionStrings>
<add name="Recipe8ConnectionString"
connectionString="Data Source=.;
Initial Catalog=EFRecipes;
Integrated Security=True;
MultipleActiveResultSets=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
In the model shown in Figure 5-23, an Employee is associated with exactly one Department. Each Department is associated with exactly one Company.
Given an instance of an Employee, you want to load both her department and the department’s company. What makes this problem somewhat unique is that we already have an instance of Employee, and we want to avoid going back to the database to get another copy of the Employee just so that we can use the Include() method to obtain the related instances of Company and Department. Perhaps in your real-world problem, Employee is a very expensive entity to retrieve and materialize.
We could use the Load() method twice to load the related Department instance and then again to load the related Company instance. However, this would generate two round trips to the database. To load the related instances using just one query, we can either requery the Employee entity set using the Include() method with a query path including the Department and the Company, or combine the Reference() and Query() methods exposed by the Entry Class. The code in Listing 5-21 shows both approaches.
Listing 5-21. Inserting into the Model and Retrieving the Related Entities Using Two Slightly Different Approaches
using (var context = new EFRecipesEntities())
{
var company = new Company { Name = "Acme Products" };
var acc = new Department { Name = "Accounting", Company = company };
var ship = new Department { Name = "Shipping", Company = company };
var emp1 = new Employee { Name = "Jill Carpenter", Department = acc };
var emp2 = new Employee { Name = "Steven Hill", Department = ship };
context.Employees.Add(emp1);
context.Employees.Add(emp2);
context.SaveChanges();
}
// First approach
using (var context = new EFRecipesEntities())
{
// Assume we already have an employee
var jill = context.Employees.First(o => o.Name == "Jill Carpenter");
// Get Jill's Department and Company, but we also reload Employees
var results = context.Employees
.Include("Department.Company")
.First(o => o.EmployeeId == jill.EmployeeId);
Console.WriteLine("{0} works in {1} for {2}",
jill.Name, jill.Department.Name, jill.Department.Company.Name);
}
// More efficient approach, does not retrieve Employee again
using (var context = new EFRecipesEntities())
{
// Assume we already have an employee
var jill = context.Employees.Where(o => o.Name == "Jill Carpenter").First();
// Leverage the Entry, Query, and Include methods to retrieve Department and Company data
// without requerying the Employee table
context.Entry(jill).Reference(x => x.Department).Query().Include(y => y.Company).Load();
Console.WriteLine("{0} works in {1} for {2}",
jill.Name, jill.Department.Name, jill.Department.Company.Name);
}
The following is the output of the code in Listing 5-21:
Jill Carpenter works in Accounting for Acme Products
Jill Carpenter works in Accounting for Acme Products
How It Works
If we didn’t already have an instance of the Employee entity, we could simply use the Include() method with a query path Department.Company. This is essentially the approach we take in earlier queries. The disadvantage of this approach is that it retrieves all of the columns for the Employee entity. In many cases, this might be an expensive operation. Because we already have this object in the context, it seems wasteful to gather these columns again from the database and transmit them across the wire.
In the second query, we use the Entry() method exposed by the DbContext object to access the Employee object and perform operations against it. We then chain the Reference() and Query() methods from the DbReferenceEntity class to return a query to load the related Department object from the underlying data store. Additionally, we chain the Include() method to pull in the related Company information. As desired, this query retrieves both Department and Company data without needlessly requerying the data store for Employees data, which has already been loaded into the context.
5-9. Filtering and Ordering Related Entities
Problem
You have an instance of an entity and you want to load a related collection of entities applying both a filter and an ordering.
Solution
Suppose that you have a model like the one shown in Figure 5-24.
Figure 5-24. A model for a hotel reservation system
Let’s assume we have an instance of a Hotel entity. To retrieve the executive suite rooms for the hotel, see which have reservations, and order them by room rate, use the pattern shown in Listing 5-22.
Listing 5-22. Filtering and Ordering an Entity Collection Using Explicit Loading Along with the Entry() and Query() Methods
using (var context = new EFRecipesEntities())
{
var hotel = new Hotel { Name = "Grand Seasons Hotel" };
var r101 = new Room { Rate = 79.95M, Hotel = hotel };
var es201 = new ExecutiveSuite { Rate = 179.95M, Hotel = hotel };
var es301 = new ExecutiveSuite { Rate = 299.95M, Hotel = hotel };
var res1 = new Reservation { StartDate = DateTime.Parse("3/12/2010"),
EndDate = DateTime.Parse("3/14/2010"), ContactName = "Roberta Jones", Room = es301 };
var res2 = new Reservation { StartDate = DateTime.Parse("1/18/2010"),
EndDate = DateTime.Parse("1/28/2010"), ContactName = "Bill Meyers", Room = es301 };
var res3 = new Reservation { StartDate = DateTime.Parse("2/5/2010"),
EndDate = DateTime.Parse("2/6/2010"), ContactName = "Robin Rosen", Room = r101 };
es301.Reservations.Add(res1);
es301.Reservations.Add(res2);
r101.Reservations.Add(res3);
hotel.Rooms.Add(r101);
hotel.Rooms.Add(es201);
hotel.Rooms.Add(es301);
context.Hotels.Add(hotel);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
// Assume we have an instance of hotel
var hotel = context.Hotels.First();
// Explicit loading with Load() provides opportunity to filter related data
// obtained from the Include() method
context.Entry(hotel)
.Collection(x => x.Rooms)
.Query()
.Include(y => y.Reservations)
.Where(y => y is ExecutiveSuite && y.Reservations.Any())
.Load();
Console.WriteLine("Executive Suites for {0} with reservations", hotel.Name);
foreach (var room in hotel.Rooms)
{
Console.WriteLine(" Executive Suite {0} is {1} per night",
room.RoomId.ToString(), room.Rate.ToString("C"));
Console.WriteLine("Current reservations are:");
foreach (var res in room.Reservations.OrderBy(r => r.StartDate))
{
Console.WriteLine(" {0} thru {1} ({2})", res.StartDate.ToShortDateString(),
res.EndDate.ToShortDateString(), res.ContactName);
}
}
}
The following is the output of the code shown in Listing 5-22:
Executive Suites for Grand Seasons Hotel with reservations
Executive Suite 65 is $299.95 per night
Current reservations are:
1/18/2010 thru 1/28/2010 (Bill Meyers)
3/12/2010 thru 3/14/2010 (Roberta Jones)
Executive Suite 64 is $79.95 per night
Current reservations are:
2/5/2010 thru 2/6/2010 (Robin Rosen)
Executive Suite 66 is $179.95 per night
How It Works
The code in Listing 5-22 uses explicit loading to retrieve a collection of related entity objects and perform filtering and ordering on them.
Along with lazy and eager loading, explicit loading is the third option for loading related data. When explicitly loading data, you are in full control. You issue commands that retrieve the data. You control if, when, and where related data is brought into the context object.
To implement explicit loading, you start with the Entry() method that is exposed by the DbContext object. Entry() accepts an argument that represents the parent entity that you wish to query. Entry() provides a wealth of information about the entity, including access to the related entity objects via the Collection() and Reference() methods.
In the example above, we start with the parent entity, Hotel, and then query related Room entities by chaining the Collection() method and passing in the navigation property, Rooms, as a parameter. The associated Query() method from the DbCollectionEntry class generates a query to load the room objects from the underlying data store.
Finally, we eagerly load the related reservations for each room by querying the Reservations navigation property as a parameter to the Include() method, applying where clause filters to retrieve only the collection of rooms of type ExecutiveSuite that have at least one reservation. We then order the collection by room rate using an OrderBy clause.
Normally, the Include() method returns all related objects for a parent with no opportunity to filter or manipulate the result set. The exception to this rule is when implementing explicit loading. As demonstrated here, we are able to filter and sort the results from related Reservation entities.
Keep in mind that we can only apply filters against related data from an Include() method using this pattern. This feature is not available when implementing lazy loading or eager loading.
5-10. Executing Aggregate Operations on Related Entities
Problem
You want to apply an aggregate operator on a related entity collection without loading the entire collection. Additionally, you want to implement the Code-First approach for Entity Framework 6 to manage data access.
Solution
Suppose that you have a model like the one shown in Figure 5-25.
Figure 5-25. Orders and their associated order items
Start by adding a console application project to Visual Studio entitled Recipe10. Be certain to reference the Entity Framework 6 libraries. Leveraging the NuGet Package Manager does this best. Right-click on Reference, and select Manage NuGet Packages. From the Online tab, locate and install the Entity Framework 6 package. Doing so will download, install, and configure the Entity Framework 6 libraries in your project.
Next we create three entity objects. Create two classes: Order and OrderItem, and copy the code from Listing 5-23 into the classes.
Listing 5-23. Entity Classes
public class Order
{
public Order()
{
OrderItems = new HashSet<OrderItem>();
}
public int OrderId { get; set; }
public System.DateTime OrderDate { get; set; }
public string CustomerName { get; set; }
public virtual ICollection<OrderItem> OrderItems { get; set; }
}
public class OrderItem
{
public int OrderItemId { get; set; }
public int OrderId { get; set; }
public int SKU { get; set; }
public int Shipped { get; set; }
public decimal UnitPrice { get; set; }
public virtual Order Order { get; set; }
}
Next create a class entitled Recipe10Context and add the code from Listing 5-24 to it, ensuring the class derives from the Entity Framework DbContext class.
Listing 5-24. Context Class
public class Recipe10Context : DbContext
{
public Recipe10Context()
: base("Recipe10ConnectionString")
{
// Disable Entity Framework Model Compatibility
Database.SetInitializer<Recipe10Context>(null);
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Order>().ToTable("Chapter5.Order");
modelBuilder.Entity<OrderItem>().ToTable("Chapter5.OrderItem");
}
public DbSet<Order> Orders { get; set; }
public DbSet<OrderItem> OrderItems { get; set; }
}
Next, add an App.Config class to the project and add the code from Listing 5-25 to it under the ConnectionStrings section.
Listing 5-25. Connection String
<connectionStrings>
<add name="Recipe10ConnectionString"
connectionString="Data Source=.;
Initial Catalog=EFRecipes;
Integrated Security=True;
MultipleActiveResultSets=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
In Figure 5-25, we have a simple model composed of an order and the products (collection of OrderItems) shipped for the order. One way to get the total amount for the order is to use the Load() method to load the entire collection of order items and then iterate through this collection, calculating the sum of the amount for each order item.
Another way to get the same result is to push the iteration to the database, letting it compute the total amount. The advantage to this second approach is that we avoid the potentially costly overhead of materializing each order item for the sole purpose of summing the total order amount. To implement this second approach, follow the pattern shown in Listing 5-26.
Listing 5-26. Applying an Aggregate Operator on Related Entities Without Loading Them
using (var context = new EFRecipesEntities())
{
var order = new Order { CustomerName = "Jenny Craig", OrderDate = DateTime.Parse("3/12/2010") };
var item1 = new OrderItem { Order = order, Shipped = 3, SKU = 2827, UnitPrice = 12.95M };
var item2 = new OrderItem { Order = order, Shipped = 1, SKU = 1918, UnitPrice = 19.95M };
var item3 = new OrderItem { Order = order, Shipped = 3, SKU = 392, UnitPrice = 8.95M };
order.OrderItems.Add(item1);
order.OrderItems.Add(item2);
order.OrderItems.Add(item3);
context.Orders.Add(order);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
// Assume we have an instance of Order
var order = context.Orders.First();
// Get the total order amount
var amt = context.Entry(order)
.Collection(x => x.OrderItems)
.Query()
.Sum(y => y.Shipped * y.UnitPrice);
Console.WriteLine("Order Number: {0}", order.OrderId);
Console.WriteLine("Order Date: {0}", order.OrderDate.ToShortDateString());
Console.WriteLine("Order Total: {0}", amt.ToString("C"));
}
The following is the output of the code in Listing 5-26:
Order Number: 6
Order Date: 3/12/2010
Order Total: $85.65
How It Works
In Listing 5-26, we implement explicit loading and start with the Entry() method that is exposed by the DbContext object. Entry() accepts an argument of Order, which represents the parent entity that we wish to query. Entry() provides a wealth of information about the Order, including access to related entity objects via the Collection() and Reference() methods.
In the example above, we query related Order Items entities by chaining the Collection() method and passing in the navigation property, OrderItems, as a parameter. The associated Query() method from the DbCollectionEntry class generates a query to load the Order Item objects from the underlying data store.
Finally, we apply the Sum() LINQ extension method, passing in a lambda expression that calculates the item total. The resulting sum over the collection is the order total. This entire expression is converted to the appropriate store layer commands and executed in the storage layer, saving the cost of materializing each order item.
This simple example demonstrates the flexibility of combining explicit loading with the Entry() and Query() method to modify the query used to retrieve the underlying associated entity collection (OrderItems). In this case, we leveraged the query, summing the amounts for OrderItems that are related to the first order without actually loading the collection.
5-11. Testing Whether an Entity Reference or Entity Collection Is Loaded
Problem
You want to test whether the related entity or entity collection is loaded in the context. Additionally, you want to implement the Code-First approach for Entity Framework 6 to manage data access.
Solution
Suppose that you have a model like the one shown in Figure 5-26.
Figure 5-26. A model for projects, managers, and contractors
Start by adding a console application project to Visual Studio entitled Recipe11. Be certain to reference the Entity Framework 6 libraries. Leveraging the NuGet Package Manager does this best. Right-click on Reference, and select Manage NuGet Packages. From the Online tab, locate and install the Entity Framework 6 package. Doing so will download, install, and configure the Entity Framework 6 libraries in your project.
Next we create three entity objects: Contractor, Manager, and Project. Then copy the code from Listing 5-27 into the classes.
Listing 5-27. Entity Classes
public class Contractor
{
public int ContracterID { get; set; }
public string Name { get; set; }
public int ProjectID { get; set; }
public virtual Project Project { get; set; }
}
public class Manager
{
public Manager()
{
Projects = new HashSet<Project>();
}
public int ManagerID { get; set; }
public string Name { get; set; }
public virtual ICollection<Project> Projects { get; set; }
}
public class Project
{
public Project()
{
Contractors = new HashSet<Contractor>();
}
public int ProjectID { get; set; }
public string Name { get; set; }
public int ManagerID { get; set; }
public virtual ICollection<Contractor> Contractors { get; set; }
public virtual Manager Manager { get; set; }
}
Next create a class entitled Recipe11Context, and add the code from Listing 5-28 to it, ensuring the class derives from the Entity Framework DbContext class.
Listing 5-28. Context Class
public class Recipe11Context : DbContext
{
public Recipe11Context()
: base("Recipe11ConnectionString")
{
// Disable Entity Framework Model Compatibility
Database.SetInitializer<Recipe11Context>(null);
}
public DbSet<Contractor> Contractors { get; set; }
public DbSet<Manager> Managers { get; set; }
public DbSet<Project> Projects { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Contractor>().ToTable("Chapter5.Contractor");
modelBuilder.Entity<Manager>().ToTable("Chapter5.Manager");
modelBuilder.Entity<Project>().ToTable("Chapter5.Project");
// Explilcitly map key for Contractor entity
modelBuilder.Entity<Contractor>().HasKey(x => x.ContracterID);
}
}
Next add an App.Config class to the project, and add the code from Listing 5-29c to it under the ConnectionStrings section.
Listing 5-29. Connection String
<connectionStrings>
<add name="Recipe11ConnectionString"
connectionString="Data Source=.;
Initial Catalog=EFRecipes;
Integrated Security=True;
MultipleActiveResultSets=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
Entity Framework exposes the IsLoaded property that it sets to true when it is 100% certain that all data from the specified entity or entity collection is loaded and available in the context. The model in Figure 5-26 represents projects, the managers for the projects, and the contractors that work on the projects. To test whether a related entity is loaded into the context object, follow the pattern shown in Listing 5-30.
Listing 5-30. Using IsLoaded to Determine Whether an Entity or Entity Collection Is in the Context
using (var context = new EFRecipesEntities())
{
var man1 = new Manager { Name = "Jill Stevens" };
var proj = new Project { Name = "City Riverfront Park", Manager = man1 };
var con1 = new Contractor { Name = "Robert Alvert", Project = proj };
var con2 = new Contractor { Name = "Alan Jones", Project = proj };
var con3 = new Contractor { Name = "Nancy Roberts", Project = proj };
context.Projects.Add(proj);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
var project = context.Projects.Include("Manager").First();
if (context.Entry(project).Reference(x => x.Manager).IsLoaded)
Console.WriteLine("Manager entity is loaded.");
else
Console.WriteLine("Manager entity is NOT loaded.");
if (context.Entry(project).Collection(x => x.Contractors).IsLoaded)
Console.WriteLine("Contractors are loaded.");
else
Console.WriteLine("Contractors are NOT loaded.");
Console.WriteLine("Calling project.Contractors.Load()...");
context.Entry(project).Collection(x => x.Contractors).Load();
if (context.Entry(project).Collection(x => x.Contractors).IsLoaded)
Console.WriteLine("Contractors are now loaded.");
else
Console.WriteLine("Contractors failed to load.");
}
The following is the output from the code in Listing 5-30:
Manager entity is loaded.
Contractors are NOT loaded.
Calling project.Contractors.Load()...
Contractors are now loaded.
How It Works
We start by using the Include() method to eagerly load the Project entity together with its related Manager for the first Project from the data store.
After the query, we check whether the manager instance is loaded by obtaining a reference to the related Manager entity using the Reference() method and checking the value of the IsLoaded property. Because this is an entity reference (reference to a single parent entity), the IsLoaded property is available on the Reference property of the DbEntityEntry type that is returned for calling the Entry() method. As we loaded both Projects and Manager, the IsLoaded property returns true.
Next we check whether the Contractor entity collection is loaded. It is not loaded because we didn’t eagerly load it with the Include() method, nor did we load it directly (yet) with the Load() method. Once we fetch it with the Load() method, the IsLoaded property for it is set to true.
When lazy loading is enabled on the context object, which is the default behavior, the IsLoaded property is set to true when the entity or entity collection is referenced. Lazy loading causes Entity Framework to load the entity or entity collection automatically when referenced. Explicit loading is similar to lazy loading, but is not automatic. Instead, the developer must explicitly load the related entity with the Load() method, giving the developer complete control over if and when related entities are loaded.
The exact meaning of IsLoaded can be a little more confusing than it seems it should be. IsLoaded is set by the results of a query by calling the Load() method, or implicitly by the span of relationship keys. When you query for an entity, there is an implicit query for the key of the related entity. If the result of this implicit query is a null key value, then IsLoaded is set to true, indicating that there is no related entity in the database. This is the same value for IsLoaded that we would expect if we did an explicit load on the relationship and found no related entity.
5-12. Loading Related Entities Explicitly
Problem
You want to load related entities directly, without relying on the default lazy loading behavior of Entity Framework.
Solution
Let’s say that you have a model like the one in Figure 5-27.
Figure 5-27. A model for doctors, their patients, and appointments
The model depicted in Figure 5-27 represents doctors, their patients, and appointments. To explicitly load related entities, follow the pattern in Listing 5-31.
Listing 5-31. Using the Load() Method
using (var context = new EFRecipesEntities())
{
// disable lazy loading feature as we are explicitly loading
// child entities
context.Configuration.LazyLoadingEnabled = false;
var doctorJoan = context.Doctors.First(o => o.Name == "Joan Meyers");
if (!context.Entry(doctorJoan).Collection(x => x.Appointments).IsLoaded)
{
context.Entry(doctorJoan).Collection(x => x.Appointments).Load();
Console.WriteLine("Dr. {0}'s appointments were explicitly loaded.",
doctorJoan.Name);
}
Console.WriteLine("Dr. {0} has {1} appointment(s).",
doctorJoan.Name,
doctorJoan.Appointments.Count());
foreach (var appointment in context.Appointments)
{
if (!context.Entry(appointment).Reference(x => x.Doctor).IsLoaded)
{
context.Entry(appointment).Reference(x => x.Doctor).Load();
Console.WriteLine("Dr. {0} was explicitly loaded.",
appointment.Doctor.Name);
}
else
Console.WriteLine("Dr. {0} was already loaded.",
appointment.Doctor.Name);
}
Console.WriteLine("There are {0} appointments for Dr. {1}",
doctorJoan.Appointments.Count(),
doctorJoan.Name);
doctorJoan.Appointments.Clear();
Console.WriteLine("Collection clear()'ed");
Console.WriteLine("There are now {0} appointments for Dr. {1}",
doctorJoan.Appointments.Count(),
doctorJoan.Name);
context.Entry(doctorJoan).Collection(x => x.Appointments).Load();
Console.WriteLine("Collection loaded()'ed");
Console.WriteLine("There are now {0} appointments for Dr. {1}",
doctorJoan.Appointments.Count().ToString(),
doctorJoan.Name);
// Currently, there isn't an easy way to refresh entities with the DbContext API.
// Instead, drop down into the ObjectContext and perform the following actions
var objectContext = ((IObjectContextAdapter)context).ObjectContext;
var objectSet = objectContext.CreateObjectSet<Appointment>();
objectSet.MergeOption = MergeOption.OverwriteChanges;
objectSet.Load();
Console.WriteLine("Collection loaded()'ed with MergeOption.OverwriteChanges");
Console.WriteLine("There are now {0} appointments for Dr. {1}",
doctorJoan.Appointments.Count(),
doctorJoan.Name);
}
// Demonstrating loading part of the collection then Load()'ing the rest
using (var context = new EFRecipesEntities())
{
// disable lazy loading feature as we are explicitly loading
// child entities
context.Configuration.LazyLoadingEnabled = false;
// Load the first doctor and attach just the first appointment
var doctorJoan = context.Doctors.First(o => o.Name == "Joan Meyers");
context.Entry(doctorJoan).Collection(x => x.Appointments).Query().Take(1).Load();
Console.WriteLine("Dr. {0} has {1} appointments loaded.",
doctorJoan.Name,
doctorJoan.Appointments.Count());
// When we need all of the remaining appointments, simply Load() them
context.Entry(doctorJoan).Collection(x => x.Appointments).Load();
Console.WriteLine("Dr. {0} has {1} appointments loaded.",
doctorJoan.Name,
doctorJoan.Appointments.Count());
}
The output of the code in Listing 5-31 is the following:
Dr. Joan Meyers's appointments were explicitly loaded
Dr. Joan Meyers has 2 appointment(s)
Dr. Joan Meyers was already loaded
Dr. Steven Mills was lazy loaded
Dr. Joan Meyers was already loaded
There are 2 appointments for Dr. Joan Meyers
Collection clear()'ed
There are now 0 appointments for Dr. Joan Meyers
Collection loaded()'ed
There are now 0 appointments for Dr. Joan Meyers
Collection loaded()'ed with MergeOption.OverwriteChanges
There are now 2 appointments for Dr. Joan Meyers
Dr. Joan Meyers has 2 appointments loaded
Dr. Joan Meyers has 2 appointments loaded
How It Works
After inserting some sample data into our database, we explicitly disable the lazy loading feature of Entity Framework, as we want to explicitly control the loading of related child entities. We can disable lazy loading in one of two ways:
- Set the LazyLoadingEnabled property from the Context.Configuration object to false. This approach disables lazy loading for all entities assigned to the context.
- Remove the virtual access modifier from each navigation property in each entity class. This approach disables lazy loading per entity class, giving you explicit control of lazy loading.
The first bit of code retrieves an instance of the Doctor entity. If you are using the explicit loading approach, it would be a good practice to use the IsLoaded property to check whether the entity or entity collection is already loaded. In the code, we check whether the doctor’s appointments are loaded. If not, we use the Load() method to load them.
In the foreach loop, we iterate through the appointments, checking if the associated doctor is loaded. Notice in the output that one doctor was already loaded while the other one was not. This is because our first query retrieved this doctor. During the retrieval process for the appointments, Entity Framework connected the loaded instance of the doctor with her appointments. This process is informally referred to as relationship fixup . Relationship fixup will not fix up all associations. In particular, it will not tie in entities across a many-to-many association.
In the last bit of code, we print the number of appointments we have for the doctor. Then we clear the collection from the context using the Clear() method. The Clear() method removes the relationship between the Doctor and appointments entity objects.. Interestingly, it does not remove the instances from memory;they are still in the context—they are just no longer connected to this instance of the Doctor entity.
Somewhat surprisingly, after we call Load() to reload the appointments, we see from the output that no appointments are in our collection! What happened? It turns out that the Load() method is overloaded to take a parameter that controls how the loaded entities are merged into the context. The default behavior for the Load() method is MergeOption.AppendOnly, which simply appends instances that are not already in the context. In our case, none of the appointments was actually removed from the context. Our use of the Clear() method simply removed them from the entity collection, not the context. When we called Load() with the default MergeOption.AppendOnly, no new instances were found, so nothing was added to the entity collection. Other merge options include NoTracking, OverwriteChanges, and PreserveChanges. When we use the OverwriteChanges option, the appointments appear in the Doctor’s Appointments.
Note in our code how we drop down into the underlying ObjectContext object in order to gain access to the MergeOption behaviors exposed by Entity Framework. The MergeOption type is not directly available in the DbContext. You’ll recall that when using Entity Framework, there are two context objects available for use. The preferred context object for Entity Framework 6 is the DbContext object, which provides an intuitive and easy-to-use facade around the legacy Object Context object. The older Object Context object is still available through an explicit cast against the DbContext object, as demonstrated in our recipe.
Along with AppendOnly, the MergeOption type exposes three other options:
- The NoTracking option turns off object state tracking for the loaded instances. With NoTracking, Entity Framework will not track changes to the object and will not be aware that the object is loaded into the context. The NoTracking option can be used on a navigation property of an object only if the object was loaded with the NoTracking option. NoTracking has one additional side effect. If we had loaded an instance of the Doctor entity with NoTracking, loading the appointments with the Load() method would also occur with NoTracking, regardless of the default AppendOnly option.
- The OverwriteChanges option will update the values in the current instance with that from the database. Entity Framework will continue to use the same instance of the entity object. This option is particularly useful if you need to discard changes made in the context and refresh them from the database. This would be helpful, for example, in implementing an undo operation in an application.
- The PreserveChanges option is, essentially, the opposite of the OverwriteChanges option. It will update the values of any entities that have database changes, but no in-memory changes. An entity that has been modified in memory will not be refreshed. To be precise, the current value of an entity modified in memory will not be changed, but the original value will be updated if it has changed on the database.
There are some restrictions on when you can use Load(). Load() cannot be called on an entity that is in the Added, Deleted, or Detached state.
The Load() method can be helpful in improving performance by restricting how much of a collection is loaded at any one time. For example, suppose our doctors had lots of appointments, but in many cases we needed to work with just a few of them. In the rare case that we need the entire collection, we can simply call Load() to append the remaining appointment instances to the context. This is demonstrated in the code snippet in Listing 5-32.
Listing 5-32. Code Snippet Demonstrating Partial Loading of an Entity Collection
// Demonstrating loading part of the collection then Load()'ing the rest
using (var context = new EFRecipesEntities())
{
// Load the first doctor and attach just the first appointment
var doctorJoan = context.Doctors.First(o => o.Name == "Joan Meyers");
context.Entry(doctorJoan).Collection(x => x.Appointments).Query().Take(1).Load();
// note that IsLoaded returns false here since all related data has not been loaded into the context
var appointmentsLoaded = context.Entry(doctorJoan).Collection(x => x.Appointments).IsLoaded; Console.WriteLine("Dr. {0} has {1} appointments loaded.",
doctorJoan.Name,
doctorJoan.Appointments.Count());
// When we need all of the remaining appointments, simply Load() them
context.Entry(doctorJoan).Collection(x => x.Appointments).Load();
Console.WriteLine("Dr. {0} has {1} appointments loaded.",
doctorJoan.Name,
doctorJoan.Appointments.Count());
}
The output of the code snippet in Listing 5-12b is the following:
Dr. Joan Meyers has 1 appointments loaded.
Dr. Joan Meyers has 2 appointments loaded.
5-13. Filtering an Eagerly Loaded Entity Collection
Problem
You want to filter an eagerly loaded collection. Additionally, you want to implement the Code-First approach for Entity Framework 6 to manage data access.
Solution
Entity Framework does not directly support filtering with the Include() method, but we can accomplish the same thing by creating an anonymous type that includes the entity along with the filtered collection of related entities.
Let’s assume that you have a model like the one in Figure 5-28.
Figure 5-28. A model for movies and their categories
Start by adding a console application project to Visual Studio entitled Recipe13. Be certain to reference the Entity Framework 6 libraries. Leveraging the NuGet Package Manager does this best. Right-click on Reference, and select Manage NuGet Packages. From the Online tab, locate and install the Entity Framework 6 package. Doing so will download, install, and configure the Entity Framework 6 libraries in your project.
Next we create three entity objects. Create two classes: Category and Movie, and copy the code from Listing 5-33 into the classes.
Listing 5-33. Entity Classes
public class Category
{
public Category()
{
Movies = new HashSet<Movie>();
}
public int CategoryId { get; set; }
public string Name { get; set; }
public string ReleaseType { get; set; }
public virtual ICollection<Movie> Movies { get; set; }
}
public class Movie
{
public int MovieId { get; set; }
public string Name { get; set; }
public string Rating { get; set; }
public int CategoryId { get; set; }
public virtual Category Category { get; set; }
}
Next create a class entitled Recipe13Context and add the code from Listing 5-34 to it, ensuring the class derives from the Entity Framework DbContext class.
Listing 5-34. Context Class
public class Recipe13Context : DbContext
{
public Recipe13Context()
: base("Recipe13ConnectionString")
{
// Disable Entity Framework Model Compatibility
Database.SetInitializer<Recipe13Context>(null);
}
public DbSet<Category> Categories { get; set; }
public DbSet<Movie> Movies { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Category>().ToTable("Chapter5.Category");
modelBuilder.Entity<Movie>().ToTable("Chapter5.Movie");
}
}
Next add an App.Config class to the project, and add the code from Listing 5-35 to it under the ConnectionStrings section.
Listing 5-35. Connection String
<connectionStrings>
<add name="Recipe13ConnectionString"
connectionString="Data Source=.;
Initial Catalog=EFRecipes;
Integrated Security=True;
MultipleActiveResultSets=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
To eagerly load and filter both the categories and their associated movies, follow the pattern in Listing 5-36.
Listing 5-36. Filtering an Eagerly Loaded Entity Collection
using (var context = new EFRecipesEntities())
{
var cat1 = new Category { Name = "Science Fiction", ReleaseType = "DVD" };
var cat2 = new Category { Name = "Thriller", ReleaseType = "Blu-Ray" };
new Movie { Name = "Return to the Moon", Category = cat1, Rating = "PG-13" };
new Movie { Name = "Street Smarts", Category = cat2, Rating = "PG-13" };
new Movie { Name = "Alien Revenge", Category = cat1, Rating = "R" };
new Movie { Name = "Saturday Nights", Category = cat1, Rating = "PG-13" };
context.Categories.AddObject(cat1);
context.Categories.AddObject(cat2);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
// filter on ReleaseType and Rating
// create collection of anonymous types
var cats = from c in context.Categories
where c.ReleaseType == "DVD"
select new
{
category = c,
movies = c.Movies.Where(m => m.Rating == "PG-13")
};
Console.WriteLine("PG-13 Movies Released on DVD");
Console.WriteLine("============================");
foreach (var cat in cats)
{
Category category = cat.category;
Console.WriteLine("Category: {0}", category.Name);
foreach (var movie in cat.movies)
{
Console.WriteLine(" Movie: {0}", movie.Name);
}
}
}
The code in Listing 5-36 produces the following output:
PG-13 Movies Released on DVD
============================
Category: Science Fiction
Movie: Return to the Moon
Movie: Saturday Nights
How It Works
We start off in Listing 5-36 by creating and initializing the categories and movies. To keep things short, we’ve created only a couple of categories and four movies.
In the query, we create a collection of anonymous types with the category instance and the filtered collection of movies in the category. The query also filters the category collection, retrieving only categories whose movies are released on DVD. In this example, just one category was released on DVD. Here we rely on relationship span to attach the movies to the categories.
This approach of leveraging an anonymous type helps gets around the limitation in eager loading that prevents us from filtering an eagerly loaded collection. Note that when explicitly loading, we do have the ability to filter an eagerly loaded collection, as demonstrated in some of the earlier recipes in this chapter. Keep in mind that anonymous types only have scope in the method in which they are created—we cannot return anonymous types from a method. If our goal were to return the entity set for further processing in the application, then we would want to create an explicit type into which we could load the data and then return from a method. In our example, that explicit type would be a simple class with two properties: Category and a collection of Movies.
5-14. Modifying Foreign Key Associations
Problem
You want to modify a foreign key association.
Solution
Entity Framework provides a couple of ways to modify a foreign key association. You can add the associated entity to a navigation property collection or assign it to a navigation property. You can also set the foreign key value with the associated entity’s key value.
Suppose that you have a model like the one shown in Figure 5-29.
Figure 5-29. A model for clients and invoices
To modify the foreign key association between client entities and invoice entities in two different ways, do the following:
- Right-click your project, and select Add New
ADO.NET Entity Data Model. Import the Client and Invoice tables. Be certain that the Include foreign key columns in the model
check box is checked, which is the default behavior, as shown in Figure 5-30. Doing so will import foreign key associations from the database that are not many-to-many relationships.
Figure 5-30. Checking the Include foreign key columns in the model check box will create foreign key associations for database relationships that are not many-to-many
- Use the code in Listing 5-37 to demonstrate the ways in which a foreign key association can be modified.
Listing 5-37. Demonstrating the Ways in Which a Foreign Key Association Can Be Modified
using (var context = new EFRecipesEntities())
{
var client1 = new Client { Name = "Karen Standfield", ClientId = 1 };
var invoice1 = new Invoice { InvoiceDate = DateTime.Parse("4/1/10"), Amount = 29.95M };
var invoice2 = new Invoice { InvoiceDate = DateTime.Parse("4/2/10"), Amount = 49.95M };
var invoice3 = new Invoice { InvoiceDate = DateTime.Parse("4/3/10"), Amount = 102.95M };
var invoice4 = new Invoice { InvoiceDate = DateTime.Parse("4/4/10"), Amount = 45.99M };
// add the invoice to the client's collection
client1.Invoices.Add(invoice1);
// assign the foreign key directly
invoice2.ClientId = 1;
// Attach() an existing row using a "fake" entity
context.Database.ExecuteSqlCommand("insert into chapter5.client values (2, 'Phil Marlowe')");
var client2 = new Client { ClientId = 2 };
context.Clients.Attach(client2);
invoice3.Client = client2;
// using the ClientReference
invoice4.Client = client1;
// save the changes
context.Clients.Add(client1);
context.Invoices.Add(invoice2);
context.Invoices.Add(invoice3);
context.Invoices.Add(invoice4);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
foreach (var client in context.Clients)
{
Console.WriteLine("Client: {0}", client.Name);
foreach (var invoice in client.Invoices)
{
Console.WriteLine(" {0} for {1}", invoice.InvoiceDate.ToShortDateString(),
invoice.Amount.ToString("C"));
}
}
}
The following is the output of the code in Listing 5-37:
Client: Karen Standfield
4/1/2010 for $29.95
4/4/2010 for $45.99
4/2/2010 for $49.95
Client: Phil Marlowe
4/3/2010 for $102.95
How It Works
Entity Framework supports independent associations and foreign key associations. For an independent association, the association between the entities is tracked separately from the entities, and the only way to change the association is through object references.
With foreign key associations, you can change the association by changing object references or by directly changing the foreign key property value. Foreign key associations are not used for many-to-many relationships.
Note Keep in mind that Foreign Key Associations are simpler, easier, the default approach and recommended by the Entity Framework team. Unless you have a concrete business reason to use an Independent Association, always consider using a Foreign Key Association.
Table 5-1 illustrates the main differences between foreign key associations and independent associations.
Table 5-1. The Differences Between Foreign Key Associations and Independent Associations
| Foreign Key Association | Independent Association |
|---|---|
| Can be set using foreign key and navigation properties | Can only be set using a navigation property |
| Is mapped as a property and does not require a separate mapping | Is tracked independently from the entity, which means that changing the association does not change the state of the entity |
| Data binding scenarios are easier because they can bind to a property value. | Data binding is complicated because you have to create a property manually, which reads the foreign key value from the entity key, or traverse the navigation property to load the related key. |
| Finding the old value for a foreign key is easier because it is a property of an entity. | Accessing an old relationship is complicated because relationships are tracked separately. |
| To delete an entity that uses a foreign key association, you only need the entity key. | To delete an entity that uses an independent association, you need the entity key and the original values for all reference keys. |
| N-Tier scenarios are easier because you don’t have to send the related end’s entity key along with the entity. | The client must send the related end’s entity key value along with the entity when the entity is attached. Entity Framework will create a stub entry, and the update statement includes the related end’s entity key. |
| Three representations of the same association are kept in sync: the foreign key, the reference, and the collection navigation property on the other side. Entity Framework handles this with the default code generation. | Two representations are kept in sync: the reference and the navigation property |
| When you load a related entity, Entity Framework uses the foreign key value currently assigned on the entity, not the foreign key value in the database. | When you load a related entity, the foreign key value is read from the database and, based on this value, the related entity is loaded. |