Generated column 'UserCode' cannot refer to auto-increment column. EfCore

user_4290481 0
Viewed 265

I have an account table that has ID auto-increment as true. Now I want to add Computed Column based on which. It can Add new value To the UserCode by the following Logic.

builder.Entity<Account>().Property(p => p.UserCode)
                .HasComputedColumnSql(@"CONCAT(CASE WHEN Role= 0 THEN 'ADM' 
                                                    WHEN Role = 1 then 'ADV' 
                                                    WHEN Role = 2 then 'INV' 
                                                    WHEN ROle = 3 then 'EMP' END,

                                        LPAD(ID, 8, '0'))");

Here is the Account Entity and DB set public DbSet<Account> Accounts { get; set; }

When I am running the migration, It's working fine But when I am executing Update Database, I am getting the following error.
Generated column 'UserCode' cannot refer to the auto-increment column.

PS: I don't want to use any trigger or more than one update statement.

entity-framework-core asp.net-core mysql
1 Answers

This is not allowed in MySQL. (https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html)

An AUTO_INCREMENT column cannot be used as a base column in a generated column definition.

This is likely due to such a column being a violation of 3NF and decided not to be supported when MySQL introduced generated columns. Generated columns need to work optionally as both virtual and stored values, which would cause a distinct "chicken & egg" scenario in the stored flavour.

Your options are either to use a trigger on insert to populate a stored column dependent on the key, violating 3NF and introducing a possible data inconsistency between the Key and a column dependent on that key; Edit: Appears to be an illegal option in MySQL :) Or compute this property as an unmapped property entirely within your entity.

For instance:

public class Account
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int AccountId { get; set; } 

    public Roles Role { get; set; }

    [NotMapped]
    public string UserCode 
    {
        get 
        {
            switch(Role)
            {
                case Roles.Admin:
                    return $"ADM{AccountId:00000000}";
                case Roles.Advisor:
                    return $"ADV{AccountId:00000000}";
                case Roles.Invenstor:
                    return $"INV{AccountId:00000000}";
                case Roles.Employee:
                    return $"EMP{AccountId:00000000}";
                default:
                    return $"???{AccountId:00000000}";
            }
        }
    }
}
user_4234970
Related