Entity Framework is quite great when it comes to working with databases. Unfortunately it can also have some very annoying surprises at times. Just recently I was fixing a “bug” in a program with the following description: “It takes a long time to load the *** view in the application”. Well, after investigating the code I came to a conclusion – Entity Framework sucks at generating queries for inherited entities when its using TPT (Table-Per-Type) mapping. The query that it was generating took anywhere from 10 to 30 seconds to execute in SQL CE 3.5. And the query execution time was growing as the number of inherited entities grew. The query was just too much for SQL CE to handle (executing the same query on SQL Server 2005 is almost instantaneous).
The solution I ended up with (along with a nasty migration script) was to use TPH (Table-Per-Hierarchy) mapping. The difference between TPT and TPH performance was amazing. Those 10-30 seconds turned into 1 second! That is wonderful! Even better – the execution time does not seem to grow exponentially as it was with TPT!
So, here is what I did:
- Got the EF Powerpack that allowed generating TPH mappings.
- The TPH generation did not work for me straight away, so I had to edit the TPH workflow and T4 templates a little bit. This turned out to be a bit trickier than it sounds since every time you “Generate database from model” the TPH workflow restores its contents to its original text (== changes you make are overwritten). So…
- I copied the TPH generation workflow and its T4 templates into the project and in make the workflow file “read-only” (just go to file properties in explorer and tick that checkbox).
- For those using some VCS – make sure that commit/update does not take that read-only flag off. In case of SVN – lock the file, otherwise the read-only flag will be gone.
- Made the necessary changes to those T4 templates and confirmed that the workflow is not overwriting itself anymore!