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. I also found a neat trick for backward-compatible View releases:
IF NOT EXISTS (SELECT Table_Name FROM INFORMATION_SCHEMA.VIEWS WHERE Table_Name = 'MyView')
EXEC sp_executesql N'create view [dbo].[MyView] as select test = 1'
GO
ALTER VIEW [dbo].[MyView] AS
SELECT ...
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).
Next I had 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 then ran my benchmarks again and found that pages that had required over a hundred queries to generate now used only ten to twenty, and were rendering in half to a third the time – a one to two hundred percent improvement, using views designed purely to mimic the existing functionality – I hadn’t even gone about optimizing them for performance yet!
| EF + LINQ | EF + Views | |
| 3 lists of 5 entities (3 types) | 360/785/1675 | 60/105/675 |
| 2 lists of 6 entities (1 type) | 325/790/1935 | 90/140/740 |
| 1 entity’s details + 1 list of 50 entities | 465/975/2685 | 90/140/650 |
Adrian Price 