c# - Code First load nested without recursion -

i have sheet can contain many sections. each section can contain many sections. load sheet, sections, , of sub sections few round trips database possible. practically, think 1-2 levels deep, may go 16. here 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 desired table structure: sheets: id (pk), ... sections: id (pk), sheetid (not null), parentid (null)

the getsheetconfiguration method loads of sections related sheet , lets ef sort out. gets relationships correct except of sections in sheet.sections. (i wanted have sheetid set every section avoid recursive queries.) how tell ef use sections parentid = null @ sheet level? - list item

you can't avoid sheet.sections collection gets filled all sections because relationship between sheet.sections , section.sheet describes: supposed have sections, not "root sections" parentid == null. entity framework populates collection based on relationship fixup , can't disable or configure behaviour.

an option solve problem introduce additional collection property reads filtered data out of sheet.sections navigation property , not mapped database in order retrieve list of root sections, so:

public class sheet {     public long id { get; set; }     // more fields     public virtual ilist<section> sections { get; set; }      public ienumerable<section> rootsections     {         { return sections.where(sect => sect.parentid == null); }     } } 

little side note: instead of ...

(sections.where(sect => sect.sheetid == id)).tolist(); 

... can use:

sections.where(sect => sect.sheetid == id).load(); 

load void method loads requested entities context. saves overhead of creating unnecessary list collection.