RU with TPT inheritance and audit fields

Asked

Viewed 190 times

1

Hello.

I’ve looked for answers in TPT and Entity tags but I can’t find anything like it.

My problem is trying to use an inheritance model (TPT) where tables have fields with the same name (example: DataCadastro and Status). At the time of entering the record indicates the error indicating that it does not accept the null field. Below is the model:

public interface ICambioBase
{
    EStatus Status { get; set; }
    DateTime DataCadastro { get; set; }
}

public abstract class Usuário : ICambioBase
{
    public int Id { get; set; }
    public EPessoaFJ Tipo { get; set; }
    public string Nome { get; set; }
    public short NacionalidadeId { get; set; }
    public EStatus Status { get; set; }
    public DateTime DataCadastro { get; set; }

    public virtual País País { get; set; }

    public virtual ICollection<Documento> Documentos { get; set; }
    public virtual ICollection<Email> Emails { get; set; }
    public virtual ICollection<Telefone> Telefones { get; set; }
    public virtual ICollection<Endereço> Endereços { get; set; }
}

public class Pessoa : Usuário
{
    public Pessoa()
    {
        Tipo = EPessoaFJ.Física;
    }
    public ESexo Sexo { get; set; }
    public DateTime DataNascimento { get; set; }
    public EEstadoCivil EstadoCivilId { get; set; }
}

Code to enter Person:

        Pessoa pessoa = new Pessoa();

        pessoa.DataNascimento = Convert.ToDateTime("03/12/1976");
        pessoa.EstadoCivilId = EEstadoCivil.Casado;
        pessoa.País = ctx.Países.Single(b => b.Sigla == "BRA");
        pessoa.Nome = "Diogenes Morais de Santana";
        pessoa.DataCadastro = DateTime.Now;
        pessoa.Status = EStatus.Ativo;
        pessoa.Sexo = ESexo.Masculino;

        ctx.Pessoas.Add(pessoa);
        ctx.SaveChanges();


    ERRO: Cannot insert the value NULL into column 'DataCadastro', table 'CAMBIO2001.dbo._Pessoas'; column does not allow nulls. INSERT fails.The statement has been terminated.

Script Creation of Tables:

CREATE TABLE _Usuários
(
    Id              INT IDENTITY(1,1) PRIMARY KEY,
    Tipo            BIT NOT NULL, -- DOCUMENTO DE PESSOA FISICA OU JURIDICA
    Nome            VARCHAR(60) NOT NULL,
    NacionalidadeId SMALLINT NOT NULL FOREIGN KEY REFERENCES _Países(Id),
    Status          BIT NOT NULL,
    DataCadastro    DATETIME2(0) NOT NULL
);

CREATE TABLE _Pessoas
(
    Id              INT PRIMARY KEY FOREIGN KEY REFERENCES _Usuários(Id),
    Sexo            BIT NOT NULL,
    DataNascimento  DATE NOT NULL,
    EstadoCivilId   TINYINT NOT NULL FOREIGN KEY REFERENCES _EstadosCivis(Id),
    Status          BIT NOT NULL,
    DataCadastro    DATETIME2(0) NOT NULL
);

