EF Relationship between 3 Tables

Asked

Viewed 363 times

4

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?

  • 1

    It depends on what you want to do, because it is not feasible the way you did and the tool generates over patterns...

  • I’m sorry, Potter, but what mistake did I make? As I said, I’m learning and any guidance is very valid!

  • I really need to understand why 3 keys, a more detailed study your question is good but, it is kind of impossible to do so!

  • will depend on what you want to implement, because, there are many for many is are two key fields only!

  • I explain: it is the flow of my system. There are several flows and these flows have steps. Each step of this flow has several actions (activities) that will lead to different steps depending on which action was executed. That’s why the triple key. Help? Rsrs

  • Yes @Rubens the Gypsy response would be the solution to your system!!!

  • i made an example going from base to Entity with Fluent.

  • @Rubens Could you please accept the correct answer? This is done by clicking on the 'V' icon below the vote counter of each reply. Thank you!

Show 3 more comments

2 answers

2

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; }
}
  • Thanks Gypsy. I will try. But I was curious how I will work this data later. In this case, how would I search for all data from a stream, for example? The Flow will have steps and each step will have actions. Automatically I think it won’t be possible, right? I’ll have to fill the object manually, is that it? Thanks.

  • Not at all. If your models have been declared correctly, the Entity Framework loads everything for you alone. I’ll update the example so you know how to do.

1


Well I’ll propose to that model then:

Database - Layout inserir a descrição da imagem aqui

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!

  • 1

    Putz thanks man... I was almost approaching this solution. Thank you very much, Harry and Gypsy.

  • 1

    Thanks Harry and Gypsy, it worked perfect.

Browser other questions tagged

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