EF6 Table splitting vs shared primary key for multiple splits

Question

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]

Perhaps you should consider using a view and creating an entity for the view? Pros - simple - Cons - no referencing.. Also consider the difficulty that comes with managing table splitting once the database has been created and things change further down the line. — Paul Zahra, Jan 13 '14 at 13:08

score 1 · Answer 1 · answered Jan 14 '14 at 07:55

With a lot of assumptions, you may try to BCP-out necessary subsets of fields and BCP-in them back to the desired DB tables. This is simple and repeatable process because BCP-out SQL statement is in your control so you can always export only part of the data (say, only records created after a certain date). But again, it may not work for you because of sheer amount of data.

score 1 · Accepted Answer · edited May 23 '17 at 12:29

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 Table splitting vs shared primary key for multiple splits

2 Answers2

Linked