c# - ENtity framework 6 code first: what is the best implementation for a baseobject with 10 childobjects -

we have baseobject 10 childobjects , ef6 code first.

of 10 childobjects, 5 have few (extra) properties, , 5 have multiple properties (5 20). implemented table-per-type, have 1 table base , 1 per child (total 10).

this, however, creates huge select queries select case , unions on place, takes ef 6 seconds generate (the first time).

i read issue, , same issue holds in table-per-concrete type scenario.

so left table-per-hierachy, creates table large number of properties, doesn't sound great either.

is there solution this?

i thought maybe skip inheritance , create union view when want items child objects/records.

any other thoughts?

thanks in advance.

another solution implement kind of cqrs pattern have separate databases writing (command) , reading (query). de-normalize data in read database fast.

assuming need @ least 1 normalized model referential integrity, think decision comes down table per hierarchy , table per type. tph reported alex james ef team , more on microsoft's data development site have better performance.

advantages of tpt , why they're not important performance:

greater flexibility, means ability add types without affecting existing table. not of concern because ef migrations make trivial generate required sql update existing databases without affecting data.

database validation on account of having fewer nullable fields. not massive concern because ef validates data according application model. if data being added other means not difficult run background script validate data. also, tpt , tpc worse validation when comes primary keys because 2 sub-class tables potentially contain same primary key. left problem of validation other means.

storage space reduced on account of not needing store null fields. trivial concern, if dbms has strategy handling 'sparse' columns.

design , gut-feel. having 1 large table feel bit wrong, because db designers have spent many hours normalizing data , drawing erds. having 1 large table seems go against basic principles of database design. biggest barrier tph. see article particularly impassioned argument.

that article summarizes core argument against tph as:

it's not normalized in trivial sense, makes impossible enforce integrity on data, , what's "awesome:" virtually guaranteed perform badly @ large scale non-trivial set of data.

these wrong. performance , integrity mentioned above, , tph not mean denormalized. there many (nullable) foreign key columns self-referential. can go on designing , normalizing data tph. in current database have many relationships between sub-types , have created erd if tpt inheritance structure. reflects implementation in code-first entity framework. example here expenditure class, inherits relationship inherits content:

public class expenditure : relationship {     /// <summary>     /// inherits content: id, handle, description, parent (is context of expenditure ,      /// project)     /// inherits relationship: source (the principal), sourceid, target (the supplier), targetid,      ///      /// </summary>     [required, inverseproperty("expenditures"), foreignkey("productid")]     public product product { get; set; }     public guid productid { get; set; }      public string unit { get; set; }     public double qty { get; set; }     public string currency { get; set; }     public double totalcost { get; set; }          } 

the inversepropertyattribute , foreignkeyattribute provide ef information required make required self joins in single database.

the product type maps same table (also inheriting content). each product has own row in table , rows contain expenditures include data in productid column, null rows containing other types. data is normalized, placed in single table.

the beauty of using ef code first design database in same way , implement in (almost) same way regardless of using tph or tpt. change implementation tph tpt need add annotation each sub-class, mapping them new tables. so, news doesn't matter 1 choose. build it, generate stack of test data, test it, change strategy, test again. reckon you'll find tph winner.