c# - Entity Framework/LINQ - Returning data transfer objects from a large nested entity data set -

i using webapi , entity framework build rest api points large mssql database (~200 tables). database normalized, retrieving values useful consumer of api requires lot of drilling down tables.

in order return useful data consumer, have taken approach of building models (or dtos) using factory pattern. however, noticed though data being returned in nice format, there performance issues due lazy loading being enabled. in short, querying data while returning data needed.

so resorted turning off lazy loading , have attempted grab data explicitly using include methods:

var accessions = db.accessionparties   .include(ap => ap.accession.accessionparties.select(ap2 => ap2.party))    .include(ap => ap.accession.accessionparties.select(ap2 => ap2.accessionpartypurposes.select  (app => app.partyaccessionpurposetype)))    .include(ap => ap.accession.accessionanimals.select(x => x.animalinformationtype))    .include(ap => ap.accession.accessionanimals.select(x => x.specimens.select(y => y.accessiontestrequestspecimens.select(z => z.accessiontestrequest.labtestoffering.testoffering))))    .tolist()   .select(a => modelfactory.createaccessionmodel(a.accession)); 

below example of factory method i'm using generate model, includes nested factory methods shape related data entities.

  public accessionmodel createaccessionmodel(accession accession)     {         return new accessionmodel()         {             accessionkey = accession.accessionkey,             submitteddate = accession.submitteddate,             parties = accession.accessionparties                                 .select(accessionparty => new { accessionparty = accessionparty, accessionparty.party })                                 .select(accessionparty => createpartymodel(accessionparty.party)),             animals = accession.accessionanimals.select(accessionanimal => createaccessionanimalmodel(accessionanimal))         };     } 

are there patterns or practices handling above situation? i've seen examples of method allows pass in array of include statements, cannot think of way handle in elegant, efficient, pragmatic way. input appreciated.

you can use automapper map between entities , dtos , projection can execute query , load columns dto needs. check http://automapper.org/ , https://github.com/automapper/automapper/wiki/queryable-extensions

hope helps.