Context class with override of the Savechanges method:

        #region Tables
    public DbSet<País> Países { get; set; }
    public DbSet<Usuário> Usuários { get; set; }
    public DbSet<Pessoa> Pessoas { get; set; }
    public DbSet<Empresa> Empresas { get; set; }
    public DbSet<DocumentoTipo> DocumentosTipos { get; set; }
    public DbSet<Documento> Documentos { get; set; }
    public DbSet<Email> Emails { get; set; }
    public DbSet<Telefone> Telefones { get; set; }
    public DbSet<Endereço> Endereços { get; set; }
    public DbSet<ParceiroFunção> TiposParceiros { get; set; }
    public DbSet<Assessor> Assessores { get; set; }
    public DbSet<Apresentante> Apresentantes { get; set; }
    public DbSet<ClienteTipo> ClientesTipos { get; set; }
    public DbSet<Ocupação> Ocupações { get; set; }
    public DbSet<ClienteSituação> ClientesSituações { get; set; }
    public DbSet<Cliente> Clientes { get; set; }
    public DbSet<Banco> Bancos { get; set; }
    #endregion


    public Cotação_Contexto() : base("Cotacao_Connection")
    {
        Configuration.LazyLoadingEnabled = false;
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

        #region Models
        modelBuilder.Configurations.Add(new MapPaíses());
        modelBuilder.Configurations.Add(new MapUsuários());
        modelBuilder.Configurations.Add(new MapPessoas());
        modelBuilder.Configurations.Add(new MapEmpresas());
        modelBuilder.Configurations.Add(new MapDocumentosTipos());
        modelBuilder.Configurations.Add(new MapDocumentos());
        modelBuilder.Configurations.Add(new MapEmails());
        modelBuilder.Configurations.Add(new MapTelefones());
        modelBuilder.Configurations.Add(new MapEndereços());
        modelBuilder.Configurations.Add(new MapParceirosFunções());
        modelBuilder.Configurations.Add(new MapAssessores());
        modelBuilder.Configurations.Add(new MapApresentantes());
        modelBuilder.Configurations.Add(new MapOcupações());
        modelBuilder.Configurations.Add(new MapClientesSituações());
        modelBuilder.Configurations.Add(new MapClientesTipos());
        modelBuilder.Configurations.Add(new MapClientes());
        modelBuilder.Configurations.Add(new MapBancos());
        #endregion

        base.OnModelCreating(modelBuilder);
    }

    public override int SaveChanges()
    {
        var context = ((IObjectContextAdapter)this).ObjectContext;

        IEnumerable<ObjectStateEntry> objectStateEntries =
            from e in context.ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Modified)
            where
                e.IsRelationship == false &&
                e.Entity != null &&
                typeof(ICambioBase).IsAssignableFrom(e.Entity.GetType())
            select e;

        var dataAtual = DateTime.Now;

        foreach (var entry in objectStateEntries)
        {
            dynamic entityBase = entry.Entity;

            if (entry.State == EntityState.Added || entityBase.DataCriacao == DateTime.MinValue)
            {
                entityBase.DataCadastro = dataAtual;
                entityBase.Status = EStatus.Ativo;
            }

        }

        //var entries = ChangeTracker.Entries();

        //foreach (var entry in entries)
        //  if (entry.State == EntityState.Added)
        //      foreach (var propname in entry.CurrentValues.PropertyNames)
        //          if (propname == "DataCadastro")
        //              entry.CurrentValues["DataCadastro"] = DateTime.Now;

        try
        {
            return base.SaveChanges();
        }
        catch (DbEntityValidationException e)
        {
            foreach (var eve in e.EntityValidationErrors)
            {
                Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
                    eve.Entry.Entity.GetType().Name, eve.Entry.State);
                foreach (var ve in eve.ValidationErrors)
                {
                    Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
                        ve.PropertyName, ve.ErrorMessage);
                }
            }
            throw;
        }
    }
}

ERROR: Cannot Insert the value NULL into column 'Datajoin', table 'dbo. _People'; column does not allow nulls. INSERT fails. The statement has been terminated

Any suggestions?

c# asp.net-mvc entity-framework

  • you are passing person.Datacadastro = Datetime.Now;?

2 answers

3

Your project draws my attention by not following several standards recommended for the Entity Framework, starting with the non-use of Migrations, that saves you from generating scripts to update your base.

The right way to do what you want, as for the registration date, is to reimplement the method SaveChanges in the context. This makes you not depend on a builder (who, by the way, is very strange in his example) and does not have to make unnecessary inheritances. For this case, an interface works better, precisely because a class can only inherit one and only one class, and if you really need to inherit another class, you will have problems.

Another criticism that deserves special attention is the fact that you are using strings where you could use Enums. For example:

pessoa.EstadoCivil = ctx.EstadosCivis.Where(ec => ec.Descrição.ToLower().Contains("casado")).Single();

EstadoCivil nor should it be a Model. You will insert civil states very few times into the life cycle of your system. In addition, the following sentence causes an unnecessary performance burden for business logic.

Finally, this:

pessoa.Pais = ctx.Paises.Where(b => b.Sigla == "BRA").Single();

Can be rewritten as follows:

pessoa.Pais = ctx.Paises.Single(b => b.Sigla == "BRA");
  • 1

    Thank you for the indications. I will make the suggested modifications and indicate if everything has occurred successfully.

  • I made the indicated changes but the main issue that is the error message remains. I inserted the method savechanges and updated the Datajoin property without success. public override int Savechanges() { var Entries = Changetracker.Entries(); foreach (var entry in Entries) { if (entry.State == Entitystate.Added) { entry.Property("Dataregistration"). Currentvalue = Datetime.Now; } } Return base.Savechanges(); }

  • Put this code in your question, please. You have made a debug of this code to check if the value is even being set?

  • Yes, I performed the debug and even with the value appearing in the "Person" entity when returning the base. Savechanges() the error continues. Sorry, but I didn’t understand the excerpt you say "Put this code in your question", you’re referring to me to edit my question and insert the savechanges excerpt in it?

  • That. Exactly. The idea is to help the community with their question. The more complete it is possible, the better.

  • I updated the question already with the modifications of the classes and use of Interface. Apparently the inheritance by type does not allow that in both tables I have columns with the same name.

  • But you are not using as in the answer I put as link. This way is not guaranteed that it will work. Nor interface test your example has. Do it the other way.

  • Gypsy. I updated according to your link, I modified the name of the interface and fields, but still the error remains. I also tested with the commented code of the Savechanges method.

  • Well, now I don’t know what can be. The code is correct. Only with debug and testing to know.

