Entity Framework Code First Convert TPT to TPH

Question

I have developed an app (posts attachments, comments etc) using EF Code First using TPT. It works fine and is in beta testing with a number of clients., however there are a number of hierarchies. So I have a base model with various inherited models, each of which contain a number of properties which are themselves inherited types.

I noticed it was very slow and so I looked in to the SQL it was generating and to get a list of posts, EF is generating over 2000 lines of SQL. The compilation time is very high and I don't like the idea of sending that much data over the wire for every request. It takes 5 seconds to get 3 posts on startup. Subsequent calls are much faster, but every time there is a recycle, the speed is slow again.

I have tried Dapper and handwritten code, but the issue is Dapper is not suited to multilevel queries where reliance on type is required; ie DisplayTemplates and hand written code, while fast is not appropriate as it makes future development much more complicated.

I want to try as an experiment moving some of the code (tables) to TPH and consider the trade off between violating the third norm and code maintainability to be acceptable, but I cant find any information on how to convert an existing db.

If I were starting from scratch, I would just remove the [Table...] annotation, however I am assuming that if I do this with a populated db, I will lose all the data in the mapped tables and it wont populate the new single table with the existing data.

Is this correct? Is anyone aware of how to or best practice on converting existing tables from TPT to TPH.

I only have a few clients with this running as a trial, but they won't be pleased if I lose half of their data !!

Answer 1

I am not aware of any tool that will do the migration from TPT to TPH automatically, but you can migrate data yourself as a part of the DB migration.

1. Remove [Table] annotation from classes
2. Generate a new migration with Add-Migration command. The generated migration will contain couple AddColumn, DropForeignKey, DropIndex, DropTable calls
3. Put all AddColumn calls to the top of the Up method

4. Write a SQL command that populates newly created columns in the table that contains all data for the hierarchy (don't forget to set the correct Discriminator).

   UPDATE [TPHTable]
   SET [TPHTable].[X] = [TPTTable].[X],
       [TPHTable].[Discriminator] = "NameOfTheClass"
   FROM [TPHTable] INNER JOIN [TPTTable]
   ON [TPHTable].[ID] =  [TPTTable].[ID]
   

   Migration allows you to call arbitrary SQL command, so add this SQL to the Up method just after AddColumn method calls.

   Sql(@"UPDATE [TPHTable] ...");
   

5. Update your database with the Update-Database command