April 6, 2012

The logic behind the .CreateSchema extension

As I stated earlier, the modelling of a class hierarchy to tables is a lot about guessing. So which guesses do the CreateSchema extension make?

Datatypes

The extension tries to follow the mapping on this page. But of course, since one C# data type maps to several data types in sql server guesses are made. Strings for example maps to nVarChar(8000) and decimal maps to Decimal and not to Money in SQL server. You will have to check the source code for a full list.

Failures

Stuff that the CreateSchema cant handle will end up in the comment field in the bottom of the generated script.

IDs 

Every table gets an primary key column named ID of either Int with Identity or GUID depending on settings, the default is Int with Identity. If you are into other name standards or want other fields acting as keys you will have to edit the SQL. Even relational tables get an ID for a primary key, not the more common way of having an aggregate key consisting of the primary keys of the related tables.

Collections

Every collection of value types or the supported reference types will be broken out into a new table with an ID and a Value field of the type of the collection. The extension currently only do this for generic lists (List<>) so if you have dictionaries or arrays for example they will end up on the failure list. 

Relations

There is no way to really know how to translate relations into tables, there is no semantic notions in c# whether a list of objects symbolize a one to many relation or a many to many relation. I am currently only making many to many relations when there are list of objects pointing from each direction to each other. Eg. object A has a list of object B and object B has a list of object A. 

Enums

Enums are currently broken out to a table much like the collections.

Class hierarchies

I support two ways to map class hierarchies to tables. I either keep class hierarchies and let tables generated by subclasses have all the fields of the superclass, duplicating the data or I split the hierarchies letting the tables generated by subclasses only contain their own properties along with a foreign key to the super class. The default strategy is to split hierarchies.

No comments:

Post a Comment