c# - Select entities with multiple and nested levels without using Include -

i have following entity:

public class item  {     public int id { get; set; }      public int? parentid { get; set; }     public item parent { get; set; }     public list<item> children { get; set; }      public double propertya { get; set; }     public double propertyb { get; set; }     ... } 

now want query database , retrieve data of nested children. achieve using eager loading include():

var allitems = dbcontext.items                     .include(x => children)                     .tolist(); 

but instead of eager loading, want following projection:

public class projection  {     public int id { get; set; }     public list<projection> children { get; set; }     public double propertya { get; set; } } 

is possible retrieve desired data single select? using entity framework 6.1.3.

edit: have tried far. don't know how tell ef map child projection same way parents.

an unhandled exception of type 'system.notsupportedexception' occurred in entityframework.sqlserver.dll

additional information: type 'projection' appears in 2 structurally incompatible initializations within single linq entities query. type can initialized in 2 places in same query, if same properties set in both places , properties set in same order.

var allitems = dbcontext.items     .select(x => new projection     {         id = x.id,         propertya = x.propertya,         children = x.children.select(c => new projection()         {             id = c.id,             propertya = c.propertya,             children = ???         })     })     .tolist(); 

generally speaking, can't load recursive structure of unknown unlimited depth in single sql query, unless bulk-load potentially relevant data irregardless whether belong requested structure.

so if want limit loaded columns (exclude propertyb) ok load rows, result following:

var parentgroups = dbcontext.items.tolookup(x => x.parentid, x => new projection {     id = x.id,     propertya = x.propertya });  // fix children foreach (var item in parentgroups.selectmany(x => x)) {     item.children = parentgroups[item.id].tolist(); } 

if want limit number of loaded rows, have accept multiple db queries in order load child entries. loading single child collection example

entry.children = dbcontext.items     .where(x => x.parentid == entry.id)     .select(... /* projection*/)     .tolist()