EF6 not loading Navigation Properties despite using include

Question

I have the following entities:

Person 1 --- * PersonAdresse * --- 1 Adresse

When trying to load Persons with PersonAdresses by calling PersonRepository.GetPersonen(...) I only get Personen (with filled out Id, Name, Vorname) but empty PersonAdresse...

I've made sure, that there is an entry in the database for the person, for the personadresse and the adresse.

EF generates following SQL (a simple SQL with no join or anything):

exec sp_executesql N'SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent1].[Vorname] AS [Vorname]
FROM [dbo].[Person] AS [Extent1]
WHERE (([Extent1].[Name] = @p__linq__0) OR (([Extent1].[Name] IS NULL) AND (@p__linq__0 IS NULL))) AND (([Extent1].[Vorname] = @p__linq__1) OR (([Extent1].[Vorname] IS NULL) AND (@p__linq__1 IS NULL)))',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',@p__linq__0=N'Mustermann',@p__linq__1=N'Max'

Entities:

public partial class Person
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Person()
    {
        this.PersonAdresse = new HashSet<PersonAdresse>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public string Vorname { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<PersonAdresse> PersonAdresse { get; set; }
}

public partial class PersonAdresse
{
    public int Id { get; set; }
    public int PersonId { get; set; }
    public int AdresseId { get; set; }

    public virtual Adresse Adresse { get; set; }
    public virtual Person Person { get; set; }
}

public partial class Adresse
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Adresse()
    {
        this.PersonAdresse = new HashSet<PersonAdresse>();
    }

    public int Id { get; set; }
    public string Strasse { get; set; }
    public string Ort { get; set; }
    public int Plz { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<PersonAdresse> PersonAdresse { get; set; }
}

PersonRepository:

public ICollection<Person> GetPersonen(PersonQueryCriteria personQueryCriteria)
{
    var query = this.Context.GetQuery<Person>()
                    .Include(q => q.PersonAdresse)
                    .Include($"{nameof(Person.PersonAdresse)}.{nameof(PersonAdresse.Adresse)}");

    if (!string.IsNullOrEmpty(personQueryCriteria.NameFilter)) {
        query = query.Where(p => p.Name == personQueryCriteria.NameFilter);
    }

    if (!string.IsNullOrEmpty(personQueryCriteria.VornameFilter))
    {
        query = query.Where(p => p.Vorname == personQueryCriteria.VornameFilter);
    }

    return query.ToList();
}

Why do the PersonAdresse and Adresse not get loaded despite using the .Include(...)?

Thanks in advance

Edit1: GetQuery-Method on the EF-Context-Class:

public IQueryable<T> GetQuery<T>() where T : class
{
    return (IQueryable<T>)this.Set(typeof(T)).AsQueryable();
}

I've added the code for the GetQuery<> method to the original question :) — xeraphim, Sep 20 '17 at 08:56
Well that's a rather complex way of just doing `return this.Set();` :) — DavidG, Sep 20 '17 at 08:57
It seems you have to do `.Include(q => q.PersonAdresse.Select(y => y.Adresse))`. Check out https://stackoverflow.com/questions/10822656/entity-framework-include-multiple-levels-of-properties — Dan Dumitru, Sep 20 '17 at 08:59
@DanDumitru I've tried including it with Select, but unfortunately it still doesnt get loaded :( — xeraphim, Sep 20 '17 at 09:04

score 1 · Accepted Answer · answered Sep 20 '17 at 16:28

This is because you probably didn't define the FK relation between Person and PersonAddresse. By default, it will join on Id => Id, I believe, so you have to tell EF that Person.Id -> PersonAddresse.PersonId.

Otherwise, it could be because you are doing AsQueryable(). DbSet is already queryable with additional logic, so change it to do this:

 this.Context.Set<Person>()...  instead of that GetQuery call.

score 0 · Answer 2 · answered Sep 20 '17 at 09:01

I will Suggest you to use Strong Types for Entity Framework Entities to Avoid Confusion for EF when Loading. I am not sure why but have experienced issues while using Interfaces instead of Collection Types like List.

If I am wrong, or Missing Something give me some Does and don'ts while using Collections Interfaces in EF Entity, for my self Improvement.

To answer your Question, you can use following Code line to Globally Disable Lazy Loading.

context.Configuration.LazyLoadingEnabled = false;

lazy loading is disabled already, unfortunately, it still doesn't get loaded — xeraphim, Sep 20 '17 at 09:03

EF6 not loading Navigation Properties despite using include

2 Answers2