Code First load nested without recursion

Question

I have a Sheet that can contain many Sections. Each Section can also contain many Sections. I would like to load the Sheet, its Sections, and all of the sub Sections with as few round trips to the database as possible. Practically, I think this will usually be 1-2 levels deep, but it may go up to 16. Here is the code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;

public class Sheet {
    public long Id { get; set; }
    // more fields
    public virtual IList<Section> Sections { get; set; }
}

public class Section {
    public long Id { get; set; }

    public long SheetId { get; set; }
    [ForeignKey("SheetId")]
    public virtual Sheet Sheet { get; set; }

    public long? ParentId { get; set; }
    [ForeignKey("ParentId")]
    public virtual Section Parent { get; set; }

    public virtual IList<Section> Sections { get; set; }
    // more fields
}

public class MyDbContext : DbContext {
    public DbSet<Sheet> Sheets { get; set; }
    public DbSet<Section> Sections { get; set; }
    public Sheet GetSheetConfiguration(long id) {
        Configuration.LazyLoadingEnabled = false;
        Sheet rtn;
        rtn = Sheets.Find(id);
        (Sections.Where(sect => sect.SheetId == id)).ToList();
        return rtn;
    }
}

This creates the desired table structure: Sheets: Id (pk), ... Sections: Id (pk), SheetId (not null), ParentId (null)

The GetSheetConfiguration method loads all of the Sections related to that sheet and lets EF sort it out. It gets the relationships correct except that all of the Sections are also in Sheet.Sections. (I wanted to have SheetId set for every Section to avoid recursive queries.) How do I tell EF to only use Sections where ParentId = null at the Sheet level? - List item

Is the code you posted for GetSheetConfiguration correct? I don't understand the line (Sections.Where ... ) — Floremin
– Floremin, Commented May 21, 2013 at 17:41
The purpose was to load all of the Sections for that Sheet and then let EF sort it out. As @Slauma suggested, it would be more clear to say Sections.Where(sect => sect.SheetId == id).Load(); — Kevin Swarts
– Kevin Swarts, Commented May 21, 2013 at 19:12

Slauma · Accepted Answer · 2013-05-21 17:52:23Z

You can't avoid that the Sheet.Sections collection gets filled with all sections because that is what your relationship between Sheet.Sections and Section.Sheet describes: It is supposed to have all sections, not only the "root sections" with ParentId == null. Entity Framework populates this collection based on relationship fixup and you can't disable or configure this behaviour.

An option to solve the problem is to introduce an additional collection property that reads filtered data out of the Sheet.Sections navigation property and that is not mapped to the database in order to retrieve the list of root sections, like so:

public class Sheet {
    public long Id { get; set; }
    // more fields
    public virtual IList<Section> Sections { get; set; }

    public IEnumerable<Section> RootSections
    {
        get { return Sections.Where(sect => sect.ParentId == null); }
    }
}

Little side note: Instead of ...

(Sections.Where(sect => sect.SheetId == id)).ToList();

... you can use:

Sections.Where(sect => sect.SheetId == id).Load();

Load is a void method that just loads the requested entities into the context. It saves the overhead of creating the unnecessary List collection.

Collectives™ on Stack Overflow

Code First load nested without recursion

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related