Resolving naming convention conflict between entities in EF4 and our database standards?

Question

I'm investigating replacing or supplementing our home grown ORM system with the Entity Framework 4, and I'm noticing that the latter may end up causing a conflict between what we've defined as the naming conventions for our programming code and our databases. Being a Microsoft shop, we've largely decided to follow Microsoft's naming guidelines for our code, which say to use Pascal casing for members, namespaces, etc.; to avoid using underscores, and so on.

The default entity naming conventions in EF4, not surprisingly, work great with these standards. For example, an entity named SalesOrder will generate a class named SalesOrder and an entity set named SalesOrders. An EF4 Model-First design will, by default, generate a table of the same name as the entity set (in this example, the generated table name is SalesOrders). However, our database standards suggest to use all lowercase and underscores between words (e.g., sales_orders). So, using the Entity Framework "as is" will cause us to start deviating from them.

Is there anywhere in the Entity Framework where you can override it's behavior to use the entity set name as the SQL table name? I can't seem to find an obvious place to specify an alternate table name for the generated SQL script. If we go forward with using EF4, is the only plausible solution to have us reconsider our database naming conventions?

Update:

I'm trying Ladislav's solution below, but I can't seem to get the Generate Database from Model option in the Entity Framework model designer to recognize my custom utility. I have a file named MyOrg.EF.Utility.CS.ttinclude in the folder:

%VSINSTALLDIR%\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\Templates\Includes

It essentially looks like this:

<#@ import namespace="Microsoft.CSharp"#>
<#@ import namespace="System.Text"#>


public class CustomUtilities
{
    public static string EntityNameToSqlName(string name)
    {
        string sqlName = "";    // The table name based on the input model name
        string pattern = "([A-Z]+[s])|([A-Z](?=[a-z]))|((?<=[a-z])[A-Z])";  //Pattern for the regex exp. below

        // Separate out each word with spaces:
        sqlName = System.Text.RegularExpressions.Regex.Replace(name, pattern, " $&");

        // Replace spaces with underscores and then make lowercase:
        sqlName = sqlName.Trim().Replace(" ", "_").ToLower();

        return sqlName;
    }

}

I've tried to reference this file in my custom .tt DDL generation file near the top as such:

<#@ include file="MyOrg.EF.Utility.CS.ttinclude"#>

However, if I try to reference the above function using code like this in the .tt file:

string tableName = CustomUtilities.EntityNameToSqlName(Id(entitySet.GetTableName()));

Visual Studio then complains that The name 'CustomUtilities' does not exist in the current context. Removing the class name from "CustomUtilities.EntityNameToSqlName" returns a similar error. Should I try a different way to insert a custom function into the DDL generation code?

Final solution:

I was able to finally get this working after I realized that I didn't wrap the C# code in my MyOrg.EF.Utility.CS.ttinclude file with this:

<#+
[my code]
#>

I also needed to add a public copy of the WriteColumns() method found in the file GenerateTSQL.Utility so that it would use my EntityNametoSqlName() method.

Unfortunately, my customized version of the original SSDLToSQL10.tt file is now a little messy, since I need to wrap CustomUtilities.EntityNameToSqlName() around quite a few items in there.

Answer 1

The correct solution is to change your database naming convention.

Why should the tail wag the dog? In modern programming most of the action takes place in the highly scalable business/service layer, not the database. Programmers should use a naming convention that works for both - and it should cater to the needs of the application developer who is going to be working with these objects day in and day out. In some cases it should probably cater to the needs of the front end developer, in others the server side.

The whole purpose of a naming convention is to reduce complexity. Yet the accepted solution here is to implement all kinds of additional complexity. And every other ORM would have to come up with their own convoluted solution to this artificial problem.

Answer 2

Sure there is. There is T4 template which converts your model to SQL DDL scripts. You can make a copy of this template and put your own logic for name generation into the new copy. After that you just need to set this template in your designer (DDL Generation Template property) and run Generate Database from Model ...

You will find default template in:

%VSINSTALLDIR%\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen\SSDLToSQL10.tt

Answer 3

You can do it . You need to implement a custom model builder to map your entities with relevant tables and relevant columns. You can override OnModelCreating function to build a custome model by adding this function to your context class.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
     modelBuilder.Entity<EntityName>().Map(c => c.ToTable("TableName"));//to map entity with table
     modelBuilder.Entity<EntityName>().Property(s => s.Property).HasColumnName("ColomnName");//to map properties with colomns
}