CODEWITHSUNDEEP
c#entity-framework
user2160375
user2160375

Reputation:

Navigation properties not loading in self-referencing relation

I've model Category that has a self-referencing relation:

public class Category
{
    public Category()
    {
        this.Childs = new HashSet<Category>();
    }

    public int CategoryId { get; set; }

    public int? ParentId { get; set; }
    public virtual Category Parent { get; set; }

    public HashSet<Category> Childs { get; set; }

    //... other useless properties
}

Relation configuration is defined in context:

modelBuilder.Entity<Category>()
            .HasOptional(c => c.Parent)
            .WithMany(c => c.Childs)
            .HasForeignKey(m => m.ParentId);

I've also generic Repository that includes methods:

public IEnumerable<T> GetAll()
{
    return this.dbSet.ToList();
}

public IQueryable<T> AsQuerable()
{
    return this.dbSet.AsQueryable();
}

And now very strange behaviour occurs. I call a method:

var categoriesEntites =
        this.catogries.AsQuerable()
                      .Where(cat => cat.ProjectId == projectId 
                                    && cat.ParentId == null)
                      .ToList();
// this.categories is of type `Repository<Category>`

In the result, Childs are not loaded..: Usual case

But when I call GetAll before:

this.catogries.GetAll();
var categoriesEntites =
        this.catogries.AsQuerable()
                      .Where(cat => cat.ProjectId == projectId 
                                    && cat.ParentId == null)
                      .ToList();

Suddenly navigation properties works.. With GetAll

I've used such an approach many times before, and I have never had such a problem.. Some ideas? LazyLoading is turned on of course..

To make things more strange, I've other self-referencing entity (that is build in exactly the same way) and there is no such a bug.

BTW. Sorry for typo mistake in nav property, it should be Children of course, not Childs...

Upvotes: 1

Views: 165

Answers (1)

Alaa Masoud
Alaa Masoud

Reputation: 7135

When you do ToList() in your GetAll() method, EF loads the entire table and will store those entities locally. This is why they appear loaded when you perform this query:

var categoriesEntites =
    this.catogries.AsQuerable()
                  .Where(cat => cat.ParentId == null)
                  .ToList();

If you look at the generated SQL for the above query, it actually only fetches categories where ParentId is null:

SELECT 
    [Extent1].[CategoryId] AS [CategoryId], 
    [Extent1].[ParentId] AS [ParentId]
    FROM [dbo].[Categories] AS [Extent1]
    WHERE [Extent1].[ParentId] IS NULL

The child collection isn't fetched even after inspecting the collection in VS debugger (triggers lazy loading) because its already loaded and stored locally by EF.

If you want to get child objects without using ToList() ( or GetAll() ), use the virtual keyword on your child collection. This is a requirement for lazy loading and will fetch when that child collection is accessed, by code or VS debugger.

public virtual HashSet<Category> Childs { get; set; }

Upvotes: 1

Related Questions