The Problem
I was profiling a Web application built on Entity Framework 6 and MVC 5, using the excellent Glimpse. I found that a page with three lists of five entities each was causing over a hundred query executions, eventually loading a huge object graph with hundreds of entities. I could eliminate the round trips using Include(), but that still left me loading way too much data when all I needed was aggregate/summary data.
The problem was that the aggregates I needed were complex and involved calculated properties, some of which were based on aggregates of navigation collection properties: a parent had sums of its children's properties, which in turn had sums of their children's properties, and in some cases parents had properties that were calculated partly based on aggregates of children's properties. You can see how this quickly spun out of control.
My requirements were that the solution had to perform better, at returning the same data, while allowing me to use standard entity framework, code first, with migrations. My solution was to calculate this data on the server side, using entities backed by views that did the joining, grouping, and aggregation.
The Solution
First I set about creating the views themselves, using DB-side logic to perform the aggregation and math I needed to get the statistics I needed for each entity. I basically went through the existing calculated properties and replicated their behavior in SQL, returning the results as columns in the view. I created one view for each type that I would be optimizing this way, plus one additional view that held aggregations shared between the other views (in order to keep the views DRY).
I then created the entities that would represent the views, using unit tests to ensure that the properties now calculated on the server matched expected values the same way that the original, app-calculated properties did. Creating entities backed by views is fairly straightforward; they behave just like tables, but obviously can't be modified - I made the property setters protected to enforce this at compile time. Because my View includes an entry for every "real" entity, any query against the entity type can be cast to the View-backed type and it will pull full statistics (there is no possibility of an entity existing in the base table but not in the view).
A Small Misstep
At first I tried to create a one to one association between the now bare entity type and the view type holding the aggregate statistics. The only ID I had for the view was the ID of the raw entity it was connected to. This turned out to be easier said than done - entity framework expects that, in a one to one relationship, it will be managing the ID at one end of the relationship; in my case, the ID's at both ends were DB-generated, even though they were guaranteed to match (since the ID in the view was pulled directly from the ID in the entity table).
I ended up abandoning the one-to-one mapping idea after a couple days' struggle, instead opting to map the statistics objects as subclasses of the real types in a table per type structure. This turned out to be relatively easy to accomplish - I added a table attribute to the sub type, giving the name of the view, and it was off to the races. I went through updating references to the calculated fields throughout LINQ queries, views, and unit tests. The unit and integration tests proved very helpful in validating the output of the views and offering confidence in the changes.
The Results
Join the conversation! Your thoughts help the community grow.