Sometimes, we need to filter based on related data. For example, we want to find people who have phone numbers that start with "1". It is worth noting that this additional task is performed purely based on relationships between a person and phone entities, using the Phones property on the person class, as shown in the following code snippet:

var query = from person in context.People
            where person.IsActive &&
            person.Phones.Any(ph => ph.PhoneNumber.StartsWith("1"))
            select person;

var methodQuery = context.People
    .Where(p => p.IsActive &&
        p.Phones.Any(ph => ph.PhoneNumber.StartsWith("1")));

Again, we use the plain string function, StartsWith in the preceding case, yet this will translate into LIKE '1%' in the where clause in the SQL Server case. We also use the familiar Any function to only find people with at least one phone number that starts with the number 1. We also (in a way) add the method syntax to the query syntax, giving us additional flexibility. Here is how this code looks in VB.NET:

Dim query = From person In context.People
            Where person.IsActive And
            person.Phones.Any(Function(ph) ph.PhoneNumber.StartsWith("1"))
            Select person

Dim methodQuery = context.People _
                    .Where(Function(p) p.IsActive And
                    p.Phones.Any(Function(ph) ph.PhoneNumber.StartsWith("1")))

We previously talked about the difference between eager and lazy loading. Both concepts are approaches to loading-related entities. For example, you may use either approach to load phone numbers for a person. If you are trying to decide which approach you need to use in a particular situation, the following rule should answer this question for you. If you are not sure that you are going to need related entries, you can use lazy loading. If you know you will certainly need related data, then use eager loading. You will need to be careful when you decide to eagerly load many relationships. This can result in very complex queries and thus can have performance implications. You can encounter problems with lazy loading as well. For example, you want to retrieve 100 rows from the People table and then display phone numbers for each person. If you use lazy loading, as you start enumerating through a list of phone numbers, Entity Framework will issue a query to retrieve that data for each person's phones list. This processing will result in 101 queries issued against the database. One query will retrieve 100 people, then one more for each person to get phone numbers. It takes time to perform these actions, so you may be looking at an extra few hundred milliseconds when using lazy loading in this use case. You need to carefully decide which approach is right for each of your situations. By default, lazy loading is enabled in Entity Framework. You can turn it off for an instance of DbContext by accessing configuration options on DbContext after context is created, which is shown as follows:

context.Configuration.LazyLoadingEnabled = false;

It may be useful to you to know how lazy loading is implemented in Entity Framework. If you run the following code and break on the line that executes the foreach loop that looks on phone numbers and examine an instance of the person object, you will notice something interesting. The type of the person object is not really Person, but instead something like System.Data.Entity.DynamicProxies.Person_XXXXXX. Entity Framework dynamically created a class that inherits from Person in order to intercept property getter calls: in our case, the Phones property. Then, in the property getter, it dynamically issues a query to populate the Phones list. However, your code can just assume that the data will be automatically populated. This is shown in the following code snippet:

var query = from person in context.People
            select person;

foreach (var person in query.ToList())
{
    foreach (var phone in person.Phones)
    {

Now, let's take a look at eager loading. You have to use the Include method in order to proactively load the related data you need. There is one parameter that this method takes, and it is the property expression that points to a related entities property. Let's implement the preceding code, but use eager loading, This is shown in the following code snippet:

var query = from person in context.People.Include(p => p.Phones)
            select person;

foreach (var person in query)
{

There are two overloads of the include method. The one we used took a property expression. You can also use a string to describe the path of the relationship. This is shown in the following code snippet:

var query = from person in context.People.Include("Phones")
            select person;

Ordinarily, you want to use the property expression method because you can take advantage of compile-time checking. The only time you need to use string-based overload is when your path cannot be described via a property expression. For example, if you want to load multiple levels of relationships. If Phones were to have types, your Include method may look like Include("Phones.PhoneType").

Here is how the code that uses eager loading looks in VB.NET:

Dim query = From person In context.People.Include(Function(p) p.Phones)
            Select person

For Each person As Person In query
    Console.WriteLine(person.LastName)
    For Each phone As Phone In person.Phones

You noticed that we used the ToList call before running through the query when lazy loading is used. This is typically not necessary and really not even recommended. However, in the case of lazy loading, you have to follow this pattern. The problem is that when a lazy loaded property is populated by Entity Framework, a new data reader is created. However, we already have an open data reader that is reading in our primary, top-level query, which is the person query in our case. ADO.NET has a limitation, where only one open reader is allowed per database connection. As a result, if you remove the function ToList from the code that is using lazy loading, you will encounter an exception. A call to functions such as ToList or ToArray cause what is referred to as an immediate execution of the query to occur. This is in contrast to simply enumerating the results of a query, as in the eager loading example, when we use deferred query execution. SQL is executed when query results are enumerated in both cases, but with a slight difference.