EF6表拆分与多个分割的共享主键 [英] EF6 Table splitting vs shared primary key for multiple splits

问题描述

我将旧版数据库系统升级到.NET + Entity Framework 6（代码优先POCO）+ PostgreSQL。为了方便编程，我希望将一张大表（200+字段）分割成多个实体，例如：

 特许经营
特许经营实体
特许经营账单
特许经营资格
特许经营分配
特许经营权
特许经营杂项
特许经营通知
  

我很高兴找到EF6支持表拆分：将单个表映射到多个实体来分割字段。

然而，尝试实现这一点，并在线阅读许多网页时，证实这在分割多次时是有问题的。实体框架不仅要导航属性到主体实体，而且要映射到表之间的所有实体。对于上面的我的情况，这将需要21个无意义的导航属性 - 42如果我打扰了他们双向。

请参阅： http://social.msdn.microsoft.com/Forums/en-US/0f65caae-8a66-431f-aa02-4b2c68f871e9/ef-41-rc-code-first-split-one-table -into-multiple-entities？论坛= adodotnetentityframework

请参阅：

建议使用具有共享主键的多个表，一个是我的选项。然而，鉴于EF的猖獗的SQL查询生成，PostgreSQL有时候具有复杂查询的随意的查询优化器，我担心此选项（100GB +数据库）的性能。

总结：

表拆分

查询性能，最快实现在数据库层

Cons ：污染我的模型和OnModelBuilding（）方法与垃圾，混淆其他开发人员

跨多个表共享的主键

优点：最干净模型&代码，非传统数据库的推荐解决方案

缺点：额外的工作要实现，潜在较差的性能

我的问题：

1）EF6是否改进了使用2+拆分的表拆分？

2）有没有任何因素我没有考虑过？

3）还有其他选择吗？

PS我不想使用[ComplexType]

解决方案

我已经解决的解决方案是表拆分选项。

这涉及到我的模型添加了大量无意义的导航属性，并依靠流畅的API。我认为这是两个罪恶中较小的一个，因为它们造成最小的开销，并避免了潜在的性能问题。

我已经更详细地概述了解决方案（包括代码）在这个主题中：

EF6未能构建表拆分/共享主键+基类的模型

I am upgrading a legacy database system into .NET + Entity Framework 6 (code-first POCO) + PostgreSQL. For ease of programming, I wish to split a large table (200+ fields) into multiple entities, eg.:

Franchise
FranchiseLegalEntity
FranchiseBilling
FranchiseSignup
FranchiseAllocation
FranchiseCompliance
FranchiseMiscellaneous
FranchiseNotifications

I was delighted to find EF6 supports "table splitting": mapping a single table to multiple entities to split the fields up.

However attempting to implement this, and reading many pages online, confirmed that this is problematic when splitting multiple times. Entity Framework requires navigation properties not just to the principal entity, but between ALL entities mapped to the table. For my scenario above, this would require 21 pointless navigation properties - 42 if I made bothered making them two-way.

See: http://social.msdn.microsoft.com/Forums/en-US/0f65caae-8a66-431f-aa02-4b2c68f871e9/ef-41-rc-code-first-split-one-table-into-multiple-entities?forum=adodotnetentityframework

See: How to separate large table into multiple discrete types using EF-Code-First

Using multiple tables with a shared primary key is recommended, an is an option for me. However, given EF's bloated SQL query generation, and PostgreSQL's sometimes haphazard query optimizer with complex queries, I have concerns about the performance of this option (100GB+ database).

To summarize:

Table splitting

Pros: Best query performance, quickest to implement at database layer

Cons: Polluting my models and OnModelBuilding() method with crap, confusing other developers

Shared primary keys across multiple tables

Pros: Cleanest models & code, recommended solution for non-legacy databases

Cons: Extra work to implement, potentially poorer performance

My questions:

1) Has EF6 improved the table splitting with 2+ splits?

2) Are there any factors I haven't considered?

3) Are there any other options?

P.S. I am not interested in using [ComplexType]

解决方案

The solution I have settled on is the Table Splitting option.

This involves adding a lot of pointless navigation properties to my models and relying on fluent API. I think it's the lesser of the two evils since they cause minimal overhead, and avoid the potential performance problems.

I've outlined the solution in more detail (including code) in this thread:

EF6 failing to build model for Table Split/Shared Primary Key + Base class?

这篇关于EF6表拆分与多个分割的共享主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！