it became apparent that a basic performance limitation of EF requires additional investigation. The time it takes EF to materialize data is a function of the amount of associations (foreign keys) the entire model has, even when such associations are
not part of the query in question.
As an example, using the AdventureWorks database generate an EDMX model, then have the following query:
List<SalesOrderHeader> headers = null;
using (var ctx = new AdventureWorksEntities())
headers = ctx.SalesOrderHeader.ToList();
This will materialize 31465 SalesOrderHeader entities. But the time it takes to do so depends on the size of the model we have. The test machine used has Windows 8.1 64-bit, .NET 4.5.1, high performance power setting, 12 GB RAM, Intel Xeon W3530 CPU at 2.8
If the EDMX only has one entity type (SalesOrderHeader) materialization takes 840 milliseconds (median value, 10 runs), but when the EDMX contains a richer model, for example one with 67 entity types and 92 associations, the same test takes 7246 milliseconds
to complete (median value, 10 runs).
The additional CPU is spent on code that’s affected by the amount of foreign keys in the model. The biggest difference seems to come from EntityEntry.TakeSnapshot() as it’s massively affected. It went from 173 samples to 29547 samples due to the cost of the
call to TakeSnapshotOfForeignKeys(), according to Visual Studio 2013 sampling profiles.
The second biggest difference comes from ObjectStateManager.FixupReferencesByForeignKeys() which went from 5 samples to 20172 samples.
This requires additional investigation.