Suggestion Migration System and modeling of individuals and legal entities in system with Entity Framework

Question

Well, here’s the thing. I have a database that has the purpose of making a company’s ledger, basically this bank has a table CaixaCorrido with the columns Id, PessoaNome, DataHora, Descricao, Valor, Tipo (In or out).

Then the need arose to further detail this system, such as having a register of pessoa, may be física or jurídica, but in the old system this was not informed.

So I’d like suggestions on how to perform this data migration, which I’m using c#, with EntityFramework, PessoaFisica and PessoaJuridica inherit from Pessoa.

Answer 1

First let’s analyze whether the derivation of Pessoa is a composition or an inheritance. As a Pessoa cannot be both physical and legal at the same time, so it is a case of inheritance (a table represents the two entities). If it could, it would be a case of composition (a common table + 2 tables representing the data complement of each entity).

Therefore, a good modeling would be the following:

public class Pessoa
{
    [Key]
    public int PessoaId { get; set; }

    [Required]
    public String NomeOuRazaoSocial { get; set; }
}

public class PessoaFisica : Pessoa
{
    [Required]
    [Cpf] // Mais abaixo coloco a implementação desse atributo.
    [Unico(ErrorMessage = "Já existe uma pessoa física com este CPF.", ModelType = typeof(PessoaFisica))] // Deste também.
    public String Cpf { get; set; }
}

public class PessoaJuridica : Pessoa
{
    [Required]
    [Cnpj] // Mais abaixo coloco a implementação desse atributo.
    [Unico(ErrorMessage = "Já existe uma pessoa jurídica com este CNPJ.", ModelType = typeof(PessoaJuridica))] // Deste também.
    public String Cnpj { get; set; }
}

Here you go [Cnpj].

Here you go [Cpf].

UnicoAttribute.cs

Before implementing this attribute, install the System.Linq.Dynamic package:

[AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
public class UnicoAttribute : ValidationAttribute
{
    public Type ModelType { get; set; }

    public UnicoAttribute() : base() { }
    public UnicoAttribute(Type _modelType) : base()
    {
        ModelType = _modelType;
    }

    protected override ValidationResult IsValid(object value, ValidationContext validationContext)
    {
        using (MeuContext db = new MeuContext())
        {
            var Name = validationContext.MemberName;

            if (string.IsNullOrEmpty(Name))
            {
                var displayName = validationContext.DisplayName;
                var prop = validationContext.ObjectInstance.GetType().GetProperty(displayName);

                if (prop != null)
                {
                    Name = prop.Name;
                }
                else
                {
                    var props = validationContext.ObjectInstance.GetType().GetProperties().Where(x => x.CustomAttributes.Count(a => a.AttributeType == typeof(DisplayAttribute)) > 0).ToList();

                    foreach (PropertyInfo prp in props)
                    {
                        var attr = prp.CustomAttributes.FirstOrDefault(p => p.AttributeType == typeof(DisplayAttribute));

                        var val = attr.NamedArguments.FirstOrDefault(p => p.MemberName == "Name").TypedValue.Value;

                        if (val.Equals(displayName))
                        {
                            Name = prp.Name;
                            break;
                        }
                    }
                }
            }

            PropertyInfo IdProp = validationContext.ObjectInstance.GetType().GetProperties().FirstOrDefault(x => x.CustomAttributes.Count(a => a.AttributeType == typeof(KeyAttribute)) > 0);

            var Id = (Guid)IdProp.GetValue(validationContext.ObjectInstance, null);

            Type entityType = validationContext.ObjectType;

            var result = db.Set(ModelType ?? entityType).Where(Name + "==@0", value);
            int count = 0;

            if (Id != Guid.Empty)
            {
                result = result.Where(IdProp.Name + "<>@0", Id);
            }

            count = result.Count();

            if (count == 0)
                return ValidationResult.Success;
            else
                return new ValidationResult(ErrorMessageString);
        }
    }
}

To migrate the data, you can build an SQL script with the table already created by Migrations or make a Seed. Seed is recommended for small data volumes. SQL script for large volumes.

If the path is by the SQL script, watch out for the filling of the column discriminator with "Personal" or "Personal".

Answer 2

Complementing the response of Gypsy, validations that need to consult the Database, can be made direct in Context, for this to overwrite the method DbContext.ValidateEntity

Before implementing this attribute, install the package System.Linq.Dynamic:

public class IndexAttribute : System.ComponentModel.DataAnnotations.Schema.IndexAttribute
{
    public string ErrorMessageString { get; set; }
}

protected override DbEntityValidationResult ValidateEntity(DbEntityEntry entityEntry, IDictionary<object, object> items)
{
    var result = base.ValidateEntity(entityEntry, items);
    var entidade = entityEntry.Entity;
    var dataSet = this.Set(entidade.GetType());
    var propriedades = entidade.GetType()
        .GetProperties();

    var keysProps = propriedades
        .Where(x => x.GetCustomAttributes(typeof(KeyAttribute), false).Any())
        .ToList();

    var indices = propriedades
        .Where(x => x.GetCustomAttributes(typeof(IndexAttribute), false).Any())
        .Select(x => new { PropertyInfo = x, Indice = (IndexAttribute)x.GetCustomAttributes(typeof(IndexAttribute), false).First() })
        .Where(x => x.Indice.IsUnique)
        .GroupBy(x => x.Indice.Name, x => x)
        .ToDictionary(x => x.Key, x => x.ToList());

    var indiceNames = new string[indices.Count];
    indices.Keys.CopyTo(indiceNames, 0);
    foreach (var indiceName in indiceNames)
    {
        var indiceProps = indices[indiceName];
        var consulta = dataSet.AsQueryable();
        foreach (var indiceProp in indiceProps.Select(x => x.PropertyInfo))
        {
            var indice = indiceProp.GetValue(entidade, null);
            consulta = consulta.Where(indiceProp.Name + "==@0", indice);
        }
        foreach (var keyProp in keysProps)
        {
            var key = keyProp.GetValue(entidade, null);
            consulta = consulta.Where(keyProp.Name + "<>@0", key);
        }

        var resultado = consulta.ToListAsync().GetAwaiter().GetResult();
        if (resultado.Count > 0)
        {
            foreach (var link in indiceProps)
            {
                result.ValidationErrors.Add(new DbValidationError(link.PropertyInfo.Name, link.Indice.ErrorMessageString));
            }
        }
    }
    return result;
}

P.S.: I tried to adapt the code written by the gypsy, but I did not test the same.