Loop over entity column mappings to transform colu

Question

I would like to apply a single transformation over a large number of columns in Entity Framework 5 without having to explicitly type them all out. As an example I would like to do the following over 50+ columns (convert PascalCase to UNDERSCORE_CASE).

modelBuilder.Entity<Department>() 
            .Property(t => t.DepartmentName) 
            .HasColumnName("DEPARTMENT_NAME");

I found the Dapper.FluentMap which can provide this functionality but it doesn't appear to work when creating the query.

Is there a way to loop over the list of properties and specify the column name following a pattern? For reference the Dapper Transform is listed as

public PropertyTransformConvention()
{
    Properties()
        .Configure(c => c.Transform(s => Regex.Replace(input: s, pattern: "([A-Z])([A-Z][a-z])|([a-z0-9])([A-Z])", replacement: "$1$3_$2$4")));
}

EDIT: This is similar to this question but it does not work for me. Perhaps this has different requirements for EF5.

Using the answer from @Hopeless I have attempted the following modification but the syntax is not quite right. I am new to EF so am not familiar with how to convert the older syntax to the newer.

modelBuilder.Entity<Job>()
            .Map(m =>
{
    m.Properties<Job>(e => e.HasColumnName(name => RegEx.Replace(name, "(?<=.)(?=[A-Z])", "_").ToUpper()));
});

Answer 1

That´s how I solved it. My goal was to have a camelCase convention applied just to an specific table/entity ( Table "Client" ).

   modelBuilder.Properties().Configure(p => p.HasColumnName(GetDBName(p, p.ClrPropertyInfo.Name)));


private string GetDBName(ConventionPrimitivePropertyConfiguration p, string name)
{
                var result = name;
                var entityName = p.ClrPropertyInfo.ReflectedType.UnderlyingSystemType.Name;
                if (entityName == "Client")
                    result = Helper.CamelCaseParaSnakeCaseOracle(name);
                return result;
}

static public string CamelCaseParaSnakeCaseOracle(string input)
{
                return Regex.Replace(input,
                  @"(?:\b|(?<=([A-Za-z])))([A-Z][a-z]*)",
                  m => string.Format(@"{0}{1}",
                    (m.Groups[1].Value.Length > 0) ? "_" : "", m.Groups[2].Value.ToUpper()));
}

Answer 2

You can use the Properties method of DbModelBuilder. Translate the pascal case pattern to underscore pattern easily like this:

modelBuilder.Properties()
            .Configure(e => e.HasColumnName(Regex.Replace(e.ClrPropertyInfo.Name, "(?<=.)(?=[A-Z])", "_").ToUpper());

The pattern can also be like this (.)([A-Z]) and the replacement should then be $1_$2.

Of course the input name should exactly have form of SomeThing. You can also take the pattern in Dapper (posted in your question), which works more exactly for some other rare cases (even including this format DDos (which will be converted to D_Dos). The point here is it does not translate to uppercase for you.

Edit:

It's a pity that in EF5, modelBuilder does not have Properties() method. So for a specific entity type, you can try this:

//in your OnModelCreating scope
//names of navigation properties defined in Job should be passed 
//in TransformAllColumns method
new CapsUnderscorePropertiesConfig<Job>(modelBuilder).TransformAllColumns();

//a helper class
public class CapsUnderscorePropertiesConfig<T> where T : class
{
    EntityTypeConfiguration<T> _entityConfig;
    Dictionary<Type, MethodInfo> _propertyMethods = new Dictionary<Type,MethodInfo>();
    MethodInfo propertyForStruct;
    MethodInfo propertyForNullableStruct;
    public CapsUnderscorePropertiesConfig(DbModelBuilder modelBuilder)
    {
        _entityConfig = modelBuilder.Entity<T>();               
    }
    void config(PropertyInfo pInfo)
    {
        var p = Expression.Parameter(typeof(T));
        var expType = typeof(Expression<>).MakeGenericType(typeof(Func<,>).MakeGenericType(typeof(T), pInfo.PropertyType));
        MethodInfo mi;
        _propertyMethods.TryGetValue(pInfo.PropertyType, out mi);
        if (mi == null)
        {
            if (pInfo.PropertyType.IsValueType)
            {
                //find the Property method for struct type having argument matching Expression<Func<TEntityType, T?>>
                //note the T? inside Func<...> (there is another overload but with T instead).
                if (propertyForStruct == null)
                {
                    foreach(var prop in _entityConfig.GetType().GetMethods().Where(m => m.Name == "Property" && m.IsGenericMethodDefinition)
                                                     .Select(e => new { genMethodDef = e, genMethod = e.MakeGenericMethod(pInfo.PropertyType) })){    
                        //there should be just 2 generic Property<T> methods filtered inhere.
                        //One is for nullable struct and the other is for struct.
                         var secondFuncArgType = prop.genMethodDef.GetParameters()[0].ParameterType.GetGenericArguments()[0].GetGenericArguments()[1];
                         if (secondFuncArgType.IsGenericType && secondFuncArgType.GetGenericTypeDefinition() == typeof(Nullable<>))
                             propertyForNullableStruct = prop.genMethodDef;
                         else propertyForStruct = prop.genMethodDef;
                    }
                }
                mi = pInfo.PropertyType.IsGenericType && pInfo.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? 
                    propertyForNullableStruct.MakeGenericMethod(pInfo.PropertyType) :
                    propertyForStruct.MakeGenericMethod(pInfo.PropertyType);
            }
            else //possible property type is string, byte[] or geo type
            {
                mi = _entityConfig.GetType().GetMethods().Single(m => m.Name == "Property" && !m.IsGenericMethodDefinition 
                                                                 && m.GetParameters()[0].ParameterType == expType);
            }
            _propertyMethods[pInfo.PropertyType] = mi;
        }
        var propConfig = mi.Invoke(_entityConfig, new object[] { Expression.Lambda(Expression.Property(p, pInfo.Name), p) }) as PrimitivePropertyConfiguration;            
        propConfig.HasColumnName(Regex.Replace(pInfo.Name, "(?<=.)(?=[A-Z])", "_").ToUpper());
    }
    //at the time of configuring, the Metadataworkspace is not present
    //So we cannot know which properties are navigation properties
    //Those propertie can be excluded by passing their names in here
    public void TransformAllColumns(params string[] excludedNavProperties)
    {
        foreach (var prop in typeof(T).GetProperties().Where(p => !excludedNavProperties.Contains(p.Name)))
        {
            config(prop);
        }
    }
}

NOTE: The code above works only for direct properties, if you have some complex properties (returning some ComplexType), then it won't work. Technically you need to exclude all properties (returning ComplexType) from the entity's properties, then if possible merge the properties of ComplexType with the entity's direct properties before looping through all and configuring each.

PS: I'm not sure if Dapper.FluentMap supports EF5, but from the code you posted, it can be as easy as appending the ToUpper() method like this:

public PropertyTransformConvention()
{
   Properties()
    .Configure(c => c.Transform(s => Regex.Replace(input: s, pattern: "([A-Z])([A-Z][a-z])|([a-z0-9])([A-Z])", replacement: "$1$3_$2$4").ToUpper()));
}

I've tried visiting the homepage of Dapper.FluentMap and looks like that it has some classes based on Convention (if this is from EF, it is supported only since EF6). So I'm not sure if the Dapper's code works in EF5. If it works you should try the code above for convenience.