EF Relationship between 3 Tables

Question

I am learning Entity Framework using Code First and found a difficulty with relationships.

I have three tables in the system that I’m making:

• Flow (Key = ID_FLUXO);
• Step (Key = ID_PASSO);
• Acao (Key = ID_ACAO);

I need two tables: one that relates the Flow (FLUXO_X_PASSO) and one that associates the three... (FLUXO_X_PASSO_X_ACAO).

I managed to do the first part using Fluent API as in the code below:

   HasMany(w => w.Steps)
    .WithMany(w => w.Workflows)
    .Map(m => 
        { 
          m.MapLeftKey("ID_FLUXO"); 
          m.MapRightKey("ID_PASSO"); 
          m.ToTable("TB_FLUXO_X_PASSO"); 
        });

But I don’t know how to do it in the second case. Like, I’d need something like:

   HasMany(w => w.Steps)
    .WithMany(w => w.Workflows)
    .Map(m => 
        { 
          m.MapLeftKey("ID_FLUXO"); 
          m.MapRightKey("ID_PASSO"); 
          ** m.Map???("ID_ACAO"); **
          m.ToTable("TB_FLUXO_X_PASSO_X_ACAO"); 
        });

I ended up creating the bank in hand and tried to reverse engineer with Powertools, but he also failed to do the second part.

Does anyone see a way out of this issue or is it better not to use EF in this particular case?

Answer 1

I don’t know Fluent API syntax where it is possible to define an association between 3 entities. I think it looks better if you map the model and set the keys on it:

public class FluxoPassoAcao {
    [Key]
    public int FluxoPassoAcaoId { get; set; }
    [Column("FLUXO_ID")]
    public int FluxoId { get; set; }
    [Column("PASSO_ID")]
    public int PassoId { get; set; }
    [Column("ACAO_ID")]
    public int AcaoId { get; set; }

    public virtual Fluxo Fluxo { get; set; }
    public virtual Passo Passo { get; set; }
    public virtual Acao Acao { get; set; }
}

For this modeling, the 3 models need to receive the following:

public class Fluxo {
    ...
    public virtual ICollection<FluxoPassoAcao> FluxosPassosAcoes { get; set; }
}

public class Passo {
    ...
    public virtual ICollection<FluxoPassoAcao> FluxosPassosAcoes { get; set; }
}

public class Acao {
    ...
    public virtual ICollection<FluxoPassoAcao> FluxosPassosAcoes { get; set; }
}

Answer 2

Well I’ll propose to that model then:

Database - Layout

Classes Models and Fluent Mapping

public partial class Acao
{
    public Acao()
    {
        this.FluxoPassoAcaos = new List<FluxoPassoAcao>();
    }

    public int ID_ACAO { get; set; }
    public string DESCRICAO { get; set; }
    public virtual ICollection<FluxoPassoAcao> FluxoPassoAcaos { get; set; }
}

public class AcaoMap : EntityTypeConfiguration<Acao>
{
    public AcaoMap()
    {
        this.HasKey(t => t.ID_ACAO);
        this.Property(t => t.DESCRICAO).HasMaxLength(50);
        this.ToTable("Acao");
        this.Property(t => t.ID_ACAO).HasColumnName("ID_ACAO");
        this.Property(t => t.DESCRICAO).HasColumnName("DESCRICAO");
    }
}

---

public partial class Fluxo
{
    public Fluxo()
    {
        this.FluxoPassoAcaos = new List<FluxoPassoAcao>();
    }

    public int ID_FLUXO { get; set; }
    public string DESCRICAO { get; set; }
    public virtual ICollection<FluxoPassoAcao> FluxoPassoAcaos { get; set; }
}
public class FluxoMap : EntityTypeConfiguration<Fluxo>
{
    public FluxoMap()
    {       
        this.HasKey(t => t.ID_FLUXO);
        this.Property(t => t.DESCRICAO).IsRequired().HasMaxLength(50);
        this.ToTable("Fluxo");
        this.Property(t => t.ID_FLUXO).HasColumnName("ID_FLUXO");
        this.Property(t => t.DESCRICAO).HasColumnName("DESCRICAO");
    }
}

