EF Core - Define custom column mapping strategy for all properties

Question

By convention, each property will be set up to map to a column with the same name as the property. If I want to change the default mapping strategy, I can do it by using either Fluent API or Data Annotation. But, I want to set a custom mapping strategy for all the properties in all entities to the database columns. My database is exists and the column names are like ID, CUSTOMER_NAME, CREDIT_AMOUNT and so on, so the column names don't follow PascalCase notation. All object names are in upper case and individual words separated with "_" symbol. This is true for the entire database. And I want to map this naming to a class like this:

public class Payment
{
    public int ID { set; get; }
    public string CustomerName { get; set; }
    public decimal CreditAmount { get; set; }
}

The database is large and I don't want to map each property and class names to the appropriated database objects. Is there any global way to define this type of mapping like this?

CustomerName -> CUSTOMER_NAME, CreditAmount -> CREDIT_AMOUNT and so on.

Answer 1

You just need to iterate the Entities and Properties from modelBuilder.Model, eg

string ToDatabaseIdentifier(string propertyName)
{
    var sb = new System.Text.StringBuilder();
    for (int i = 0; i < propertyName.Length; i++)
    {
        var c = propertyName[i];
        if (i>0 && Char.IsUpper(c) && Char.IsLower(propertyName[i-1]))
        {
            sb.Append('_');
        }
        sb.Append(Char.ToUpper(c));
    }
    return sb.ToString();
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    foreach (var e in modelBuilder.Model.GetEntityTypes())
    {   
        e.SetTableName(ToDatabaseIdentifier(e.Name));
        foreach (var p in e.GetProperties())
        {
            p.SetColumnName(ToDatabaseIdentifier(p.Name));
        }
    }

    base.OnModelCreating(modelBuilder);
}

Answer 2

Possible way of doing that convention with reflection like this: Getting Entity types from DBSet properties of DbContext class Then getting properties (columns) of entity types
So In your DbContext class add this line:

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            //see below for this extension method
            this.ApplyCaseMappingRule(modelBuilder);

            base.OnModelCreating(modelBuilder);
        }

Extension method source:

public static class Extensions
    {
        public static void ApplyCaseMappingRule<TDbContext>(this TDbContext _, ModelBuilder modelBuilder) where TDbContext:DbContext
        {
            var ignoreType = typeof(NotMappedAttribute);
            var dbSetProps = typeof(TDbContext).GetProperties();
            foreach (var dbSetProp in dbSetProps)
            {
                if (dbSetProp.PropertyType.TryGetEntityTypeFromDbSetType(out var entityType))
                {
                    modelBuilder.Entity(entityType, option =>
                    {
                        option.ToTable(Mutate(dbSetProp.Name));
                        var props = entityType.GetProperties();
                        foreach (var prop in props)
                        {
                            //check if prop has Ignore attribute
                            var hasIgnoreAttribute =
                                prop.PropertyType.CustomAttributes.Any(x => x.AttributeType == ignoreType);
                            if (hasIgnoreAttribute) continue;

                            option.Property(prop.PropertyType, prop.Name).HasColumnName(Mutate(prop.Name));
                        }
                    });
                }
            }
        }

        private static bool TryGetEntityTypeFromDbSetType(this Type dbSetType, out Type entityType)
        {
            entityType = null;
            if (dbSetType.Name != "DbSet`1") return false;
            if (dbSetType.GenericTypeArguments.Length != 1) return false;
            entityType = dbSetType.GenericTypeArguments[0];
            return true;
        }

        public static IEnumerable<string> SplitCamelCase(this string source)
        {
            const string pattern = @"[A-Z][a-z]*|[a-z]+|\d+";
            var matches = Regex.Matches(source, pattern);
            foreach (Match match in matches)
            {
                yield return match.Value;
            }
        }

        public static string Mutate(string propName)
        {
            return string.Join("_", propName.SplitCamelCase().Select(x => x.ToUpperInvariant()));
        }

Tested on .NET 5 with EF 5.0.0