EF Relationship between 3 Tables

4

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

I have three tables in the system that I'm doing:

  • Flow (Key = FLOW_ID);
  • Step (Key = STEP ID);
  • Action (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 was able 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"); 
        });

However, I do not know how to do it in the second case. Like, I'd need something like this:

   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 could not do the second part.

Does anyone see an exit for this issue or is it better not to use EF in this particular case?

    
asked by anonymous 20.05.2014 / 19:11

2 answers

1

Well I'll propose to that model then:

Database - Layout

ClassesModelsandFluentMapping

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 FlowPassAction

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

Realize that it works, but it will depend even if that's what you need!

    
23.05.2014 / 02:28
2

I do not know any Fluent API syntax where you can define an association between 3 entities. I think you'd better 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, all 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; }
}
    
20.05.2014 / 19:36