Query Entity Framework relationship Many to Many

Question

Query Entity Framework relationship Many to Many

Navigation

#1 by (4 votes)

4

I'm starting to use the Entity Framework and am wanting to query between two tables, Livroes and Autors , and their relationship is of type Many to Many . I do not know if it's relevant but I'm using the Code First method, so I've created the classes below.

AUTHORS:

class Autor {
    public long Id { get; set; }
    public string Nome { get; set; }
    public ICollection<Livro> Livros { get; set; }
}

BOOKS:

class Livro {
    public long Id { get; set; }
    public string Nome { get; set; }

    [Column("EditoraId")]
    public long EditoraId { get; set; }

    public ICollection<Autor> Autores { get; set; }
}

I'm not going to put the publisher class because I know how to access it and it would get too big the question, if necessary edict and put later. Below my Context class

class EFContext : DbContext {
    public DbSet<Editora> Editoras { get; set; }
    public DbSet<Livro> Livros { get; set; }
    public DbSet<Autor> Autores { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        modelBuilder.Entity<Autor>()
       .HasMany(autor => autor.Livros)
       .WithMany(livro => livro.Autores)
       .Map(x => {
           x.ToTable("livros_e_autores");
           x.MapLeftKey("autor_id");
           x.MapRightKey("livro_id");
       });
    }

    public EFContext() {
        DropCreateDatabaseIfModelChanges<EFContext> initializer = new DropCreateDatabaseIfModelChanges<EFContext>();
        Database.SetInitializer(initializer);
    }
}

When I run the program because of this Many to Many relationship, a new table, called books_and_author , is created as shown in the image below.

TheproblemisthatIdonotknowhowtoaccessthisnewtablethatwascreated,Itriedtomakeaforeachinsideaforeach(codebelow)passingIDoftheBookbuttheresultiswrong,returningallauthorsforeachbook.

publicvoidListaTodosLivroAutores(){using(EFContextctx=newEFContext()){varretornoLivro=fromlivinctx.Livrosselectliv;foreach(varitemLivinretornoLivro){Console.WriteLine("\nCódigo Livro: " + itemLiv.Id
                                      + "\nNome Livro: " + itemLiv.Nome);

                var retornoAutor = from aut in ctx.Autores
                            where ctx.Livros.Any(l => l.Id == itemLiv.Id)
                            select aut;
                foreach(var itemAut in retornoAutor) {
                    Console.WriteLine("     Autor: " + itemAut.Nome);
                }
            }
        }
    }

My question is, do you have access to this table created by the relationship? If yes, how? The query I wanted would be something like this Select below.

SELECT liv.Id "Código Livro",
       liv.Nome "Nome do Livro",
       aut.Nome "Autores"
  FROM dbo.livros_e_autores lea
  INNER JOIN dbo.Livroes liv ON liv.Id = lea.livro_id
  INNER JOIN dbo.Autors aut ON aut.Id = lea.autor_id

sql-server c# entity-framework

asked by anonymous 21.02.2018 / 14:16

1 answer

Is it possible to use ng-model with contenteditable? REGEX of HH: MM: SS may be negative

score 4 · Accepted Answer

You do not need to access this table, just make the query:

var resultado = ctx.Livros.Include(l=> l.Autores).ToList();

or

var resultado = ctx.Livros.Include("Autores").ToList();

To print, for example:

foreach (Livro l in resultado)
{
    Console.WriteLine("Código Livro: " + l.Id);
    Console.WriteLine("Nome: " + l.Nome);

    foreach (Autor a in l.Autores)
        Console.WriteLine("Autor: " + a.Nome);    

    Console.WriteLine("---------------");

}