So I have the following model classes on my DbContext:
Everytime I render a list of LoanApplication objects I do something like this:
var context = new MyContext();
var applications = context.LoanApplications.Where(d => d.PropertyThatIWantToFilter = localVariable);
This returns an IQueryable that then I convert to a ViewModel like this on my controller method call:
var vm = applications.Select(d => new LoanApplicationViewModel(d));
Inside the LoanApplicationViewModel
constructor I accept the entity object and do the corresponding mapping. The thing is that, since the Solicitors collection is a navigational property, a call is made to the database each time a new view model is instanced. The average number of solicitors per application is two, so that means that if I render a table listing the last 10 applications then the app is making about ~18-20 trips to the database.
I thought there had to be a better way to get this collection, so I changed my original query to eagerly load the collection like so:
var applications = context.LoanApplications.Include("Solicitors").Where...
Although this reduced the number of calls to the database to only one, the query was much slower, about 50% more slow.
The database is hosted on SQL Azure, and we've implemented Transient Fault Handling, but I want to reduce the quantity of calls made to the database without reducing response-time performance.
What is the best practice here?
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…