---

public partial class Passo
{
    public Passo()
    {
        this.FluxoPassoAcaos = new List<FluxoPassoAcao>();
    }
    public int ID_PASSO { get; set; }
    public string DESCRICAO { get; set; }
    public virtual ICollection<FluxoPassoAcao> FluxoPassoAcaos { get; set; }
}
public class PassoMap : EntityTypeConfiguration<Passo>
{
    public PassoMap()
    {       
        this.HasKey(t => t.ID_PASSO);
        this.Property(t => t.DESCRICAO).HasMaxLength(50);
        this.ToTable("Passo");
        this.Property(t => t.ID_PASSO).HasColumnName("ID_PASSO");
        this.Property(t => t.DESCRICAO).HasColumnName("DESCRICAO");
    }
}

---

public partial class FluxoPassoAcao
{
    public int ID_FLUXO { get; set; }
    public int ID_PASSO { get; set; }
    public int ID_ACAO { get; set; }
    public virtual Acao Acao { get; set; }
    public virtual Fluxo Fluxo { get; set; }
    public virtual Passo Passo { get; set; }
}
public class FluxoPassoAcaoMap : EntityTypeConfiguration<FluxoPassoAcao>
{
    public FluxoPassoAcaoMap()
    {
        this.HasKey(t => new { t.ID_FLUXO, t.ID_PASSO, t.ID_ACAO });        
        this.Property(t => t.ID_FLUXO).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
        this.Property(t => t.ID_PASSO).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
        this.Property(t => t.ID_ACAO).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
        this.ToTable("FluxoPassoAcao");
        this.Property(t => t.ID_FLUXO).HasColumnName("ID_FLUXO");
        this.Property(t => t.ID_PASSO).HasColumnName("ID_PASSO");
        this.Property(t => t.ID_ACAO).HasColumnName("ID_ACAO");

        this.HasRequired(t => t.Acao)
            .WithMany(t => t.FluxoPassoAcaos)
            .HasForeignKey(d => d.ID_ACAO);
        this.HasRequired(t => t.Fluxo)
            .WithMany(t => t.FluxoPassoAcaos)
            .HasForeignKey(d => d.ID_FLUXO);
        this.HasRequired(t => t.Passo)
            .WithMany(t => t.FluxoPassoAcaos)
            .HasForeignKey(d => d.ID_PASSO);
    }
}

---

Generics class

public partial class GenericsContext : DbContext
{
    public GenericsContext()
        : base("Name=GenericsContext")
    {
    }

    public DbSet<Acao> Acao { get; set; }
    public DbSet<Fluxo> Fluxo { get; set; }
    public DbSet<FluxoPassoAcao> FluxoPassoAcao { get; set; }
    public DbSet<Passo> Passos { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new AcaoMap());
        modelBuilder.Configurations.Add(new FluxoMap());
        modelBuilder.Configurations.Add(new FluxoPassoAcaoMap());
        modelBuilder.Configurations.Add(new PassoMap());        
    }
}

---

Insert

using (GenericsContext db = new GenericsContext())
{
    Passo passo = new Passo();
    passo.DESCRICAO = "Passo";
    db.Passo.Add(passo);
    db.SaveChanges();


    Fluxo fluxo = new Fluxo();
    fluxo.DESCRICAO = "Fluxo";
    db.Fluxo.Add(fluxo);
    db.SaveChanges();


    Acao acao = new Acao();
    acao.DESCRICAO = "Acao";
    db.Acao.Add(acao);
    db.SaveChanges();


    FluxoPassoAcao fluxopassoacao = new FluxoPassoAcao();
    fluxopassoacao.Acao = acao;
    fluxopassoacao.Fluxo = fluxo;
    fluxopassoacao.Passo = passo;
    db.FluxoPassoAcao.Add(fluxopassoacao);
    db.SaveChanges();

}

---

Search by Fluxopassoacao

FluxoPassoAcao findFPA = db.FluxoPassoAcao.Where(x =>x.ID_PASSO == 2 && x.ID_ACAO == 2 && x.ID_FLUXO == 2).FirstOrDefault();

Realize it works, but, uh, it will really depend on whether that’s what you need!