c# - Rebuild Parent->Child->GrandChild hierarchy with LINQ performance -

the problem

i have hierarchy pulling database , trying restore using linq. when run linq query against collections, not appear hitting grand-child objects.

object setup

the hierarchy of objects follows

one project -> many sections 1 section -> many biditems 1 biditem -> many subitems 

they related through foreign keys in database , mapped model objects. following simplified versions of models.

models

public class section {     public int sectionid { get; set; }      public int projectid { get; set; } }  public class biditem {     public int biditemid { get; set; }      public int sectionid { get; set; } }  public class subitem {     public int subitemid { get; set; }      public int biditemid { get; set; } } 

view models

public class sectionviewmodel : basechangenotify {     private readonly section section;     private readonly list<biditemviewmodel> biditems;      public sectionviewmodel(project project, section section)     {         var repository = new projectrepository();          this.section = section;          this.biditems = new list<biditemviewmodel>(             (from item in repository.getbiditemsbysectionid(section.sectionid)              select new biditemviewmodel(project, item)).tolist());     }      public sectionviewmodel(project project, section section, list<biditemviewmodel> biditemsforsection)     {         this.section = section;         this.biditems = biditemsforsection;     } }  public class biditemviewmodel : basechangenotify {     private biditem biditem;      private list<subitem> subitems;      public biditemviewmodel(project project, biditem biditem, list<subitem> subitems = null)     {         var repository = new projectrepository();          this.biditem = biditem;          if (subitems == null)         {             subitems = repository.getsubitemsbybiditemid(biditem.biditemid);         }          this.subitems = subitems;     } } 

you can see in 1 constructor in each view model, hitting repository fetch children objects. wanted re-write because it's not performing well. there dozen sections, each 100+ biditems. each biditem can have 100+ subitems. project having 5 sections, hit database 50,000 times during apps start (takes 2.9 seconds).

troublesome source code

i have refactored make 3 calls, 1 fetch sections project, 1 biditems in project , 1 subitems in project. need reconstruct hierarchy.

i tried using lambda initially:

list<section> projectsections =     repository.getsectionsbyprojectid(projectid).where(section => section.sectionid != 0).tolist(); list<biditem> biditemcollection = repository.getbiditemsbyprojectid(projectid); list<subitem> subitemcollection = repository.getsubitemsbyprojectid(projectid);  // after database calls can test actual reconstruction performance. timer.start();  foreach (var sectionviewmodel in projectsections.select(section => new sectionviewmodel(project, section))) {     parallel.foreach(biditemcollection         .where(biditem => biditem.sectionid == sectionviewmodel.sectionid), biditem =>          {             var biditemviewmodel = new biditemviewmodel(project, biditem,                    subitemcollection.where(subitem => subitem.biditemid == biditem.biditemid).tolist());              sectionviewmodel.biditems.add(biditemviewmodel);         });      sectionviewmodels.add(sectionviewmodel); } timer.stop(); 

and worked alright, slow. original approach take 2.9 seconds during start return sections, biditems , subitems. lambda took 2.3 seconds. tried linq query.

list<section> projectsections =     repository.getsectionsbyprojectid(projectid).tolist(); list<biditem> biditemcollection = repository.getbiditemsbyprojectid(projectid); list<subitem> subitemcollection = repository.getsubitemsbyprojectid(projectid); timer.start();  sectionviewmodels = new list<sectionviewmodel>(     section in projectsections     select new sectionviewmodel(         project,         section,         biditemcollection.where(c => c.sectionid == section.sectionid)             .select(                 biditem =>                     new biditemviewmodel(project, biditem,                         new list<subitem>(                             subitemcollection.where(subitem => subitem.biditemid == biditem.biditemid))))             .tolist())); timer.stop(); 

this returned fastest, @ 0.3 seconds, each of biditems contained empty subitem collection. reason, subitems not populating biditem view model constructor should. set breakpoint within subitemcollection.where() lambda , never gets hit.

i appreciate guidance on doing wrong linq. i'm bit new linq know i'm doing wrong simple fix.

edit:

so appears issue linq query unit test using wrong stored procedure (as lambda) fetch subitems resulting inn 0 returns. have fixed , matching numbers on 3 variations.

the interesting thing results now. first approach, hitting database 500 times takes 1.89 seconds. lambda takes 2.3 seconds reconstruct 3 database queries. linq takes 0.70 seconds. database query (with dapper) takes 0.11 seconds both lambda , linq unit tests. have 2 questions now.

1. why lambda slower?
2. can improve linq query run faster 0.6 seconds it's taking set 400 small (4-9 basic value type properties) objects?

thanks in advance!

johnathon.

why lambda slower?

projectsections.select(section => new sectionviewmodel(project, section))

from section in projectsections select new sectionviewmodel(     project,     section,     biditemcollection.where(...).tolist() 

these 2 call different constructor, hence difference in execution time.

as long logic , method same, both way of writing should give same result , @ same time. because, compiler generate same il.

how can optimize it?

since cannot benchmark on machine, i'll use general assumption.

• usually performing pull required data better pulling multiple times. avoid calling sectionviewmodel.ctor(project,section) , biditemviewmodel.ctor(project,biditem) perform more queries in database.

with said, write lambda following : //actually 3rd piece of code cleaned up

sectionviewmodels = new list<sectionviewmodel>(         projectsections.select(             s => new sectionviewmodel(project, s, biditemcollection.where(b => b.sectionid == s.sectionid).select(                     b => new biditemviewmodel(project, b, subitemcollection.where(si => si.biditemid == b.biditemid)))))); 

also, prettiness, i've changed following contructors avoid having tolist in middle of lambda :

public class sectionviewmodel {     private readonly section section;     private readonly list<biditemviewmodel> biditems;      public sectionviewmodel(project project, section section, ienumerable<biditemviewmodel> biditemsforsection)     {         this.section = section;         this.biditems = biditemsforsection.tolist();     } }  public class biditemviewmodel {     private biditem biditem;     private list<subitem> subitems;      public biditemviewmodel(project project, biditem biditem, ienumerable<subitem> subitems)     {         this.biditem = biditem;         this.subitems = subitems.tolist();     } }