ReBuildAll Blog
Thoughts (mostly) on .NET development

Missing features from LINQ to SQL and Entity Framework: create tables, table name changes/prefixes   (Framework)   
I find it interesting that some common usage scenarios are missing from both LINQ to SQL and the Entity Framework. I did some research on the internet, and I do not seem to be the only person who is having these problems. There are solutions to some, but I still feel the need to rant about these in this blog :-)

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. :-)


 

Comments

sierramike Re: Missing features from LINQ to SQL and Entity Framework: create tables, table name changes/prefixes
Hi,

Glad to read this post, I have the exact same thoughts. I didn't write lot of code those last three years, but dug into the last frameworks to keep up-to-date, and for some small projects.

My thought is that each time I try to use modern frameworks, I just pull my last hair off because those try to make things easy, but for a small detail I loose even more time to try to find an inelegant workaround.

This applies to MEF which doesn't let me choose which plugins I want to instanciate, and how many times each, or Linq to SQL which doesn't let me map by code the table names, I forgot the others.

I don't want to create XML mapping files to handle with after deploying an application, I want to pass a string argument in the constructor of my object, which it will be using then to prefix the table names, that's not so complicated !!!

I read in your last comment that there is a good way to prefix easily in EF, could you post a link to an article or other documentation about that to help me get started ?

Thanks a lot !
Lenard Gunda Re: Missing features from LINQ to SQL and Entity Framework: create tables, table name changes/prefixes
Thanks for the link, I will check it out, although my Linq to SQL usage has dropped to zero in favour of EF.

I actually had been using Entity Framework a lot lately. When using code first Entity Framework, you can create table prefixes easily, just by overriding the entity names when the model is being created (OnModelCreating() method of the DbContext.

I am not sure how to achieve the same when you use the designers though.
Max Re: Missing features from LINQ to SQL and Entity Framework: create tables, table name changes/prefixes
It had been a long while since the post so I am not sure if you are still looking for a solution to dynamic table name problem with LINQ.

Any way, I have just found a solution recently and like to share it with you. Check it out "http://www.codeproject.com/Articles/333249/Dynamic-Table-Mapping-for-Linq-to-SQL"