written by:
Creating the database from the model using code
The first problem is creating the database from the model in the ORM, from code. By the term "from code" I mean creating the model when the application is running instead of using design time tools in Visual Studio.
LINQ to SQL supports this scenario by providing you with a CreateDatabase() and DeleteDatabase() method, that will essentially create the database and destroy it. It works as expected, but is not enough. There are some limitations as well, but I could live with them. For example, the LINQ model does not describe every piece of metadata that the original table would have (from which the model was generated). So when the model is used to generate the database, some info will be missing, like users, usage rights, stored procedures, etc. As said, this would not be a problem if all I need is the tables themselves.
But there is no fine grained control over the creation process! The CreateDatabase() method will either
a) create the entire database from scratch
b) or FAIL
Now what I would want to do is be able to create my tables independent of the database. If the database exists, just add my tables into it. LINQ to SQL does not seem to be able to support this scenario. There is an awful lot of support built into the LINQ to SQL classes for generating all the schema etc. (Reflector shows them … they are of course internal to LINQ to SQL). But you cannot create just the tables. Or just the missing tables.
Now why would I want something like that? Let me put up a few scenarios:
a) In a shared hosting environment you usually have 1 database. So the database is shared between different applications. If I have two applications that I would want to be able to dynamically create the tables, the second one to be run is just out of luck if wants to use this method.
b) If I have an application that is partitioned into components and components can be plugged in dynamically, then I would like to have all components having their own LINQ mappings. Thus the situation is similar to the one described above: two or more sets of tables need to be created. I would even support the opposite, when a component is uninstalled, its tables get removed.
The Entity Framework has a similar CreateDatabase() method, with the same faulty assumptions the LINQ version makes: if the database already exists, it will just throw an exception. There is an interesting method in EF though, CreateDatabaseScript(). This will generate the script that - according to MSDN - the CreateDatabase() method will execute. HOWEVER, the script this method returns omits the database creation statements. So this could be used to create the tables.
Otherwise for now the best solution seems to be to generate the database script files by hand, include them in the project and run them using SqlConnection/SqlCommand. This way I can be sure of what happens, and no existing tables are damaged.
Table names (prefixes, postfixes, entire names)
Sometimes the names of the tables will change during runtime. It can be a complete change or it can be something like a prefix that is applied to the table name. There can be a million reasons for this, here are two of those:
a) In a shared hosting environment you will usually get only one database. If you wish to use different applications you could run into naming collisions without prefixes.
b) You are writing a component that will be used by other programs. To play nicely along, your component should not “lock” into using a given table name. Prefixes or changing names should be supported.
It is dreadful how difficult it is to change table names at runtime for both LINQ to SQL and the Entity Framework. I found posts that describe ways to do it, but the entire process is complicated and difficult to automate. Especially the EF way seems very tricky.
Nevertheless, solutions do exist.
The following article describes a way for both technologies. For LINQ to SQL, you need to generate a mapping file and use it at runtime. What this means, that you cannot automate the process, and that you need to generate and modify the file again, should your database or mapping change.
For the EF scenario it also requires file changes, which is not too good. Anyway, here it is:
http://theruntime.com/blogs/jacob/archive/2008/08/27/changing-table-names-in-an-orm.aspx
For EF there is a better way, a small framework found on CodePlex. It enables you to play with metadata at runtime, like adding a table prefix. This solves the problem, but it also requires some code modifications that will disappear (and need to be redone), should you use the designer.
http://efmodeladapter.codeplex.com/
I cannot understand how these feature can be missing, especially from EF 4, which should be a mature product (I know LINQ to SQL is not being actively developed further). Even Microsoft’s own ASP.NET tables use the prefix notation to make their tables stand out. Again, in shared hosting environments it might be good to have prefixes. Versioning scenarios could involve prefixes or postfixes (== table name changes). Use cases are virtually endless, and it is a pretty straightforward thing to implement.
Summary
Creating tables from models and changing names after compile time seem to be simple problems for me - I would even call them everyday problems. And still they are not solved in Microsofts own ORM tools.
I would love to hear feedback on this post, feel free to suggest other solutions, to point out errors or to comment on these things. Also, experience with other OR mappers is welcome.
