.NET Core - Entity Framework - Unexpected Behaviour with Eager Loading Include()

Question

Initial Query

If I run the following query I get the result below, CityTranslation.IdFkLanguageNavigation will be null. CityTranslation.IdFkCityNavigation on the other hand will be populated. Considering they are both on the same hierarchical level.

    var data = await _context.City
                        .Include(x => x.CityTranslation)
                        .Include(c => c.IdFkCountryNavigation)
                        .ToListAsync();

I've tried using ThenInclude() after .Include(x => x.CityTranslation) but it wouldn't let me use the Language Property.

---

Altered Query

With the query below, I do get some really unexpected results. Once I debug through data2, suddently the Language navigation property from my first query result gets populated!

This only works if I call ToList() on the second query, otherwise it still doesn't get populated. I have to assume the data is shared across the whole EF context but how can I leverage this behaviour without sending a second query to the database? I am really stunned by this behaviour and would appreciate any reference or explanation.

                var data = await _context.City
                    .Include(x => x.CityTranslation)
                    .Include(c => c.IdFkCountryNavigation).ToListAsync();

                var data2 = _context.CityTranslation.Include(c => c.IdFkLanguageNavigation).ToListAsync();

---

Context

I am using Microsoft.EntityFrameworkCore.SqlServer version 1.1.2

---

City

public partial class City
{
    public City()
    {
        CityTranslation = new HashSet<CityTranslation>();
    }

    public int IdPkCity { get; set; }
    public string Id { get; set; }
    public string Code { get; set; }
    public string Latitude { get; set; }
    public string Longitude { get; set; }
    public string TimeZone { get; set; }
    public string Uri { get; set; }
    public string Name { get; set; }
    public string Language { get; set; }
    public int? IdFkCountry { get; set; }

    public virtual ICollection<CityTranslation> CityTranslation { get; set; }
    public virtual Country IdFkCountryNavigation { get; set; }

---

City Translations

public partial class CityTranslation
{
    public int IdPkCityTranslation { get; set; }
    public string Translation { get; set; }
    public int IdFkCity { get; set; }
    public int IdFkLanguage { get; set; }

    public virtual City IdFkCityNavigation { get; set; }
    public virtual Language IdFkLanguageNavigation { get; set; }
}

---

Language

public partial class Language
{
    public Language()
    {
        AirportTranslation = new HashSet<AirportTranslation>();
        CityTranslation = new HashSet<CityTranslation>();
        CountryTranslation = new HashSet<CountryTranslation>();
    }

    public int IdPkLanguage { get; set; }
    public string Name { get; set; }
    public string Code { get; set; }

    public virtual ICollection<AirportTranslation> ATranslation{ get; set; }
    public virtual ICollection<CityTranslation> CTranslation { get; set; }
    public virtual ICollection<CountryTranslation> C2Translation { get; set; }
}

Answer 1

This will work, just paste this.

// Hits the database only once
var data = await _context.City
    .Include(x => x.CityTranslation)
        .ThenInclude(x => x.IdFkLanguageNavigation)
    .ToListAsync();

Now, as to why your second query loads the Language navigation property of your first query, you have to look into how EntityFramework works as a whole, read up on explicit loading.

Here's an example of explicit loading.

// Hits the database once.
var data = await _context.City
    .Include(x => x.CityTranslation)
    .ToListAsync();

var cityTranslationIds = data.Select(x => x.CityTranslation.IdPkCityTranslation);

// Hits the database the second time.
// Language navigation property will be loaded onto the data variable above
_context.Language
    .Where(x => cityTranslateIds.Contains(x.IdPkLanguage))
    .Load();

// Your second query, what you did here is essentially the same as the above's Load(), 
// but the Load() is better suited for your intention.
var data2 = await _context.CityTranslation
    .Include(c => c.IdFkLanguageNavigation)
    .ToListAsync();

Depending on the situation, you need to choose between eager and explicit loading for best performance.