Using include doesn't change the behavior

c# entity-framework entity-framework-6 performance sql-server

Question

Could Someone help me to 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 without knowing the difference . Does the Eager Loading require using Include ?

1
6
4/30/2016 11:19:56 PM

Accepted Answer

The both query are retrieving the related data just first query by using Eager Loading (and yes Eager loading is achieved by use of the Include method as you guessed) and the second query by using Lazy loading which is by default. But since your query will only returns EmailAddresses because of the Select() and SelectMany() operations the Include() method doesn't change the behavior. To see when Include() method is matter in your example read the following lines that I will prove it in one example:

To know some difference between this two kind of loading related entities Eager loading is typically more efficient when you need the related data for all retrieved rows of the primary table. And also when relations are not too much, eager loading will be good practice to reduce further queries on server. But when you know that you will not need a property instantly then lazy loading maybe a good choice. And also eager loading is a good choice in a situation where your db context would be disposed and lazy loading could not take place anymore. To prove that one is 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 related entity lazily because the db is disposed. To prove try this:

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 disposed and can no longer be used for operations that require a connection.

But if you change the GetEmailAddresses to use Eager Loading like this:

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 below code should works 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 would be disposed the Eager Loading would be a better choice.

6
5/7/2016 5:28:55 PM

Popular Answer

Don't know about EF 7, but in EF 6 both those statements produce the same queries to database and so are essentially the same. There is no lazy loading, no eager loading (in a sense this term is usually used) whatsoever.

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

Consider this sample code (details does not matter, there is just Error entity with Code navigation property):

// 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 include:

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

What is eager loading? It's when you include additional data to the related entity using Include statement. Here Include statement has no effect, it's just does nothing, so we cannot name that eager loading.

What is lazy loading? It's when navigation property is loading when you access it for the first time. You do not do this in your examples, so there is no lazy loading either.

Both examples just execute identical queries to database (after you materialize them with enumeration`ToArray` etc).



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow