how can I add table prefixes to tables created using model first in EF4?

Question

My client has a db table naming convention that requires me to prefix all tables with an application specific prefix - like "myapp_" - I am using EF4 with the model first approach.

I can specify a custom db namespace but they do not want to introduce a new namespace for this app - none of their other apps use namespaces other than dbo.

I went down the path of customizing the t4 templates but I could not specify which t4 templates the workflow should use when generating the 3 metadata files that EF needs at runtime.

Is there a preferred way to accomplish the table prefix requirement using EF?

thanks Mcihael

Answer 1

I'm not sure if there is a template for generating edmx files that the Database Generation Workflow would be using, but it definitely use one for DDL generation. which is located at this path:
<Program Files>\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen
Inside this folder look for the file named SSDLToSQL10.tt You’ll have to manually copy and paste this file in the very same folder in order for VS2010 to recognize it and then you can modify and customize it to prefix the table names based on your DB naming convention. Inside the T4 template, look for this code:

foreach (EntitySet entitySet in Store.GetAllEntitySets())
           {
               string schemaName = Id(entitySet.GetSchemaName());
               string tableName = Id(entitySet.GetTableName());
               ...

You can change the table name to have your custom prefix. There are 3 places inside the T4 that has the above code and you need to apply the prefix. Once you are done save and close the file and go back to your EDM inside VS. You'll see that now when you drop down the DDL Generation Template property, the new T4 file that you copied and changed is an available option. Select that and now when you generate database from model, you'll find your table names have the prefix

Therefore, it’s possible to modify the template and customize how the DDL is built. There is a catch tough: The Workflow does not use the modified T4 to change the edmx file for storage and mappings definitions (SSDL & MSL). So even though the database have been created correctly, the edmx still pointing to the original table names.

Therefore, you'll need to open the edmx in XML editor and manually change C-S mapping (MSL) and SSDL contents to have your prefixes. For that you only need to change StoreEntitySet in mappings and the Name of EntitySets in SSDL content.