Table relationship - EF

Question

Good Afternoon!

I have the "Service Order" and "Attachment" tables. In my "Service Order" table, I want to be able to attach the scanned service order and be able to attach pictures of the equipment.

Work order

[Table("OrdemDeServico")]
public class OrdemDeServico
{
    public OrdemDeServico()
    {

        OrdemEscaneada = new Anexo();
        FotosDoEquipamento = new List<Anexo>();
    }


    [Key]
    public int Id { get; set; }

    public int? OrdemEscaneada_Id { get; set; }

    [Display(Name="Ordem de Serviço")]
    [ForeignKey("OrdemEscaneada_Id")]
    public virtual Anexo OrdemEscaneada { get; set; }
    public virtual List<Anexo> FotosDoEquipamento { get; set; }
...

Now for the Annex

public class Anexo
{

    [Key]
    public int Id { get; set; }

    [StringLength(80)]
    public string Nome { get; set; }

    public Byte[] Arquivo { get; set; }

}

I can add and remove the scanned order easily. I know it would be better to put the relationship in the table "Attachment", but I can’t do it in a good way. I tried the following:

 public int? FotosDoEquipamento_Id { get; set; }

    [ForeignKey("FotosDoEquipamento_Id")]
    public virtual OrdemDeServico FotosDoEquipamento { get; set; }

Only that still creating "Ordemdeservico_id", must be the relationship on account of the orderSampled, but the foreign key is in another table, and I thought it would not give problem.

The point is that I don’t know how to add the photos. I didn’t want to create another table just for this, since this one already has what I need.

The table design created by the system:

Answer 1

Your code is quite out of standard, and this makes the Entity Framework may not work properly.

I’ll start with the entity OrdemDeServico:

[Table("OrdemDeServico")]
public class OrdemDeServico
{
    public OrdemDeServico() // Este construtor não precisa.
    {
        // Estas inicializações não precisam.
        // O Entity Framework inicializa elas para você.
        OrdemEscaneada = new Anexo();
        FotosDoEquipamento = new List<Anexo>();
    }

    [Key]
    public int Id { get; set; }
    // Esta chave estrangeira está fora do padrão. 
    // O correto é OrdemEscaneadaId.
    public int? OrdemEscaneada_Id { get; set; }

    [Display(Name="Ordem de Serviço")]
    [ForeignKey("OrdemEscaneada_Id")] // Isto aqui não é necessário.
    public virtual Anexo OrdemEscaneada { get; set; }
    // List implementa ICollection. A recomendação é usar
    // a interface, até para que você tenha mais opções na 
    // hora de iterar os objetos.
    public virtual List<Anexo> FotosDoEquipamento { get; set; }
...

It’s not incorrect, but it needs to improve.

Already Anexo I can’t say the same. You defined relationships incorrectly. If Anexo may belong to an order of service, but at the same time may contain another order of service (as it is a scanned order), the relationship becomes a paradox. By the Entity Framework convention, if OrdemDeServico contains a "Scan Order", so "Scan Order" does not need to reference OrdemeServico, but "Scanned Order" remains a Anexo, which forces the table to have a key.

This is not simple to solve and calls for a mixed approach. First, modify Anexo forever belong to a OrdemDeServico:

public class Anexo
{
    [Key]
    public int Id { get; set; }
    public int OrdemServicoId { get; set; }

    [StringLength(80)]
    public string Nome { get; set; }

    public Byte[] Arquivo { get; set; }

    public virtual OrdemDeServico OrdemDeServico { get; set; }
}

Second of all, let OrdemDeServico in the pattern:

[Table("OrdemDeServico")]
public class OrdemDeServico
{
    [Key]
    public int Id { get; set; }   
    public int? OrdemEscaneadaId { get; set; }

    [Display(Name="Ordem de Serviço")]
    public virtual Anexo OrdemEscaneada { get; set; }
    public virtual ICollection<Anexo> FotosDoEquipamento { get; set; }
...

In your context class, define the following method, using the Fluent API:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<OrdemDeServico>().HasRequired(x => x.OrdemEscaneada)
        .WithMany()
        .HasForeignKey(x => x.OrdemEscaneadaId)
        .WillCascadeOnDelete(false);

    modelBuilder.Entity<Anexo>()
        .HasRequired(x => x.OrdemDeServico)
        .WithMany(x => x.FotosDoEquipamento)
        .HasForeignKey(x => x.OrdemDeServicoId);

    ...
}