I've Field
entity and I'm looking for a way to load all tree of nested Fields without multiple DB's queries. I heard about solution with a closure table like on this question Most efficient method of self referencing tree using Entity Framework
But I'm not sure that it's the most simplest way (actually I don't understand how to populate the closure table and map result after query)
Rather than I would like to load all Fields by CommonId but I don't load data from cache by means of navigation properties.
public class Field
{
public int Id { get; s; };
public CommonId{ get; set;} //each field from FieldDetails has same value
[ForeignKey("ParentField")]
public int? ParentFieldId { get; set; }
public virtual Field ParentField {get;set;}
public virtual ICollection<Field> FieldDetails { get; set; } =
new List<Field>();
}
var allFields = Context.Fields.where(f=>f.CommonId == 1).ToList(); //load all fields in memory cache
Field root = Context.Fields.Find(1);
foreach (var f in root.FieldDetails)
{
foreach (var f2 in f.FieldDetails) // should be loaded from cache instead of DataBase Query
{
...
}
}