Show 4 more comments

1


After a few days of breaking my head and leaving aside the question of inheritance, I resumed the model with the inheritance by modifying a small piece of the code. In the equal names property I added a letter indicating the level and in the mapping of the property I indicated the name of the table column with Hascollumnname and then successfully managed the insertion of the data. Follows:

Model:

public abstract class Usuário
{
    public int Id { get; set; }
    public EPessoaFJ Tipo { get; set; }
    public string Nome { get; set; }
    public short NacionalidadeId { get; set; }
    public EStatus Status { get; set; }
    public DateTime DataCadastro { get; set; }

    public virtual País Nacionalidade { get; set; }
    public virtual List<Documento> Documentos { get; set; }
}
public class Pessoa : Usuário
{
    public ESexo Sexo { get; set; }
    public DateTime DataNascimento { get; set; }
    public EEstadoCivil EstadoCivilId { get; set; }
    public EStatus Statusb { get; set; } //inseri uma letra indicando o segundo nível
    public DateTime DataCadastrob { get; set; }
}

Mappings:

    public MapUsuários()
    {
        this.ToTable("_Usuários");

        HasKey(u => u.Id);

        Property(u => u.Tipo)
            .IsRequired();

        Property(u => u.Nome)
            .HasMaxLength(60)
            .IsRequired();

        Property(u => u.NacionalidadeId)
            .IsRequired();

        Property(u => u.Status)
            .IsRequired();

        Property(u => u.DataCadastro)
            .IsRequired();

        HasRequired(u => u.Nacionalidade).WithMany().HasForeignKey(u => u.NacionalidadeId);

        HasMany(u => u.Documentos).WithRequired(d => d.Usuário).HasForeignKey(d => d.UsuárioId);
    }
    public MapPessoas()
    {
        this.ToTable("_Pessoas");

        HasKey(p => p.Id);

        Property(p => p.Sexo)
            .IsRequired();

        Property(p => p.DataNascimento)
            .IsRequired();

        Property(p => p.EstadoCivilId)
            .IsRequired();

        Property(p => p.Statusb)
            .HasColumnName("Status") //Indicando o nome da coluna no banco
            .IsRequired();

        Property(p => p.DataCadastrob)
            .HasColumnName("DataCadastro")
            .IsRequired();

    }

Insertion test

private void btnIniciar_Click(object sender, EventArgs e)
    {
        País país = new País();
        país.Nome = "Brasil";
        país.Sigla = "BRA";

        ctx.Países.Add(país);

        DocumentoTipo dt = new DocumentoTipo();
        dt.Descrição = "Outros";
        dt.Tipo = EPessoaFJ.Física;

        ctx.DocumentosTipos.Add(dt);

        dt = new DocumentoTipo();
        dt.Descrição = "RG";
        dt.Tipo = EPessoaFJ.Física;

        ctx.DocumentosTipos.Add(dt);

        dt = new DocumentoTipo();
        dt.Descrição = "CNPJ";
        dt.Tipo = EPessoaFJ.Jurídica;

        ctx.DocumentosTipos.Add(dt);

        Pessoa pessoa = new Pessoa();
        pessoa.Tipo = EPessoaFJ.Física;
        pessoa.Nome = "Diogenes Morais de Santana";
        pessoa.Nacionalidade = ctx.Países.Local.Single(p => p.Sigla == "BRA");

        pessoa.Sexo = ESexo.Masculino;
        pessoa.DataNascimento = Convert.ToDateTime("XX/XX/XX76");
        pessoa.EstadoCivilId = EEstadoCivil.Casado;
        pessoa.Statusb = EStatus.Ativo;
        pessoa.DataCadastrob = DateTime.Now;
        pessoa.Documentos = new List<Documento>();
        Documento doc = new Documento();
        doc.DocumentoTipo = ctx.DocumentosTipos.Local.Single(dtip => dtip.Descrição == "RG");
        doc.Numero = "xxxxxxxx";
        doc.DataEmissão = Convert.ToDateTime("01/01/2001");
        doc.Emissor = "SSP/SP";
        doc.Nacionalidade = ctx.Países.Local.Single(p => p.Sigla == "BRA");

        pessoa.Documentos.Add(doc);

        ctx.Usuários.Add(pessoa);

        ctx.SaveChanges();
    }

I know it’s not a technical solution, but it solved my problem.

Thank you very much Gypsy.

  • In fact this is a clear case that additional mapping hinders more than helps. If you completely abandon the Fluent API you wouldn’t have this problem.

Browser other questions tagged c# asp.net-mvc entity-framework

You are not signed in. Login or sign up in order to post.