Why do I need to .Include() collections

Question

I wrote a query which is pretty simple:

var locations = await _context.Locations
                .Include(x => x.LocationsOfTheUsers)
                .Include(x => x.Address)
                    .ThenInclude(x => x.County)
                .Where(CalculateFilters(searchObj))
                .ToListAsync(cancellationToken);

And everytime LocationsOfTheUsers were null so I decided to .Include(x => x.LocationsOfTheUsers) and I received results as expected but I'm not sure why do I have to include this collections since it's defined like this:

public class Location
{
    public string Title { get; set; }
    public long? RegionId { get; set; }
    public Region Region { get; set; }
    public long? AddressId { get; set; }
    public Address Address { get; set; }
    public long? CountyId { get; set; }
    public County County { get; set; }
    public ICollection<LocationsOfTheUsers> LocationsOfTheUsers { get; set; }
}

I thought this will be automatically included since it exist as ICollection in Location class.

So why is .Include() on LocationsOfTheUsers needed here?

Thanks guys

Cheers

https://learn.microsoft.com/en-us/ef/core/querying/related-data — Johnathan Barclay, Aug 14 '20 at 14:09
Read the docs, read [ask] and show your research. Counter-question: why should Entity Framework always join all related tables (and until how deep) when querying for records? You can either use lazy loading (but don't) or include exactly what you need where you need it. — CodeCaster, Aug 14 '20 at 14:10

score 1 · Answer 1 · answered Aug 17 '20 at 09:56

In entity framework the non-virtual properties represent the columns of the tables, the virtual properties represent the relations between the tables (one-to-many, many-to-many, ...)

So your property should have been defined as:

public virtual ICollection<LocationsOfTheUsers> LocationsOfTheUsers { get; set; }

One of the slower parts of a database query is the transfer of the selected data from the database management system to your local process. Hence it is wise to limit the selected data to the values you actually plan to use.

If you have a one-to-many relation between Schools and Students, and you ask for School [10] you don't want automatically to fetch its 2000 Students.

Even if you would like to have "School [10] with all its Students" it would not be efficient to use Include to also fetch the Students. Every Student will have a foreign key SchoolId with a Value of [10]. If you would use Include you would transfer this foreign key 2000 times. What a waste!

When using entity framework always use Select to fetch data and select only the properties that you actually plan to use. Only use Include if you plan to change the included items.

This way you can separate your database table structure from the actual query. If your database structure changes, only the query changes, users of your query don't notice the internal changes.

Apart from better performance and more robustness against changes, readers of your code can more easily see what values are in their query.

Certainly don't use Include to save you some typing. Having to debug one error after future changes will take way more time than you will ever save by typeing include instead of Select

Finally: limit your data early in your process, so put the Where in front.

So your query should be:

var predicate = CalculateFilters(searchObj)
var queryLocations = dbContext.Locations
    .Where(predicate)
    .Select(location => new
    {
         // Select only the location properties that you plan to use
         Id = location.Id,
         Name = location.Name,

         // Locations Of the users:
         UserLocations = location.LocationsOfTheUsers
            .Select(userLocation => new
            {
                 // again: only the properties that you plan to use
                 Id = userLocation.Id,
                 ...

                 // Not needed, you already know the value
                 // LocationId = userLocation.LocationId
            })
            .ToList(),

            Address = new
            {
                 Street = location.Address.Street,
                 PostCode = location.Addrress.PostCode,
                 ...

                 County = location.Address.County.Name // if you only want one property

                 // or if you want more properties:
                 County = new
                 {
                     Name = location.Address.County.Name,
                     Abbr = location.Address.Count.Abbr,
                     ...
                 }),
            },
        });

score 0 · Answer 2 · answered Aug 14 '20 at 14:16

I thought this will be automatically included since it exist as ICollection in Location class.

Well, it's not automatically included, probably for performance reasons as the graph of related entities and their recursive child entities may be rather deep.

That's why you use eager loading to explicitly include the related entities that you want using the Include method.

The other option is to use lazy loading which means that the related entities are loaded as soon as you access the navigation property in your code, assuming some prerequisites are fulfilled and that the context is still around when this happens.

Please refer to the docs for more information.

@Roxy'Pro: Does this answer your question? – mm8 Aug 20 '20 at 15:27 — mm8, Aug 20 '20 at 15:27

score 0 · Answer 3 · answered Aug 14 '20 at 16:51

I believe you are using EntityFrameworkCore. In EntityFramework (EF6), lazy loading is enabled by default, However, in EntityFrameworkCore, lazy loading related entities is handled by a separate package Microsoft.EntityFrameworkCore.Proxies.

To enable the behaviour you are seeking, install the above package and add the following code

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseLazyLoadingProxies();
}

After this, the related entities will be loaded without the Include call.

Why do I need to .Include() collections

3 Answers3