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.

You can use extension `public static class ExtensionMethods { public static string ToUnderscoreCase(this string str) { str = str.Insert(0, str[0].ToString().ToLower()).Remove(1, 1); return string.Concat(str.Select((x, i) => i > 0 && char.IsUpper(x) ? "_" + x.ToString() : x.ToString())).ToLower(); } }` — Vivek Nuna, Jul 20 '18 at 07:06

score 3 · Accepted Answer · answered Nov 19 '21 at 13:28

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

It's not required to have DbSet properties for every Entity type. Related types will be added automatically, and Entities can be registered directly in OnModelCreating. So using Reflection is error-prone, and also not needed. The ModelBuilder.Model is built based on conventions and attributes before the call to OnModelCreating, and you can change it there. — David Browne - Microsoft, Nov 19 '21 at 13:52

score 0 · Answer 2 · answered Nov 19 '21 at 13:46

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);
}

EF Core - Define custom column mapping strategy for all properties

2 Answers2