Using include does not change behavior

Question

Can someone help me clarify the difference between:

var query = awlt.People.Include(p => p.EmailAddresses) .Where(p => p.LastName.Equals(lastName)) .SelectMany(a => a.EmailAddresses) .Select(a => a.EmailAddress1); 

---

  var query = awlt.People .Where(p => p.LastName.Equals(lastName)) .SelectMany(a => a.EmailAddresses) .Select(a => a.EmailAddress1); 

---

I get the same results in both cases, not knowing the difference. Is required loading required with Include ?

Answer 1

Both requests retrieve related data only from the first request using Lively loading (and yes. Achieved using the Include method, you guessed it), and the second request using Lazy loading , which is the default. But since your request returns only EmailAddresses due to Select() and SelectMany() , the Include() method does not change the behavior . To see when the Include() method matters in your example, read the following lines, which I will prove in one example:

To find out some difference between these two types of objects related to loading The desired loading is usually more efficient when you need the appropriate data for all the extracted rows of the primary table. And also when the relationship not too much, loaded loading will be good practice to reduce further server requests. But when you know that you don't need real estate instantly, then lazy loading might be a good choice. Also the desired download is a good choice in a situation where your db context is deleted and the lazy loading can no longer be. To prove that Lazy Loading and one is Eager Loading, consider the following code:

 public List<Person> GetEmailAddresses() { using (yourEntities awlt = new yourEntities()) { var query = awlt.People .Where(p => p.LastName.Equals(lastName)); return query.ToList(); } } 

After calling this method, you cannot load the associated object lazily because db is located. To prove this, follow these steps:

 var query = GetEmailAddresses(); foreach (var item in query.SelectMany(a => a.EmailAddresses).Select(a => a.EmailAddress1)) { MessageBox.Show(item); } 

And you will get this error:

The ObjectContext instance has been deleted and can no longer be used for operations that require a connection.

But if you change GetEmailAddresses to use Eager Loading as follows:

 public List<Person> GetEmailAddresses() { using (yourEntities awlt = new yourEntities()) { var query = awlt.People.Include("EmailAddresses") .Where(p => p.LastName.Equals(lastName)); return query.ToList(); } } 

Then the code below should work fine:

 var query = GetEmailAddresses(); foreach (var item in query.SelectMany(a => a.EmailAddresses).Select(a => a.EmailAddress1)) { MessageBox.Show(item); } 

So, in a situation where your db context will be deleted, Eager Loading will be the best choice.

Answer 2

I don’t know about EF 7, but in EF 6 both of these statements produce the same database queries and are essentially the same. There is no lazy loading, no active loading (in a sense, this term is commonly used).

You need Include only the properties of the objects that you materialize. In the above example, you materialize Person.EmailAddresses.EmailAddress1 , but you only include Person.EmailAddresses - this has no effect (for more details see, For example, here ).

Consider this code example (the details do not matter, there is only an Error object with the property to navigate the code):

 // note we materialized query var errors = ctx.Errors.Include(c => c.Code).ToArray(); // no lazy loading happens here - we already loaded all related Codes with Include var codeIds = errors.Select(c => c.Code.CodeID).ToArray(); 

And this one:

 // no need to include here! var codeIds = ctx.Errors.Select(c =>c.Code.CodeID).ToArray(); 

And with the inclusion of:

 // include has no effect here! var codeIds = ctx.Errors.Inlcude(c => c.Code).Select(c => c.Code.CodeID).ToArray(); 

What is a download? This is when you include additional data in a related object using the Include statement. Here the Include statement has no effect, it just does nothing, so we cannot name this loadable download.

What is lazy loading? This is when the navigation property is loaded the first time it is accessed. You do not do this in your examples, so there is no lazy loading.

Both examples simply perform identical database queries (after you materialize them with the enumeration "ToArray", etc.).

Answer 3

The result of the two queries is exactly the same (also about the "impatient" and "lazy" load).
In this case, I think the request is also very similar or the same, but never trusts the requests created by the EF provider. To view the generated requests, you can stop the program with a breakpoint and look at the request object (pointing to it with the mouse pointer). This is a query created by an EF provider.

About loading the Eager (Include statement) in this case it should not be useful because it is used to load the properties of the output object. In this case, you select EMailAddress1, so using Include you can load the properties of EMailAddress1 (and avoid lazy requests while accessing EMailAddress1).

Answer 4

You can find the difference if you look into the SQL Server Profiler after executing the query. Thus, in your first case, the database receives only one query and selects records from the People table, as well as the EmailAddresses table, while in the second case, it makes two queries to the database and first selects People and then EmailAddresses in the second query. Thus, the first scenario is called impatient loading, and the second - lazy loading.