I am using WebAPI and Entity Framework to build a REST API that points to a large MSSQL database (~200 tables). The database is very normalized, so retrieving values that are useful to the consumer of the API requires a lot of drilling down into the tables.
In order to return useful data to the consumer, I have taken the approach of building models (or DTOs) using a factory pattern. However, I noticed that even though all the data was being returned in a nice format, there were performance issues due to lazy loading being enabled. In short, I was querying too much data even while I was returning only the data I needed.
So I resorted to turning off lazy loading and have attempted to grab the 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 is an example of the factory method I'm using to generate the model, which includes nested factory methods as well to shape my 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 any patterns or practices for handling the above situation? I've seen some examples of a method that allows you to pass in an array of include statements, but I cannot think of a way to handle this in an elegant, efficient, pragmatic way. Any input would be much appreciated.