Read Schema for a "table per type" database

May 22, 2013 at 3:24 AM
Edited May 22, 2013 at 8:54 PM
Hello Martin

is it possible to read the schema for a "table per type" database in SQL server

once in the final application and using Entity Framework , i will get my data like this
I am sure you know all of that ;)

but how your awesome robot will make sense of the fields to know how to get all fields for each table

i will never get tired of thanking you for this awesome community project

May 24, 2013 at 3:18 PM
Edited May 24, 2013 at 3:27 PM
7.1 here

The queries which are generated will be more complex than those that are generated with the other inheritance strategies, which may result on longer execution times on the store. It will generally take longer to generate queries over a TPT model, and to materialize the resulting objects.

I wonder if your project can be programmed to "construct" the table and all its fields

May 27, 2013 at 2:41 PM
The schema can be read fine - the problem is for the code generation part which has to reverse engineer the code first model.

We can identify the "shared primary key" which is a key part of this inheritance - the child table PK is also a FK to the parent table. There's a IsSharedPrimaryKey extension method for it.

But we don't then write the inheritance in the class definition- we do normal associations as if it was a 1:1 relationship (which is another way of looking at it).
Say you have Employee and Manager tables. A manager is always an employee.
In code first, you could have a Manager class that inherits from Employee. That's inheritance.
Or you could have a Manager class with an required Employee property. This is 1:1, and that's what we try to do now. Yes, in code you cannot tell that a Manager cannot be created without an Employee first, so that's domain logic.

Which you want depends mostly on business logic. I haven't seen a lot of TPT in the wild, and in fact I know a case (in Code First) where we ripped it out because it was inefficient.

For the moment I think we'll keep to the 1:1 modelling, but I'll think about how we can do inheritance as well.

Thanks for the input!
Jun 8, 2013 at 4:26 PM
Edited Jun 8, 2013 at 4:31 PM
thanks Martin.

as you probably know,Ward Bell is the guy for data, he has this application
and there is Entity Framework Table-per-Type inheritance there
Jun 11, 2013 at 5:51 AM
I had another look at this, and I have an initial implementation.
Changeset is 27209

If two tables have a "shared primary key", it's a one to one.
If there are more than two tables joined to the same primary key, it's not likely to be one to one, it's TPT inheritance.