实体框架代码首先将TPT转换为TPH [英] Entity Framework Code First Convert TPT to TPH

问题描述

我使用TPT开发了使用EF Code First的应用程序（发布附件，评论等）。它工作正常，并且与许多客户端进行beta测试，但是有一些层次结构。所以我有一个基本模型与各种继承的模型，每个都包含一些属性本身是继承类型。

我注意到这是非常缓慢，所以我看在它生成的SQL中，并获得一个帖子列表，EF正在生成超过2000行的SQL。编译时间非常高，我不喜欢每个请求通过电线发送这么多数据的想法。启动时需要5秒才能获得3个帖子。随后的通话速度要快得多，但每次都有回收，速度再慢一些。

我尝试过Dapper和手写代码，但问题是Dapper不是适用于需要依赖类型的多层次查询;即DisplayTemplates和手写代码，而快速不适合，因为它使未来的开发变得更加复杂。

我想尝试移动一些代码（表）到TPH，考虑违反第三规范和代码可维护性之间的权衡是可以接受的，但是我无法找到有关如何转换现有数据库的任何信息。

如果我从头开始，我只是删除[Table ...]注释，但是我假设如果我用一个填充的数据库这样做，我将丢失映射表中的所有数据，它不会填充新的单个表与现有的数据。

这是正确的吗？任何人都知道如何或最好地将现有表格从TPT转换为TPH。

我只有几个客户端以此作为试用版，但他们赢得了不幸的是，如果我输了一半的数据!!

解决方案

我不知道有什么工具可以从TPT自动到TPH，但您可以自己迁移数据作为数据库迁移的一部分。

1. 删除 [表] 类中的注释


2. 使用 Add-Migration 命令生成新的迁移。生成的迁移将包含几个 AddColumn ， DropForeignKey ， DropIndex ， DropTable 调用


3. 将所有 AddColumn 调用到 Up 方法



4. 编写一个SQL命令，填充表中新创建的列，其中包含层次结构的所有数据（don'不要设置正确的鉴别器）。 [x] = [TPTTable]。[X]，
   [TPHTable]。[Discriminator] =NameOfTheClass
   FROM [TPHTable] INNER JOIN [TPTTable]
   ON [TPHTable]。[ID] = [TPTTable]。[ID]
   

   迁移允许您调用任意SQL命令，因此将此SQL添加到 Up 方法刚刚 AddColumn 方法调用。

     Sql （@UPDATE [TPHTable] ...）; 
     




5. 使用更新数据库命令




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 !!

解决方案

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

这篇关于实体框架代码首先将TPT转换为TPH的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！